In Python with sqlite is it necessary to close a cursor?

Question

Here is the scenario. In your function you're executing statements using a cursor, but one of them fails and an exception is thrown. Your program exits out of the function before closing the cursor it was working with. Will the cursor float around taking up space? Do I have to close the cursor?

Additionally, the Python documentation has an example of cursor use and says: "We can also close the cursor if we are done with it." The keyword being "can," not "must." What do they mean precisely by this?

Answer 1

It's probably a good idea (although it might not matter much with sqlite, don't know there, but it'll make your code more portable). Further, with recent Python (2.5+), it's easy:

from __future__ import with_statement
from contextlib import closing

with closing(db.cursor()) as cursor:
    # do some stuff

Answer 2

You're not obliged to call close() on the cursor; it can be garbage collected like any other object.

But even if waiting for garbage collection sounds OK, I think it would be good style still to ensure that a resource such as a database cursor gets closed whether or not there is an exception.

Answer 3

All,

I was experiencing a gradual memory leak with my code (Python 3.8) using sqlite3. I traced the likely cause to my database classes. As it turns out, I would open and use a cursor but never closed it. The database remained open during the life of the program (a Windows Service) and would be closed on exit.

Once I began closing the cursors in all of my db operations which used them, my memory leak stopped, and the memory footprint became steady.

I would therefore suggest that you take the time to close your cursors. It makes the code more consistent and apparently, helps control memory consumed.

Here's an example of how I close the cursor:

def write_to_db(self, cache_item:CacheEntry):
        '''Write a single cache entry to the database'''
        crsr = self._db_con.cursor()

        # Load some data elements
        fax_line_path = cache_item._dir_part
        phone_line = cache_item._phone_line
        sub_folder = cache_item._subfolder
        fname = cache_item._fname
        work_done = cache_item.get_workdone()

        try:
            crsr.execute(FilenameCacheDB.INSERT_CACHE,
                             (fax_line_path, 
                              phone_line, 
                              sub_folder, 
                              fname, 
                              work_done))

        except Exception as e:
            LOG.warning(f"Could not write {cache_item} to db because {e}")
            raise e

        finally:
            #
            # I was *not* closing the cursor prior
            #
            crsr.close()
            self._db_con.commit()

Answer 4

Interestingly, the Python 3.0 doc says "We can also close the cursor if we are done with it", while the Python 2.7 and 3.6 doc says "We can also close the connection if we are done with it".

The Python 2.7 and 3.0-3.4 docs don't describe the cursor .close() method. But the Python 3.5 and 3.6 docs describe the cursor .close() method:

Close the cursor now (rather than whenever __del__ is called).

The cursor will be unusable from this point forward; a ProgrammingError exception will be raised if any operation is attempted with the cursor.

Answer 5

I haven't seen any effect for the sqlite3.Cursor.close() operation yet.

After closing, you can still call fetch(all|one|many) which will return the remaining results from the previous execute statement. Even running Cursor.execute() still works ...

Answer 6

This code will automatically close the Cursor. It will also automatically close and commit the Connection.

import sqlite3
import contextlib

def execute_statement(statement):
    with contextlib.closing(sqlite3.connect(path_to_file)) as conn: # auto-closes
        with conn: # auto-commits
            with contextlib.closing(conn.cursor()) as cursor: # auto-closes
                cursor.execute(statement)

Answer 7

Looking at the code snippet and ideas given by stackoverflowuser2010 and Peer, gracefully handling cursors is easier using Python contextmanager.

from contextlib import contextmanager

@contextmanager
def OpenCursor(conn):
    cursor = conn.cursor()
    try:    
        yield (cursor)
    except Exception as e:  
        cursor.close()  
        raise e
    else:                     
        cursor.close() 

Usage without OpenCursor:

def get(conn, key, default=None):
    cursor = conn.cursor()
    cursor.execute(f'SELECT value FROM table WHERE key=?', (key,))
    row = cursor.fetchone()
    if row:
        return (True)
    else:
        return (default)

Usage with OpenCursor as context manager:

def get(conn, key, default=None):
    with OpenCursor(conn) as cursor:
        cursor.execute(f'SELECT value FROM table WHERE key=?', (key,))
        row = cursor.fetchone()
        if row:
            return (True)
        else:
            return (default)

Answer 8

Yes, we should close our cursor. I once encountered an error when I used my cursor to configure my connection object: 'PRAGMA synchronous=off' and 'PRAGMA journal_mode=off' for faster insertion. Once I closed the cursor, the error went away. I forgot what type of error I encountered.