何时使用 MySQLdb 关闭游标

2021-11-20 00:00:00 python mysql-python mysql

我正在构建一个 WSGI Web 应用程序并且我有一个 MySQL 数据库.我正在使用 MySQLdb，它提供用于执行语句和获取结果的游标.获取和关闭游标的标准做法是什么?特别是，我的游标应该持续多久?我应该为每笔交易获取一个新的游标吗?

I'm building a WSGI web app and I have a MySQL database. I'm using MySQLdb, which provides cursors for executing statements and getting results. What is the standard practice for getting and closing cursors? In particular, how long should my cursors last? Should I get a new cursor for each transaction?

我相信您需要在提交连接之前关闭游标.查找不需要中间提交的事务集是否有任何显着优势，这样您就不必为每个事务获取新游标?获取新游标的开销是不是很大，还是没什么大不了的?

I believe you need to close the cursor before committing the connection. Is there any significant advantage to finding sets of transactions that don't require intermediate commits so that you don't have to get new cursors for each transaction? Is there a lot of overhead for getting new cursors, or is it just not a big deal?

推荐答案

与其问什么是标准实践，因为这通常是不清楚和主观的，您可以尝试从模块本身寻求指导.一般来说，像其他用户建议的那样使用 with 关键字是个好主意，但在这种特定情况下，它可能无法提供您期望的功能.

Instead of asking what is standard practice, since that's often unclear and subjective, you might try looking to the module itself for guidance. In general, using the with keyword as another user suggested is a great idea, but in this specific circumstance it may not give you quite the functionality you expect.

从模块的 1.2.5 版本开始，MySQLdb.Connection 实现了 上下文管理器协议 使用以下代码(github):

As of version 1.2.5 of the module, MySQLdb.Connection implements the context manager protocol with the following code (github):

def __enter__(self):
    if self.get_autocommit():
        self.query("BEGIN")
    return self.cursor()

def __exit__(self, exc, value, tb):
    if exc:
        self.rollback()
    else:
        self.commit()

已有几个关于 with 的问答，或者您可以阅读 理解 Python 的 "with" 语句，但本质上发生的事情是 __enter__ 在 with 块的开头执行，而 __exit__ 在离开 with 块时执行.如果您打算稍后引用该对象，您可以使用可选语法 with EXPR as VAR 将 __enter__ 返回的对象绑定到一个名称.因此，鉴于上述实现，这里有一个简单的方法来查询您的数据库:

There are several existing Q&A about with already, or you can read Understanding Python's "with" statement, but essentially what happens is that __enter__ executes at the start of the with block, and __exit__ executes upon leaving the with block. You can use the optional syntax with EXPR as VAR to bind the object returned by __enter__ to a name if you intend to reference that object later. So, given the above implementation, here's a simple way to query your database:

connection = MySQLdb.connect(...)
with connection as cursor:            # connection.__enter__ executes at this line
    cursor.execute('select 1;')
    result = cursor.fetchall()        # connection.__exit__ executes after this line
print result                          # prints "((1L,),)"

现在的问题是，退出with块后，连接和游标的状态是什么?上面显示的 __exit__ 方法仅调用 self.rollback() 或 self.commit()，并且这些方法都不会继续调用close() 方法.游标本身没有定义 __exit__ 方法——即使有也没关系，因为 with 只是管理连接.因此，在退出 with 块后，连接和游标都保持打开状态.通过将以下代码添加到上述示例中，可以轻松确认这一点:

The question now is, what are the states of the connection and the cursor after exiting the with block? The __exit__ method shown above calls only self.rollback() or self.commit(), and neither of those methods go on to call the close() method. The cursor itself has no __exit__ method defined – and wouldn't matter if it did, because with is only managing the connection. Therefore, both the connection and the cursor remain open after exiting the with block. This is easily confirmed by adding the following code to the above example:

try:
    cursor.execute('select 1;')
    print 'cursor is open;',
except MySQLdb.ProgrammingError:
    print 'cursor is closed;',
