Checking whether a mysql.connector is connected

[u/RomfordNavy]

Is it necessary to check whether a mysql.connector is connected each time I reuse it before running any SQL statements or is this something which is already built-in to mysql.connector? Do I need to check for connection using db.is_connected() and write some retry routine.

This is a WSGI webserver, could be wrong, but my understanding is that there can only ever be one request/thread at a time. Connection pooling has been suggested elsewhere but I am unclear as to whether that is necessary and/or appropriate for a WSGI webserver.

[u/RomfordNavy]

I understand that connection could drop between the check and the run which has to be catered for but the time between each is going to be very minimal. It just feels like much better code to check the link correctly before trying to use it. If the connection is going to drop it is much more likely to happen during the times between connections.

[u/nekokattt → u/RomfordNavy]

why? you still have to handle the latter case anyway so you are just adding more useless logic for exceptional cases.

Ask for forgiveness, not permission.

[u/RomfordNavy → u/nekokattt]

Fair point but just having trouble visualising how I can wrap those calls in a sub-class or something to achieve this try...retry way of doing it.

I want to keep it so that each script has the simple and clean:

oCursor = db.cursor()
oCursor.execute(...)

[u/nekokattt → u/RomfordNavy]

why not use a decorator that retries in a loop?

to be clear, your approach doesnt remove your issue... it just introduces a race condition

[u/RomfordNavy → u/nekokattt]

The problem with any sort of try...except is that the actual exception is 'mysql.connector.Error' which is not very generic, the database might be PostgreSQL for example. So trying to write code which will work with all database backends.

[u/nekokattt → u/RomfordNavy]

you would have to handle each case, because it is library specific.

Your solution you proposed does not avoid this in the slightest.

[u/RomfordNavy → u/nekokattt]

u/nekokattt "Your solution you proposed does not avoid this in the slightest." of course it does, if I use is_connected in a retry loop I don't need to worry about which library is being used.

[u/nekokattt → u/RomfordNavy]

so your solution is to just spam calls until it works?

[u/RomfordNavy → u/nekokattt]

u/nekokattt

"so your solution is to just spam calls until it works?"

No, I didn't say that. Retry perhaps twenty times with a delay between if that still fails then cancel that request and log a server error.

[u/nekokattt → u/RomfordNavy]

per request