使用MySQLdb何时关闭游标

97 投票

5 回答

104689 浏览

提问于 2025-04-16 15:46

我正在开发一个WSGI网页应用，并且使用了MySQL数据库。我用的是MySQLdb，它提供了游标（cursors）来执行语句和获取结果。获取和关闭游标的标准做法是什么呢？特别是，我的游标应该持续多久？每次交易都需要获取一个新的游标吗？

我认为在提交连接之前需要关闭游标。有没有什么明显的好处是可以找到一些不需要中间提交的交易，这样就不需要为每个交易获取新的游标？获取新游标会不会有很多额外的开销，还是说其实没什么大不了的？

mysql database connection wsgi application cursor management transaction handling resource optimization performance considerations

5 个回答

^{注意：这个回答是针对PyMySQL的，它可以替代MySQLdb，并且实际上是MySQLdb的最新版本，因为MySQLdb已经停止维护。我相信这里的内容对旧版MySQLdb也是适用的，但我没有确认。}

首先，有一些事实：

Python的with语法在执行with块的内容之前，会调用上下文管理器的__enter__方法，之后再调用__exit__方法。
连接有一个__enter__方法，它除了创建并返回一个游标外，不做其他事情，还有一个__exit__方法，它会根据是否抛出异常来决定是提交还是回滚，但并不会关闭连接。
在PyMySQL中，游标完全是用Python实现的抽象概念；在MySQL本身并没有对应的概念。¹
游标有一个__enter__方法，它不做任何事情，还有一个__exit__方法，它“关闭”游标（这只是意味着将游标对其父连接的引用置为无效，并丢弃存储在游标上的任何数据）。
游标持有对创建它们的连接的引用，但连接并不持有对它们创建的游标的引用。
连接有一个__del__方法，用于关闭连接。
根据https://docs.python.org/3/reference/datamodel.html，CPython（默认的Python实现）使用引用计数，当一个对象的引用数量降到零时，会自动删除该对象。

把这些信息结合起来，我们可以看到像下面这样的简单代码在理论上是有问题的：

# Problematic code, at least in theory!
import pymysql
with pymysql.connect() as cursor:
    cursor.execute('SELECT 1')

# ... happily carry on and do something unrelated

问题在于没有人关闭连接。实际上，如果你把上面的代码粘贴到Python命令行中，然后在MySQL命令行中运行SHOW FULL PROCESSLIST，你会看到你创建的空闲连接。由于MySQL的默认连接数是151，这个数字并不大，如果有很多进程保持这些连接打开，你可能会遇到问题。

然而，在CPython中，有一个救命稻草，确保像我上面的例子这样的代码可能不会导致你留下大量的打开连接。这个救命稻草是，一旦cursor超出作用域（例如，创建它的函数结束，或者cursor被赋值为其他值），它的引用计数就会降到零，这会导致它被删除，从而使连接的引用计数降到零，进而调用连接的__del__方法，强制关闭连接。如果你已经把上面的代码粘贴到你的Python命令行中，你可以通过运行cursor = 'arbitrary value'来模拟这一点；一旦你这样做，打开的连接就会从SHOW PROCESSLIST的输出中消失。

不过，依赖这个方法并不优雅，理论上在CPython以外的Python实现中可能会失败。更好的做法是明确地调用.close()来关闭连接（以便在不等待Python销毁对象的情况下释放数据库上的连接）。更稳健的代码如下：

import contextlib
import pymysql
with contextlib.closing(pymysql.connect()) as conn:
    with conn as cursor:
        cursor.execute('SELECT 1')

这虽然不太好看，但不依赖于Python自动销毁你的对象来释放（有限的可用）数据库连接。

注意，如果你已经像这样明确关闭连接，关闭游标是完全没有意义的。

最后，来回答这里的附加问题：

获取新游标是否有很大的开销，还是说这并不算什么大事？

并没有，实例化一个游标根本不会影响MySQL，基本上什么都不做。

有没有什么显著的好处，找到不需要中间提交的事务集合，这样你就不必为每个事务获取新游标？

这要看情况，很难给出一个普遍的答案。正如https://dev.mysql.com/doc/refman/en/optimizing-innodb-transaction-management.html所说，“如果一个应用程序每秒提交数千次，可能会遇到性能问题，而如果每2-3小时才提交一次，则会遇到不同的性能问题”。每次提交都会有性能开销，但如果长时间保持事务打开，你会增加其他连接等待锁的时间，增加死锁的风险，并可能增加其他连接执行某些查找的成本。

^{¹ MySQL确实有一个叫做游标的结构，但它们只存在于存储过程内部；与PyMySQL的游标完全不同，这里不相关。}

回答于 2025-04-16 由 Python大师

分享举报

用'with'关键字来重写代码会更好。'with'可以自动处理关闭游标的问题（这很重要，因为游标是一个不受管理的资源）。这样做的好处是，即使出现异常，它也会自动关闭游标。

from contextlib import closing
import MySQLdb

