Python Database connection Close

Python Database connection Close

Connections have a close method as specified in 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()
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 pyodbc
conn = 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 contextmanager
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 didnt open the block using with open_db_connection(..., commit=True).

Python Database connection Close

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