Python的MySQLdb的上下文管理器 [英] Context manager for Python's MySQLdb

问题描述

我习惯(宠坏了?)python的 SQLite 接口来处理SQL数据库. python的SQLite API中的一个不错的功能是上下文管理器"，即python的with语句.我通常以以下方式执行查询:

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

使用上面的代码，如果我的查询修改了数据库并且忘记了运行conn.commit()，则上下文管理器在退出with语句时自动为我运行它.它还可以很好地处理异常:如果在我提交任何内容之前发生异常，则数据库将回滚.

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.

我现在正在使用MySQLdb界面，该界面似乎不支持开箱即用的类似上下文管理器.我如何创建自己的?在此处，有一个相关问题，但是它没有提供完整的解决方案.

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.

推荐答案

以前，MySQLdb连接是上下文管理器. 从此提交日期为2018年12月4日，但是，MySQLdb连接不是较长的上下文管理器 并且用户必须显式调用conn.commit()或conn.rollback()，或编写自己的上下文管理器，例如下面的管理器.

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)

要使用它，您可以将config.py放在PYTHONPATH中，并在其中定义HOST，USER，PASS和MYDB变量.

To use it you would place config.py in your PYTHONPATH and define the HOST, USER, PASS, MYDB variables there.

这篇关于Python的MySQLdb的上下文管理器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！