PyMySQL in Flask / Apache sometimes returns an empty result

Question

I have a Flask application that runs on Apache that relies on PyMySQL. The application provides a number of REST commands. It works under Python 3.

Without providing an entire source, the program is structured as:

#!flask/bin/python import json import pymysql from flask import * # Used to hopefully share the connection if the process isn't restarted mysql_connection = None # Gets the mysql_connection, or opens it GetStoreCnx(): global mysql_connection if (mysql_connection != None): store_connection_string = "" # Get the connection string from the config file with open('config/storedb.json', 'r') as f: store_connection_string = json.load(f) mysql_connection = pymysql.connect(**store_connection_string) return mysql_connection; class Server(Flask): def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) # Return results via REST @app.route('/results1', methods=['GET']) def get_result1(): cnx = GetStoreCnx(); cursor = cnx.cursor(); query = """ SELECT result_name, successful FROM results """ cursor.execute(query) cnx.commit() result = cursor.fetchall() return json.dumps(result) # Run server, if needed if __name__ == '__main__': app.run(host='0.0.0.0', debug=True) 

There are several more REST calls, but they all essentially do the same thing (i.e. get a connection, create a cursor, launch a basic select query, which sometimes has more than two fields, execute the query, fetch the result and return it as a JSON object ) The commits there should not be unnecessary, but it seems to be a problem with PyMySQL, which leads to getting old data.

The problem is that these REST calls sometimes return an empty JSON set (i.e. [] ). Further research showed that calling execute sometimes returns a completely empty result, but does not throw an exception. This happens regularly, but not all the time. Some calls successfully return values. When I try to save the call until it returns the result (like:

 while(cursor.execute(query) < 1): pass 

), the process goes into an endless cycle, ultimately (quickly), preventing Apache service from any other requests.

The server (currently) only serves 5 calls per second. The problem does not occur if I use the development server.

Can this error be prevented? Is this a bug in PyMySQL? Am I doing something to prevent proper MySQL connections?

Answer 1

You create one global mysql connection that is used by your application, hoverver pymysql declares threadsafety 1, which according to the dbapi2 specification means:

 1 Threads may share the module, but not connections. 

Since related requests in the bulb will be served by different threads, you should not share this connection. The reason you have no problem using the development server is because it works on a single thread.

To avoid this, you can:

• create a new connection for each stream, save it as a local stream for future use
• create a new connection for each request, save it in flask.g for future use

To do this, your GetStoreCnx function can be modified as follows:

 import threading thread_local = threading.local() def GetStoreCnx(): if not hasattr(thread_local, 'mysql_connection'): store_connection_string = "" # Get the connection string from the config file with open('config/storedb.json', 'r') as f: store_connection_string = json.load(f) mysql_connection = pymysql.connect(**store_connection_string) thread_local.mysql_connection = mysql_connection return thread_local.mysql_connection; 

SQLAlchemy does something similar with scoped_session() . This should also work with flask.g instead of thread_local for one connection for each request.