if connection.open:
    print 'connection is open'
else:
    print 'connection is closed'

您应该看到输出cursor is open; connection is open"打印到标准输出.

You should see the output "cursor is open; connection is open" printed to stdout.

我相信您需要在提交连接之前关闭游标.

I believe you need to close the cursor before committing the connection.

为什么?MySQL C API，即MySQLdb 的基础，没有实现任何游标对象，正如模块文档中所暗示的:"MySQL 不支持游标；但是，游标很容易被模拟." 事实上，MySQLdb.cursors.BaseCursor 类直接继承自 object 并且在提交/回滚方面对游标没有这种限制.一位 Oracle 开发人员这样说:

Why? The MySQL C API, which is the basis for MySQLdb, does not implement any cursor object, as implied in the module documentation: "MySQL does not support cursors; however, cursors are easily emulated." Indeed, the MySQLdb.cursors.BaseCursor class inherits directly from object and imposes no such restriction on cursors with regard to commit/rollback. An Oracle developer had this to say:

在 cur.close() 之前的 cnx.commit() 对我来说听起来最合乎逻辑.可能是你可以遵循以下规则:如果不再需要，请关闭光标."因此在关闭游标之前 commit().最后，对于连接器/Python，它没有太大区别，但是或其他可能是数据库.

cnx.commit() before cur.close() sounds most logical to me. Maybe you can go by the rule: "Close the cursor if you do not need it anymore." Thus commit() before closing the cursor. In the end, for Connector/Python, it does not make much difference, but or other databases it might.

我希望这与您在此主题上的标准做法"一样接近.

I expect that's as close as you're going to get to "standard practice" on this subject.

查找不需要中间提交的事务集有什么显着优势，这样您就不必为每个事务获取新游标吗?

Is there any significant advantage to finding sets of transactions that don't require intermediate commits so that you don't have to get new cursors for each transaction?

我非常怀疑，在尝试这样做时，您可能会引入额外的人为错误.最好决定一个约定并坚持下去.

I very much doubt it, and in trying to do so, you may introduce additional human error. Better to decide on a convention and stick with it.

获取新游标是否有很多开销，或者只是没什么大不了的?

Is there a lot of overhead for getting new cursors, or is it just not a big deal?

开销可以忽略不计，根本不接触数据库服务器；它完全在 MySQLdb 的实现中.你可以查看github上的BaseCursor.__init__ 如果您真的很想知道创建新游标时发生了什么.

The overhead is negligible, and doesn't touch the database server at all; it's entirely within the implementation of MySQLdb. You can look at BaseCursor.__init__ on github if you're really curious to know what's happening when you create a new cursor.

回到之前我们讨论with的时候，也许现在你可以理解为什么MySQLdb.Connection类__enter__和__exit__ 方法在每个 with 块中为您提供一个全新的游标对象，并且不必费心跟踪它或在块的末尾关闭它.它相当轻巧，纯粹是为了您的方便.

Going back to earlier when we were discussing with, perhaps now you can understand why the MySQLdb.Connection class __enter__ and __exit__ methods give you a brand new cursor object in every with block and don't bother keeping track of it or closing it at the end of the block. It's fairly lightweight and exists purely for your convenience.

如果微观管理游标对象对您来说真的那么重要，您可以使用 contextlib.closure 来弥补游标对象没有定义的 __exit__ 方法的事实.就此而言，您还可以使用它来强制连接对象在退出 with 块时自行关闭.这应该输出my_curs 已关闭；my_conn 已关闭":

If it's really that important to you to micromanage the cursor object, you can use contextlib.closing to make up for the fact that the cursor object has no defined __exit__ method. For that matter, you can also use it to force the connection object to close itself upon exiting a with block. This should output "my_curs is closed; my_conn is closed":

from contextlib import closing
import MySQLdb

with closing(MySQLdb.connect(...)) as my_conn:
    with closing(my_conn.cursor()) as my_curs:
        my_curs.execute('select 1;')
        result = my_curs.fetchall()
