[Docker][Flask][MariaDB][PYMYSQL] docker - flask에서 mariadb 연결 오류(error: subprocess-exited-with-error) 해결, pymysql로 대체

0. error

1. source code

2. solution

---

0. error

- Flask로 간단한 application 구현을 위해 container를 만드는 도중 에러 발생

- mariadb 연결하는 부분에 오류가 발생, 유사 오류 확인

 유사사례: https://stackoverflow.com/questions/73350980/python-mariadb-library-and-his-connector-inside-a-docker-container

Collecting mariadb==1.1.4
  Downloading mariadb-1.1.4.zip (97 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 97.4/97.4 kB 10.8 MB/s eta 0:00:00
  Preparing metadata (setup.py): started
  Preparing metadata (setup.py): finished with status 'error'
  error: subprocess-exited-with-error
  
  × python setup.py egg_info did not run successfully.
  │ exit code: 1
  ╰─> [8 lines of output]
      Traceback (most recent call last):
        File "<string>", line 2, in <module>
        File "<pip-setuptools-caller>", line 34, in <module>
        File "/tmp/pip-install-2uzbllpk/mariadb_4588dbf4d5fc48b989ebd2605c2d7d1c/setup.py", line 27, in <module>
          cfg = get_config(options)
        File "/tmp/pip-install-2uzbllpk/mariadb_4588dbf4d5fc48b989ebd2605c2d7d1c/mariadb_posix.py", line 64, in get_config
          print('MariaDB Connector/Python requires MariaDB Connector/C '
      TypeError: not enough arguments for format string
      [end of output]
  
  note: This error originates from a subprocess, and is likely not a problem with pip.
error: metadata-generation-failed

× Encountered error while generating package metadata.
╰─> See above for output.

note: This is an issue with the package mentioned above, not pip.
hint: See above for details.

---

1. Source Code

- app.py

# /app.py

from flask import Flask, render_template, jsonify, request
from connect_db import connect

# app 선언
app = Flask(__name__)
# json에서 한글 사용
app.config['JSON_AS_ASCII'] = False

# mariaDB 연결
mariadb = connect()
mariadb.db_connect()

#Flask 형식.. @을 쓰고 라우터 정보를 적는다.
@app.route('/')
def index():
    #index page. 모든 html 페이지는 templates 라는 폴더 안에 있어야 함. 
    print("run server")
    #기본 페이지는 index.html로 
    return render_template("index.html")

# Flask form
@app.route('/api', methods=['POST'])
def api_test(): 
    # json request, id
    params = request.get_json()
    # id에서 - 빼주기
    params['id'] = params['id'].replace("-","")
    # db에서 검색
    data, dict_data = mariadb.query_run(params['id'])
    print(data)
    
    # return the results!
    return jsonify(dict_data)

@app.route('/run', methods=['POST'])
def web_test(): 
    # json request, id
    params = request.get_json()
    # id에서 - 빼주기
    params['id'] = params['id'].replace("-","")
    # db에서 검색
    data, dict_data = mariadb.query_run(params['id'])
    print(data)
    
    # return the results!
    return jsonify(dict_data)

if __name__ == '__main__':
    app.run(debug = True, host='0.0.0.0', port=5000)

---

- connect_db.py

import mariadb

db_config = {
    'host': '00.00.000.000',
    'port': 3306,
    'user': 'user',
    'password': 'password',
    'database': 'database'
}

class connect:
   # def __init__(self):
   #     self.host = ''
   #     self.port = 3306
   #     self.user = ""
   #     self.password = ""
   #     self.database = ""

    def db_connect(self):
        self.db = mariadb.connect(**db_config
           # host=self.host, 
           # port=self.port, 
           # user=self.user, 
           # password=self.password, 
           # database=self.database
            )
   
    def query_run(self, req_num):
        self.cursor = self.db.cursor()
        query = f"select * from ack_cbc_diff where REQNO='{req_num}'"
        self.cursor.execute(query)
        #results = self.cursor.fetchall()
        self.cursor.rowfactory = lambda *args: dict(zip([d[0] for d in self.cursor.description], args))
        data = self.cursor.fetchall()

        dict_data=[]
        for result in data:
            dict_data.append(dict(zip([x[0] for x in self.cursor.description], result)))
        self.cursor.close()
        return data, dict_data

    def close(self):
        self.db.close()

---

- index.html

<html>
  <head>
    <script>
	  var xhr = new XMLHttpRequest();
      xhr.onload = function(e){
		  if (this.status == 200){
			  try{
                  var res_data = JSON.parse(this.response);			  
			      document.getElementById("H102").value = res_data[0]["H102"];
			      document.getElementById("H103").value = res_data[0]["H103"];
			  }
			  catch(e){
				  document.getElementById("H102").value = "Code Error";
                  document.getElementById("H103").value = "Code Error";
				  console.log(this.response);
				  console.log(e);
			  }
		  }
		  else{
			  document.getElementById("H102").value = "Communication Error";
			  document.getElementById("H103").value = "Communication Error";
		  }
	  }

	  function send(){
		document.getElementById("H102").value = "Please, wait.";
        document.getElementById("H103").value = "This is underway.";
		let form = document.forms[0];
		var unit_number = document.getElementById("unit_number").value;
        if(unit_number.length < 10){
            alert("잘못된 Unit_number 형식입니다.");			
		}
		else{
			xhr.open(form.method, form.action, true);
			xhr.setRequestHeader("Content-Type", "application/JSON;charset=UTF-8");
			var json_data= JSON.stringify({"id":unit_number});
			xhr.send(json_data);
		}
	  }

      function copy(item){
		  const textArea = document.getElementById(item);
		  textArea.select();
		  document.execCommand('copy');
	  }

	</script>
  </head>
  <body>
    <h1>PB, Rule-Engine1 test</h1>
    <p>Enter unit-number</p>
	<form action="run" method="POST" name="run_form">
	  <input type="text" name="unit_number" id="unit_number" />
	  <input type="button" value="run" onclick="send();"/>
	</form>
	<br><br>
	<table style='text-align:center;'>
	<tr>
	  <th><input type="button" value="H102" onclick='copy("H102");'></th>
	  <th><input type="button" value="H103" onclick='copy("H103");';></th>
	</tr>
	<tr>
	  <td>
	    <input type="number" id="H102">
	  </td>
	  <td>
	    <input type="number" id="H103">
	  </td>
	</tr>
  </table>
  </body>
</html>

---

- Dockerfile

FROM python:3.7.3-alpine3.8

# set a directory for the app
WORKDIR /app

# copy all the files to the container(directory)
COPY . /app

# install dependencies
#RUN apt-get update
#RUN apt-get -y upgrade

#RUN apt-get install libmariadb3
#RUN apt-get install libmariadb-dev


#RUN pip install --upgrade setuptools
#RUN pip install --upgrade setuptools

#RUN apk add --no-cache mariadb-dev build-base
RUN pip3 install --upgrade pip
RUN pip3 install flask
RUN pip3 install mariadb==1.0.11
#RUN pip3 install --no-cache-dir -r requirements.txt

# PORT
EXPOSE 5000

# run the command
CMD ["python3", "app.py"]`

---

2. solution

- 에러 해결을 위해 여러 방법을 시도해봤지만 실패

- 유사사례 답변으로 mariadb==1.1.5에서는 해결되었지만 아직 미배포되었다는 답변 확인

- mariadb 연결을 mariadb library 대신 pymysql로 대체

- connect_db.py 수정

import pymysql

class connect:

    def db_connect(self):
        self.db = pymysql.connect(
            host='', 
            port=3306, 
            user='', 
            password='', 
            db='',
            charset='utf8'
            )
   
    def query_run(self, req_num):
        self.cursor = self.db.cursor()
        query = f"select * from ack_cbc_diff where REQNO='{req_num}'"
        self.cursor.execute(query)
        #results = self.cursor.fetchall()
        self.cursor.rowfactory = lambda *args: dict(zip([d[0] for d in self.cursor.description], args))
        data = self.cursor.fetchall()

        dict_data=[]
        for result in data:
            dict_data.append(dict(zip([x[0] for x in self.cursor.description], result)))
        self.cursor.close()
        return data, dict_data

    def close(self):
        self.db.close()

- container 생성 및 정상 구동 확인.