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

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 6

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 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.

Answer 9

First, what is a python sqlite3.Cursor? The code shows it is essentially a holder for a sqlite3_stmt structure, a prepared statement handle which is passed to sqlite3_step to iterate through result rows.

As far as I can tell, there’s no benefit to creating your own cursor object with conn.cursor() over just using the one automatically created for you and returned by conn.execute(). Skip conn.cursor() and save yourself the extra line of code each time. (This is sqlite-specific advice; there may be reasons to create your own cursors with other DB-API drivers for other database systems, I don’t know.)

What does cursor.close() do?

static PyObject *
pysqlite_cursor_close_impl(pysqlite_Cursor *self)
{
    ⋮
    if (self->statement) {
    (void)stmt_reset(self->statement);
    Py_CLEAR(self->statement);
    ⋮
}

The sqlite docs give a short description of sqlite3_reset, which is what stmt_reset calls:

6. Binding Parameters and Reusing Prepared Statements

… SQLite allows the same prepared statement to be evaluated multiple times. This is accomplished using the following routines:

• sqlite3_reset()

• sqlite3_bind()

After a prepared statement has been evaluated by one or more calls to sqlite3_step(), it can be reset in order to be evaluated again by a call to sqlite3_reset(). Think of sqlite3_reset() as rewinding the prepared statement program back to the beginning.

Closing a cursor causes Python to tell the underlying sqlite library to discard the associated result set. But Python still keeps the sqlite3_stmt structure around, because Python internally maintains a cache of prepared statements.

Code like the following is generally fine on CPython:

for row in conn.execute("SELECT …"):
    do_stuff_with(r)

This is because:

1. When iteration gets to the end of the result set, python will automatically call stmt_reset to discard the result set. This happens with both standard python iteration protocols like for … in …, and the sqlite3.Cursor.fetch* methods.

2. If the loop exits early, e.g., because an exception was raised, CPython’s reference-counting will trigger finalization of the anonymous cursor object, again calling stmt_reset. Other python implementations may differ.

Given the above, while it is unlikely that you will ever run into a memory leak by not closing your cursors, it is definitely possible if you have cursors that are both:

• only partially-iterated, and
• kept in scope for long periods of time.

Thus for portability and explicitness you may want to write:

from contextlib import closing

with closing(conn.execute("SELECT …")) as cursor:
    for row in cursor:
        …