try:
    my_curs.execute('select 1;')
    print 'my_curs is open;',
except MySQLdb.ProgrammingError:
    print 'my_curs is closed;',
if my_conn.open:
    print 'my_conn is open'
else:
    print 'my_conn is closed'

注意with closure(arg_obj) 不会调用参数对象的__enter__ 和__exit__ 方法；它将只在 with 块的末尾调用参数对象的 close 方法.(要查看实际情况，只需使用包含简单的 __enter__、__exit__ 和 close 方法定义一个类 Fooprint 语句，并将 with Foo(): pass 和 with closed(Foo()): pass 时发生的情况进行比较代码>.)这有两个重要意义:

Note that with closing(arg_obj) will not call the argument object's __enter__ and __exit__ methods; it will only call the argument object's close method at the end of the with block. (To see this in action, simply define a class Foo with __enter__, __exit__, and close methods containing simple print statements, and compare what happens when you do with Foo(): pass to what happens when you do with closing(Foo()): pass.) This has two significant implications:

首先，如果启用了自动提交模式，当您使用 with connection 时，MySQLdb 将在服务器上BEGIN 一个显式事务，并在结束时提交或回滚事务堵塞.这些是 MySQLdb 的默认行为，旨在保护您免受 MySQL 立即提交任何和所有 DML 语句的默认行为的影响.MySQLdb 假定当您使用上下文管理器时，您需要一个事务，并使用显式 BEGIN 绕过服务器上的自动提交设置.如果您习惯于使用 with connection，您可能会认为自动提交被禁用，而实际上它只是被绕过.如果您在代码中添加 closure 并失去事务完整性，您可能会遇到令人不快的惊喜；您将无法回滚更改，您可能会开始看到并发错误，并且可能不会立即发现原因.

First, if autocommit mode is enabled, MySQLdb will BEGIN an explicit transaction on the server when you use with connection and commit or rollback the transaction at the end of the block. These are default behaviors of MySQLdb, intended to protect you from MySQL's default behavior of immediately committing any and all DML statements. MySQLdb assumes that when you use a context manager, you want a transaction, and uses the explicit BEGIN to bypass the autocommit setting on the server. If you're used to using with connection, you might think autocommit is disabled when actually it was only being bypassed. You might get an unpleasant surprise if you add closing to your code and lose transactional integrity; you won't be able to rollback changes, you may start seeing concurrency bugs and it may not be immediately obvious why.

第二，with closed(MySQLdb.connect(user, pass)) as VAR 将连接对象绑定到VAR，与with MySQLdb.connect(user, pass) as VAR，它将一个新的游标对象绑定到VAR.在后一种情况下，您将无法直接访问连接对象！相反，您必须使用游标的 connection 属性，该属性提供对原始连接的代理访问.当游标关闭时，其connection 属性设置为None.这会导致废弃的连接一直存在，直到发生以下情况之一:

Second, with closing(MySQLdb.connect(user, pass)) as VAR binds the connection object to VAR, in contrast to with MySQLdb.connect(user, pass) as VAR, which binds a new cursor object to VAR. In the latter case you would have no direct access to the connection object! Instead, you would have to use the cursor's connection attribute, which provides proxy access to the original connection. When the cursor is closed, its connection attribute is set to None. This results in an abandoned connection that will stick around until one of the following happens:

  • 移除对游标的所有引用
  • 光标超出范围
  • 连接超时
  • 通过服务器管理工​​具手动关闭连接

您可以通过监控打开的连接(在 Workbench 中或通过 using SHOW PROCESSLIST)，同时逐行执行以下几行:

You can test this by monitoring open connections (in Workbench or by using SHOW PROCESSLIST) while executing the following lines one by one:

with MySQLdb.connect(...) as my_curs:
    pass
my_curs.close()
my_curs.connection          # None
my_curs.connection.close()  # throws AttributeError, but connection still open
del my_curs                 # connection will close here

相关文章