何时使用MySQLdb关闭游标 [英] When to close cursors using MySQLdb

问题描述

我正在构建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'

您应该看到输出到标准输出的光标已打开；连接已打开".

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的基础不实现任何游标对象，如模块文档中所述:确实，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.

如果对微管理光标对象确实很重要，则可以使用

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 closing(arg_obj)不会调用参数对象的__enter__和__exit__方法；它将仅在with块的末尾调用参数对象的close方法. (要查看实际情况，只需使用包含简单print语句的__enter__，__exit__和close方法定义类Foo，然后将执行with Foo(): pass时发生的情况与您进行比较时的情况进行比较做with closing(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，则可能会认为实际上仅绕过自动提交已被禁用.如果在代码中添加closing并失去事务完整性，可能会令人不快.您将无法回滚更改，您可能会开始看到并发性错误，并且可能并不清楚为什么.

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 closing(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中或通过使用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

这篇关于何时使用MySQLdb关闭游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！