Python Database connection Close

Question

Using the code below leaves me with an open connection, how do I close?

import pyodbc
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') 

csr = conn.cursor()  
csr.close()
del csr

Answer 1

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

import pyodbc
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') 

csr = conn.cursor()  
csr.close()
del csr
conn.close()     #<--- Close the connection

Answer 2

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

import pyodbc
pyodbc.pooling = False
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') 

csr = conn.cursor()  
csr.close()
del csr

Answer 3

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

import contextlib
import pyodbc
import sys

@contextmanager
def 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).