Handle SQLAlchemy disconnect

Question

I use SQLAlchemy for my Flask application, and I just ran into a few problems with a private connection to my MySQL database.

I am setting up my connection like this in __init__.py

 engine = create_engine('mysql://user: pass@localhost /db', pool_recycle=3600) db = SQLAlchemy(app) 

(I use both methods to query my DB)

I just added the pool_recycle parameter, but I would like to make sure that the lost connection can be restarted.

Reading the next section: http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic

I'm not sure how to really use this code, I don’t want to check the connection every time it is used in the code, but rather restarted the connection if it closes. Is it possible to add a general event to handle database outages?

Answer 1

it is better to use sqlalchemy with poolclass = NullPoll (not persistent connections) on proyects with high traffic flow since there are so many connections.

for my experience i always use slqalchemy with this function

Answer 2

I think the problem is that the application itself only knows if the connection is disconnected when it tries to use it. It’s as if you are logged in to the website, but after the upgrade your session is dead, so ask to log in again. Until you update, you are logged in. Schrödinger Cat?

I ran into the same problem. It still seems that using pool_recycle is the way to go, and if an error occurs, then it is time to intercept the lover or edit mySQL configs.

As the documentation suggests, an ether that you regularly check or expect an error (for example, in Java, just try: except: all the classes are methods and use them) or just update from time to time all hopes that the connection is up. In this case, the pessimistic approach regularly checks before you use the database. This is the safest way, but, as you mentioned, pretty dirty.

Another option to do as Django is simply to have no permanent connections. Of course, this is much slower, but if the project is smaller, it should do the job. About this: http://docs.sqlalchemy.org/en/latest/core/pooling.html#sqlalchemy.pool.NullPool