python - How can a background process access the database in Flask?

Question

A MySQL server is initialized in Flask (with connexion) on service startup.

service.app.datastore = DatastoreMySQL(service.config)

class DatastoreMySQL(Datastore):
    def __init__(self, config):
        ...
        self.connection_pool = pooling.MySQLConnectionPool(
            database=self.database,
            host=self.hostname,
            username=self.username,
            password=self.password,
            pool_name="pool_name",
            pool_size=self.pool_size,
            autocommit=True
        )

    def exec_query(self, query, params=None):
        try:
            connection = self.connection_pool.get_connection()
            connection.ping(reconnect=True)
            with closing(connection.cursor(dictionary=True, buffered=True)) as cursor:
                if params:
                    cursor.execute(query, params)
                else:
                    cursor.execute(query)
        finally:
            connection.close()

The view functions use the database by passing the DB reference from current_app.

def new():
    do_something_in_db(current_app.datastore, request.get_json())

def do_something_in_db(db, data):
    db.create_new_item(data)
    ...

However, a background process (run with APScheduler) must also run do_something_in_db(), but when passed a datastore reference an mysql.connector.errors.OperationalError error is thrown.

My understanding is that this error comes from two sources:

• The server timed out and closed the connection. However, in this service the exec_query() function obtains a connection and executes right away, so there should be no reason that it times out. The monitor is also initialized at service startup with a datastore reference, but I am not sure how that can time out given that a new connection is created each time exec_query() is called.

• The server dropped an incorrect or too large packet. However, there are no packets here - the process is run by a local background scheduler.

The error in full:

Job "Monitor.monitor_running_queries" raised an exception
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 509, in cmd_query
    raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: MySQL server has gone away

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/k8s-service/lib/datastore/datastore_mysql.py", line 88, in exec_query
    cursor.execute(query, params)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor_cext.py", line 276, in execute
    raw_as_string=self._raw_as_string)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 512, in cmd_query
    sqlstate=exc.sqlstate)
mysql.connector.errors.DatabaseError: 2006 (HY000): MySQL server has gone away

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/apscheduler/executors/base.py", line 125, in run_job
    retval = job.func(*job.args, **job.kwargs)
  File "/opt/k8s-service/lib/background.py", line 60, in monitor_running_queries
    self.handle_process_state(query.id, datastore, hive)
  File "/opt/k8s-service/lib/background.py", line 66, in handle_process_state
    query = datastore.get_item(query_id)
  File "/opt/k8s-service/lib/datastore/datastore.py", line 48, in get_item
    return_results=True)
  File "/opt/k8s-service/lib/datastore/datastore.py", line 97, in exec_query
    connection.close()
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/pooling.py", line 131, in close
    cnx.reset_session()
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 768, in reset_session
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

question from:https://stackoverflow.com/questions/65923041/how-can-a-background-process-access-the-database-in-flask

Answer 1

Waitting for answers