I am used to (spoiled by?) python's SQLite interface to deal with SQL databases. One nice feature in python's SQLite's API the "context manager," i.e., python's with
statement. I usually execute queries in the following way:
import as sqlite
with sqlite.connect(db_filename) as conn:
query = "INSERT OR IGNORE INTO shapes VALUES (?,?);"
results = conn.execute(query, ("ID1","triangle"))
With the code above, if my query modifies the database and I forget to run conn.commit()
,the context manager runs it for me automatically upon exiting the with
statement. It also handles exceptions nicely: if an exception occurs before I commit anything, then the database is rolled back.
I am now using the MySQLdb
interface, which doesn't seem to support a similar context manager out of the box. How do I create my own? There is a related question here, but it doesn't offer a complete solution.
Python provides an easy way to manage resources: Context Managers. The with keyword is used. When it gets evaluated it should result in an object that performs context management. Context managers can be written using classes or functions(with decorators).
MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2. 0 and is built on top of the MySQL C API. Packages to Install mysql-connector-python mysql-python.
Previously, MySQLdb connections were context managers. As of this commit on 2018-12-04, however, MySQLdb connections are no longer context managers, and users must explicitly call conn.commit() or conn.rollback(), or write their own context manager, such as the one below.
You could use something like this:
import config
import MySQLdb
import MySQLdb.cursors as mc
import _mysql_exceptions
import contextlib
DictCursor = mc.DictCursor
SSCursor = mc.SSCursor
SSDictCursor = mc.SSDictCursor
Cursor = mc.Cursor
@contextlib.contextmanager
def connection(cursorclass=Cursor,
host=config.HOST, user=config.USER,
passwd=config.PASS, dbname=config.MYDB,
driver=MySQLdb):
connection = driver.connect(
host=host, user=user, passwd=passwd, db=dbname,
cursorclass=cursorclass)
try:
yield connection
except Exception:
connection.rollback()
raise
else:
connection.commit()
finally:
connection.close()
@contextlib.contextmanager
def cursor(cursorclass=Cursor, host=config.HOST, user=config.USER,
passwd=config.PASS, dbname=config.MYDB):
with connection(cursorclass, host, user, passwd, dbname) as conn:
cursor = conn.cursor()
try:
yield cursor
finally:
cursor.close()
with cursor(SSDictCursor) as cur:
print(cur)
connection = cur.connection
print(connection)
sql = 'select * from table'
cur.execute(sql)
for row in cur:
print(row)
To use it you would place config.py
in your PYTHONPATH and define the HOST, USER, PASS, MYDB variables there.
Think things have changed since this question was originally asked. Somewhat confusingly (from my point of view at least), for recent versions of MySQLdb
, if you use a connection in a context you get a cursor (as per the oursql
example), not something that closes automatically (as you would if you opened a file for instance).
Here's what I do:
from contextlib import closing
with closing(getConnection()) as conn: #ensure that the connection is closed
with conn as cursor: #cursor will now auto-commit
cursor.execute('SELECT * FROM tablename')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With