vandelay vandelay - 5 months ago 12
SQL Question

What happens if I don't close a mysql connection and opens another?

cnx = mysql.connector.connect(user="root", password="pass", host="127.0.0.1", database="db")
cursor = cnx.cursor()
return cursor, cnx


Lets say I open a connection like the above, and make a select query
cursor.execute(selectquery)
and use
cursor.fetchall()
to get the data.

If I don't
cnx.close()
and
cursor.close()
what will happen when I continuously open and do select queries like this?

Will the connection stay open? Or are there any "garbage patrol" that can see it's not getting used?

Answer

Let's assume you are reusing the same variable name, or that it went out of scope (the function that contained it returns) without being explicitly closed. The most common version of python has reference counting, which should detect that there's no longer any way to access your connection object. So eventually the connection should be closed. But python does not guarantee this behavior, and in fact (as I implied) it does not hold for all python engines.

Now suppose you used two different variables to hold two simultaneously open connections. This is entirely legal, and occasionally useful. Mostly you won't notice anything odd, but if transactions are enabled and you abandon the first connection without closing it or committing the transaction, database changes made in this transaction will not yet be visible from the other connection. That's how transactions are meant to work.

If you don't like the style of closing explicitly, you can guard the connection with a with statement:

with mysql.connector.connect(...) as cnx:
    cursor = cnx.cursor() 
    ...

The connection will be closed for you as soon as execution leaves the with block, no matter by what route.

Comments