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:

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

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

It seems to work okay, but I just wanted to double check.

它似乎工作正常，但我只是想仔细检查。

2 个解决方案

#1

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

我认为这里有什么构成交易存在误解。

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:

执行多个事务（而不仅仅是SQL语句），如下所示：

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

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

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

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.

是的，只要您不在线程之间共享该连接，您应该能够毫无问题地重新使用打开的连接。

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.

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

#2

It would be better to first build a query string and then execute that single MySQL statement. For example:

最好先构建一个查询字符串，然后再执行该单个MySQL语句。例如：

query = "DELETE FROM table_name WHERE id IN ("
for i in range(10):
    query = query + "'" + str(i) + "', "
query = query[:-2] + ')'

cur = conn.cursor()
cur.execute(query)

#1