9

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.

Basj
  • 41,386
  • 99
  • 383
  • 673
  • Have you checked the docs? https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager – DeepSpace Nov 25 '18 at 20:40
  • @downvoter: can you help how to improve the question? (I did read the doc, but here it's slightly different) – Basj Nov 25 '18 at 20:49

3 Answers3

12

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.

sudormrfbin
  • 618
  • 8
  • 16
  • 3
    https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3#step-5-%E2%80%94-using-with-statements-for-automatic-cleanup – sudormrfbin Jan 09 '21 at 07:43
5

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(...)
cs95
  • 379,657
  • 97
  • 704
  • 746
  • There is a built-in way: https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager – DeepSpace Nov 25 '18 at 20:40
  • @DeepSpace Thanks, will edit, didn't know about that. Additionally, I'm wondering if there's a reason OP is trying to set up a context manager for the cursor aside from them simply not having read the docs. – cs95 Nov 25 '18 at 20:42
  • 1
    @DeepSpace This is a context manager for the connection object, not the cursor. `Connection objects can be used as context managers that automatically commit or rollback transactions`: Let's say I want to do a SELECT query and then a few `cur.fetchone()`, I need a cursor object, don't you think so? – Basj Nov 25 '18 at 20:47
  • 1
    @coldspeed No no, I read the doc, but (see my previous comment), the doc explains it automatically commits, and also it doesn't speak about a cursor. It's not exactly what I want: I don't want to commit, and I need a cursor to fetch rows. Or do you mean there's another way to do it? – Basj Nov 25 '18 at 20:48
  • @Basj If you just need something that can close the cursor under any circumstances (because you don't trust someone else to do it), then my wrapper class suggestion would work for you. – cs95 Nov 25 '18 at 20:49
  • 1
    @coldspeed Yes i'll probably use it. I confirm I think what I'm looking for (a context manager for a cursor that can auto close it) is slightly different to a context manager for a connection that can auto commit write operations on the DB. – Basj Nov 25 '18 at 20:51
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 """)
howdoicode
  • 779
  • 8
  • 16