Using a "with" contextmanager with pysqlite

Question

I'm trying to use the "with" contextmanager using pysqlite:

>>> with conn.cursor() as db:
  res = db.execute("SELECT * FROM Publishers LIMIT 5;").fetchall()

... ... Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: __enter__

I see that the __enter__ and __exit__ methods are not defined, so a with contextmanager won't be available.

I also understand from the pysqlite documentation that the sqlite3 is a little quirky with respect to connections. In general, I would prefer use the context manager with python DB APIs per idiomatic python.

Does this suggest that I /should not/ try overloading and achieving a context manager? That there's something in the sqlite3 bindings that make this not advisable or not idiomatic?

Does this mean proper usage would be instantiating my cursor only one time (db = conn.cursor()) as a global and calling it (db.execute(query,params)) in each of my functions? Or that I must re-instantiate, call, and close the db in every function (db = conn.cursor(); db.query(query,params); db.close() and do so verbosely, lacking the context manager?

is there a reason you’re not using sqlite from the stdlib? — gold_cy, Jan 03 '19 at 22:18
also your questions depend on your use case, is your process idempotent? if not you’ll probably want one instance of a cursor so you can roll everything back and vice versa — gold_cy, Jan 03 '19 at 22:20
@aws_apprentice, yes, I'm using the fts5 and json modules, which you have to build yourself. So, my import ends up as from `pysqlite3 import dbapi2 as sqlite3` from http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/ — Mittenchops, Jan 03 '19 at 22:58

score 2 · Accepted Answer · answered Jan 04 '19 at 16:44

Per documentation, you use the connection as the context manager (with conn as db:), not the cursor to the connection (~~with conn.cursor() as db:~~):

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print "couldn't add Joe twice"

Using a "with" contextmanager with pysqlite

1 Answers1