pythonmysqlpython-3.xdatabasepymysql

InterfaceError using PyMySQL (database connection closes)


I use PyMySQL library and Flask in my program. My view function accesses the database every time it called. After some calls it breaks and raise InterfaceError(0, ''). All next requests also raise InterfaceError (any db query, specifially).

Traceback (most recent call last):
   (several files of mine and Flask)
  File "/home/maxim/.local/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/maxim/.local/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/maxim/.local/lib/python3.7/site-packages/pymysql/connections.py", line 516, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/maxim/.local/lib/python3.7/site-packages/pymysql/connections.py", line 750, in _execute_command
    raise err.InterfaceError("(0, '')")
pymysql.err.InterfaceError: (0, '')

I read PyMySQL library code and saw, that this error occures if connection's _sock variable is None (i think it means connection is closed). But why is it happen?

I use one connection object for all view functions (i.e. it is defined outside functions). Do I do it right or I must make new connection every request? Or I need do something other to get rid of this error?

My code: https://pastebin.com/sy3xKtgB
Full traceback: https://pastebin.com/iTU75FUi


Solution

  • I solved my problem by creating a new connection to db every request.

    def get_db():
        return pymysql.connect(
            'ip',
            'user',
            'password',
            'db_name',
            cursorclass=pymysql.cursors.DictCursor
        )
    

    I call this function every request.

    from flask import Flask, request
    from my_utils import get_db
    
    app = Flask(__name__)
    @app.route('/get', methods=['POST'])
    def get():
        conn = get_db()
        with conn.cursor() as cur:
            pass