''' At the beginning you open a DB connection. Particular moment when
  you open connection depends from your approach:
  - it can be inside the same function where you work with cursors
  - in the class constructor
  - etc
'''
db = MySQLdb.connect("host", "user", "pass", "database")
with closing(db.cursor()) as cur:
    cur.execute("somestuff")
    results = cur.fetchall()
    # do stuff with results

    cur.execute("insert operation")
    # call commit if you do INSERT, UPDATE or DELETE operations
    db.commit()

    cur.execute("someotherstuff")
    results2 = cur.fetchone()
    # do stuff with results2

# at some point when you decided that you do not need
# the open connection anymore you close it
db.close()

回答于 2025-04-16 由 Python大师

分享举报

与其问什么是标准做法，因为这往往不太明确且主观，不如直接看看模块本身的说明。一般来说，像其他用户提到的那样使用 with 关键字是个好主意，但在这个特定情况下，它可能无法提供你预期的功能。

从模块的 1.2.5 版本开始，MySQLdb.Connection 实现了上下文管理器协议，具体代码如下 (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 块时执行。如果你打算稍后引用 __enter__ 返回的对象，可以使用可选的语法 with EXPR as VAR 来将其绑定到一个名称。因此，基于上述实现，这里有一个简单的查询数据库的方法：

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 块后，连接和游标都保持打开状态。你可以通过在上面的示例中添加以下代码来轻松确认这一点：

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'

你应该会看到输出 "游标是打开的；连接是打开的" 打印到标准输出。

我认为你需要在提交连接之前关闭游标。

为什么呢？MySQL C API 是 MySQLdb 的基础，并没有实现任何游标对象，正如模块文档中所暗示的那样："MySQL 不支持游标；但是，游标可以很容易地模拟。" 确实，MySQLdb.cursors.BaseCursor 类直接继承自 object，并且对游标在提交/回滚方面没有任何限制。一位 Oracle 开发者这样说：

在 cur.close() 之前调用 cnx.commit() 对我来说是最合逻辑的。也许你可以遵循这个规则：“如果不再需要游标，就关闭它。”因此在关闭游标之前调用 commit()。最终，对于 Connector/Python，这并没有太大区别，但对于其他数据库可能会有影响。

我想这就是你能得到的关于这个主题的“标准做法”了。

有没有什么显著的好处去寻找不需要中间提交的事务集，这样你就不必为每个事务获取新的游标？

我对此非常怀疑，试图这样做可能会引入额外的人为错误。最好是决定一个约定并坚持下去。

获取新游标是否有很大的开销，还是说这并不算什么大事？

开销是微不足道的，并不会影响数据库服务器；这完全是在 MySQLdb 的实现内部。如果你真的好奇创建新游标时发生了什么，可以查看 BaseCursor.__init__ 在 github 上。

回到之前讨论的 with，也许现在你可以理解为什么 MySQLdb.Connection 类的 __enter__ 和 __exit__ 方法在每个 with 块中都会给你一个全新的游标对象，而不去跟踪它或在块结束时关闭它。这是相对轻量的，纯粹是为了方便你。

如果你真的很在意管理游标对象，可以使用 contextlib.closing 来弥补游标对象没有定义 __exit__ 方法的事实。实际上，你也可以用它来强制连接对象在退出 with 块时关闭自己。这应该输出 "my_curs 已关闭；my_conn 已关闭"：

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 closing(arg_obj) 不会调用参数对象的 __enter__ 和 __exit__ 方法；它只会在 with 块结束时调用参数对象的 close 方法。（要查看这一点的实际效果，只需定义一个包含简单 print 语句的类 Foo，并比较 with Foo(): pass 和 with closing(Foo()): pass 的结果。）这有两个重要的影响：

首先，如果启用了自动提交模式，当你使用 with connection 时，MySQLdb 会在服务器上 BEGIN 一个显式事务，并在块结束时提交或回滚该事务。这是 MySQLdb 的默认行为，旨在保护你免受 MySQL 默认行为的影响，即立即提交所有 DML 语句。MySQLdb 假设当你使用上下文管理器时，你想要一个事务，并使用显式的 BEGIN 来绕过服务器上的自动提交设置。如果你习惯使用 with connection，你可能会认为自动提交是禁用的，实际上它只是被绕过了。如果你在代码中添加了 closing，并失去了事务完整性，你可能会感到不愉快；你将无法回滚更改，可能会开始看到并发错误，并且可能不容易发现原因。

其次，with closing(MySQLdb.connect(user, pass)) as VAR 将 连接对象 绑定到 VAR，而 with MySQLdb.connect(user, pass) as VAR 则将 一个新的游标对象 绑定到 VAR。在后者的情况下，你将无法直接访问连接对象！相反，你必须使用游标的 connection 属性，这提供了对原始连接的代理访问。当游标关闭时，它的 connection 属性被设置为 None。这导致一个被遗弃的连接，直到以下情况之一发生：

游标的所有引用都被移除
游标超出作用域
连接超时
通过服务器管理工具手动关闭连接

你可以通过在执行以下代码时逐行监控打开的连接（在 Workbench 中或通过使用 SHOW PROCESSLIST）来测试这一点：

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

回答于 2025-04-16 由 Python大师

分享举报

使用MySQLdb何时关闭游标

5 个回答

撰写回答