MySQLdb with multiple transactions per connection

Question

Is it possible to use one MySQLdb connection for several 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() 

Everything seems to be in order, but I just wanted to double check.

Answer 1

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

In this example, one connection is opened, then one transaction is executed. You execute several SQL transactions in this transaction, but completely close them after committing. Of course, this is more than normal.

Performing multiple transactions (as opposed to just SQL statements) is as follows:

 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 makes 10 transactions, each of which consists of 10 separate delete operators.

And yes, you should be able to reuse an open connection for this without problems, unless you are sharing this connection between threads.

For example, SQLAlchemy reuses connections by merging them, distributing open connections as needed for the application. New transactions and new instructions are executed in these connections throughout the life of the application, without the need to close them until the application is closed.

Answer 2

Better to build a query string first and then execute this single MySQL statement. For instance:

 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)