Handling Database Disconnection With Peewee

Question

My code uses Peewee with MySQL. Everything worked like a charm until I tried to use it with requests that is used for accessing a slow server. In a nutshell, I need to upload objects to a MySQL db, but my program crashes - after uploading a few entries - with the following error:

peewee.OperationalError: (2006, 'MySQL server has gone away')

I understand the error, I already experienced this issue with a Flask app and the Peewee documentation helped me out in that case. But in this case I am not using Flask, it's just a simple Python script. Therefore I couldn't figure out yet how could I manage this problem.

What is interesting that the Peewee related part has nothing to do with html requests. I am doing some non Peewee related tasks then I am calling this method:

def uploadObj (objekt):
    with myDB.atomic():
        entrylist.insert_many(objekt).execute()
        print ("upload")

I assume the problem happens when the html request is slow and the connection is idle for a long time and disconnects.

Based on this answer I tried:

db = MySQLDatabase(db_name, user=db_username, passwd=db_password, host=db_host, port=db_port)
db.get_conn().ping(True)

But this didn't solve the problem.

For my second try I tried the below code that seems fixing the problem:

def uploadObj (objekt):
    try:
        with myDB.atomic():
            entrylist.insert_many(objekt).execute()
            print ("upload")
        myDB.close()
    except:
        myDB.connect()
        with myDB.atomic():
            entrylist.insert_many(objekt).execute()
            print ("upload")

If the connection disconnects, I manually reconnect to the database.

My question is that is this a proper solution that I can use without any possible issues? Or is there a better way to prevent the issue?

score 3 · Accepted Answer · answered Aug 29 '19 at 15:54

3

You can use the reconnect mixin:

from playhouse.shortcuts import ReconnectMixin

class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
    pass

db = ReconnectMySQLDatabase('my_app', ...)

If you have a long-running script that is idle for a while, MySQL will terminate the connections if there is no activity. You can configure your mysql server not to do this, or you can use the above to automatically reconnect under certain error conditions.

answered Aug 29 '19 at 15:54

coleifer

24,887
6
60
75

Is this more recommended then just calling `myDB.connect()` after the disconnection? – rihekopo Aug 29 '19 at 17:33
It depends on what you're trying to do upon a disconnect. If the disconnect is initiated by the server after a long time idle, you may wish to retry the query that failed. – coleifer Sep 02 '19 at 21:41
Is it possible to implement the ReconnectMixin with an Application Factory and Peewee's FlaskDB? – SimonB Mar 28 '23 at 21:53

Handling Database Disconnection With Peewee

1 Answers1