104

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
Improve this question

6 Answers 6

Reset to default
154

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Is this best practice? To manually close the cursor, then delete it, then close the connection?
I would like to the know answer to @DustinMichels questions as well.
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 this, but you can explicitly close the connection if you wish". And similarly for cursors too.
@TuanDT: del is not required here. It does close the cursor, but it usually is not necessary because the same happens automatically when csr goes out of scope (and that is usually soon enough). Use it only if you want to remove csr from the namespace and/or reduce references to the object by one. When I first posted this, I kept del because it is not necessarily wrong and I assumed the OP understood its purpose. But since it seems to be causing confusion and is not required for closing the connection, I'm removing it now.
pyodbc objects DO NOT close automatically when used with context managers!! As the documentation you link to points out, using context manager syntax is equivalent to committing BUT NOT closing the connection or cursor. See github.com/mkleehammer/pyodbc/issues/43
|
67

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 didn't open the block using with open_db_connection("...", commit=True).

Improve this answer

5 Comments

good idea, But I use Mysql and Sqlite....not oracle (well not directly:-)!) Where the connection string to driver... why import sys?
Whoops, I thought I replaced my oracle specific code with pyodbc, but I overlooked one (fixed now). The syntax is the same, since both use the common PEP 249 database API. I imported sys so I could write any exceptions to standard error. You could also use logging or just a plain print statement. And you pass the same connection string you were using before to open_db_connection().
Ok, looking at the code: What Do i get by doing? Seems like a lot of extra lines of code to check if connection is open?
This is exactly what I was looking for and I have it working successfully with MySQLdb, but you gotta' change import contextlib to from contextlib import contextmanager or change your decorator to @contextlib.contextmanager. Either way, I guess, but I prefer the former.
great method, I was looking for such a thing. But how do I put it inside a class? My code at the moment has pyodbc connection as one of the fields of the class: self.db = pyodbc.connect(path) now when I want to overwrite it to your: self.db = open_db_connection("...") or anything in this manner, my connection get's closed as soon as it's initialized pyodbc.ProgrammingError: The cursor's connection has been closed. - (and I want to be connected thourghout the runtime.
8

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
Improve this answer

1 Comment

Funny I could use the pooling, but mysql just starts another conn with a new ID.
6

According to pyodbc documentation, connections to the SQL server are not closed by default. Some database drivers do not close connections when close() is called in order to save round-trips to the server.

To close your connection when you call close() you should set pooling to False:

import pyodbc

pyodbc.pooling = False
Improve this answer

1 Comment

When you say "call close()", do you mean cursor.close()? I assume so, since connection.close() would avoid the need to set pooling to false.
6

You can define a DB class as below. Also, as andrewf suggested, use a context manager for cursor access.I'd define it as a member function. This way it keeps the connection open across multiple transactions from the app code and saves unnecessary reconnections to the server. 

import pyodbc

class MS_DB():
    """ Collection of helper methods to query the MS SQL Server database.
    """

    def __init__(self, username, password, host, port=1433, initial_db='dev_db'):
        self.username = username
        self._password = password
        self.host = host
        self.port = str(port)
        self.db = initial_db
        conn_str = 'DRIVER=DRIVER=ODBC Driver 13 for SQL Server;SERVER='+ \
                    self.host + ';PORT='+ self.port +';DATABASE='+ \
                    self.db +';UID='+ self.username +';PWD='+ \ 
                    self._password +';'
        print('Connected to DB:', conn_str)
        self._connection = pyodbc.connect(conn_str)        
        pyodbc.pooling = False

    def __repr__(self):
        return f"MS-SQLServer('{self.username}', <password hidden>, '{self.host}', '{self.port}', '{self.db}')"

    def __str__(self):
        return f"MS-SQLServer Module for STP on {self.host}"

    def __del__(self):
        self._connection.close()
        print("Connection closed.")

    @contextmanager
    def cursor(self, commit: bool = False):
        """
        A context manager style of using a DB cursor for database operations. 
        This function should be used for any database queries or operations that 
        need to be done. 

        :param commit:
        A boolean value that says whether to commit any database changes to the database. Defaults to False.
        :type commit: bool
        """
        cursor = self._connection.cursor()
        try:
            yield cursor
        except pyodbc.DatabaseError as err:
            print("DatabaseError {} ".format(err))
            cursor.rollback()
            raise err
        else:
            if commit:
                cursor.commit()
        finally:
            cursor.close()

ms_db = MS_DB(username='my_user', password='my_secret', host='hostname')
with ms_db.cursor() as cursor:
        cursor.execute("SELECT @@version;")
        print(cur.fetchall())
Improve this answer

1 Comment

I think best solution is in this link https://stackoverflow.com/a/38078544/12274302
4

The most common way to handle connections, if the language does not have a self closing construct like Using in .NET, then you should use a try -> finally to close the objects. Its possible that pyodbc does have some form of automatic closing but here is the code I do just in case:

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

    cursor = conn.cursor()  

    # ... do stuff ...

finally:
    try: cursor.close()
    except: pass
    try: conn.close()
    except: pass
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.