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?
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"