MySQLdb每个连接有多个事务 [英] MySQLdb with multiple transaction per connection

问题描述

是否可以对多个事务使用单个MySQLdb连接，而不关闭它们之间的连接？换句话说，这样的：

Is it okay to use a single MySQLdb connection for multiple transactions without closing the connection between them? In other words, something like this:

conn = MySQLdb.connect(host="1.2.3.4", port=1234, user="root", passwd="x", db="test")

for i in range(10):
    try:
        cur = conn.cursor()
        query = "DELETE FROM SomeTable WHERE ID = %d" % i
        cur.execute(query)
        cur.close()
        conn.commit()

    except Exception:
        conn.rollback()

conn.close()

$ b

推荐答案

我认为这里有一个误解，说明什么是交易。

I think there is a misunderstanding about what constitutes a transaction here.

您的示例打开一个连接，然后在其上执行一个事务。您在该事务中执行多个SQL语句，但在提交后完全关闭它。

Your example opens up one connection, then executes one transaction on it. You execute multiple SQL statements in that transaction, but you close it completely after committing. Of course that's more than fine.

执行多个事务（而不是只是SQL语句），看起来像这样：

Executing multiple transactions (as opposed to just SQL statements), looks like this:

conn = MySQLdb.connect(host="1.2.3.4", port=1234, user="root", passwd="x", db="test")

for j in range(10):
    try:
        for i in range(10):
            cur = conn.cursor()
            query = "DELETE FROM SomeTable WHERE ID = %d" % i
            cur.execute(query)
            cur.close()
        conn.commit()
    except Exception:
        conn.rollback()

conn.close()

$ b b

上面的代码提交了10个事务，每个事务由10个单独的delete语句组成。

The above code commits 10 transactions, each consisting of 10 individual delete statements.

是的，你应该能够重用open没有问题，只要你不在线程之间共享该连接。

And yes, you should be able to re-use the open connection for that without problems, as long as you don't share that connection between threads.

例如， SQLAlchemy 通过合并它们来重新使用连接，根据需要将打开的连接提交给应用程序。在应用程序的整个生命周期内，在这些连接上执行新的事务和新语句，而无需在应用程序关闭之前关闭。

For example, SQLAlchemy re-uses connections by pooling them, handing out open connections as needed to the application. New transactions and new statements are executed on these connections throughout the lifetime of an application, without needing to be closed until the application is shut down.

这篇关于MySQLdb每个连接有多个事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！