Python Database connection Close

Connections have a close method as specified in PEP-249 (Python Database API Specification v2.0):

import pyodbcconn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') csr = conn.cursor()  csr.close()conn.close()     #<--- Close the connection

Since the pyodbc connection and cursor are both context managers, nowadays it would be more convenient (and preferable) to write this as:

import pyodbcconn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') with conn:    crs = conn.cursor()    do_stuff    # conn.commit() will automatically be called when Python leaves the outer `with` statement    # Neither crs.close() nor conn.close() will be called upon leaving the `with` statement!! 

See https://github.com/mkleehammer/pyodbc/issues/43 for an explanation for why conn.close() is not called.

Note that unlike the original code, this causes conn.commit() to be called. Use the outer with statement to control when you want commit to be called.

Also note that regardless of whether or not you use the with statements, per the docs,

Connections are automatically closed when they are deleted (typically when they go out of scope) so you should not normally need to call [conn.close()], but you can explicitly close the connection if you wish.

and similarly for cursors (my emphasis):

Cursors are closed automatically when they are deleted (typically when they go out of scope), so calling [csr.close()] is not usually necessary.

You can wrap the whole connection in a context manager, like the following:

from contextlib import contextmanagerimport pyodbcimport sys@contextmanagerdef open_db_connection(connection_string, commit=False):    connection = pyodbc.connect(connection_string)    cursor = connection.cursor()    try:        yield cursor    except pyodbc.DatabaseError as err:        error, = err.args        sys.stderr.write(error.message)        cursor.execute("ROLLBACK")        raise err    else:        if commit:            cursor.execute("COMMIT")        else:            cursor.execute("ROLLBACK")    finally:        connection.close()

Then do something like this where ever you need a database connection:

with open_db_connection("...") as cursor:    # Your code here

The connection will close when you leave the with block. This will also rollback the transaction if an exception occurs or if you didn't open the block using with open_db_connection("...", commit=True).

You might try turning off pooling, which is enabled by default. See this discussion for more information.

import pyodbcpyodbc.pooling = Falseconn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') csr = conn.cursor()  csr.close()del csr