CODEWITHSUNDEEP
pythonpython-3.xpeewee
rihekopo
rihekopo

Reputation: 3350

Handling Database Disconnection With Peewee

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?

Upvotes: 3

Views: 2115

Answers (1)

coleifer
coleifer

Reputation: 26245

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.

Upvotes: 3

Related Questions