Is there a "with conn.cursor() as..." way to work with Sqlite?

Question

Instead of using:

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute(...)
c.close()

would it be possible to use the Pythonic idiom:

with conn.cursor() as c:
    c.execute(...)

It doesn't seem to work:

AttributeError: __exit__

Note: it's important to close a cursor because of this.

Inshore

Have you checked the docs? docs.python.org/3/library/… – Airborne

Answer 1

You can use contextlib.closing:

import sqlite3
from contextlib import closing

conn = sqlite3.connect(':memory:')

with closing(conn.cursor()) as cursor:
    cursor.execute(...)

This works because closing(object) automatically calls the close() method of the passed in object after the with block.

Babbette

digitalocean.com/community/tutorials/… – Babbette

Answer 2

A simpler alternative would be to use the connection object with the context manager, as specified in the docs.

with con:
    con.execute(...)

---

If you insist on working with the cursor (because reasons), then why not make your own wrapper class?

class SafeCursor:
    def __init__(self, connection):
        self.con = connection

    def __enter__(self):
        self.cursor = self.con.cursor()
        return self.cursor

    def __exit__(self, typ, value, traceback):
        self.cursor.close()

You'll then call your class like this:

with SafeCursor(conn) as c:
    c.execute(...)

Africander

Answer 3

Adding to sudormrfbin's post. I've recently experienced an issue where an INSERT statement wasn't committing to the database. Turns out I was missing the with context manager for just the Connection object.
Also, it is a good practice to always close the Cursor object as well, as mentioned in this post.
Therefore, use two contextlib.closing() methods, each within a with context manager:

import contextlib
import sqlite3

# Auto-closes the Connection object
with contextlib.closing(sqlite3.connect("path_to_db_file")) as conn:
    # Auto-commit to the database
    with conn:
        # Auto-close the Cursor object
        with contextlib.closing(conn.cursor()) as cursor:
            # Execute method(s)
            cursor.execute(""" SQL statements here """)

Infrequency

You can omit with conn: because cursor.close() would close the connection – Freeboard