Using MySQL.connector with Twisted Python to execute multiple queries

Question

I recently had a Python 2.7x project where I needed to use mysql.connector to execute multiple, semicolon delineated statements in one query. This is explained nicely in the this post..

However, I needed to use mysql.connector with Twisted for my current project, which means using Twisted's excellent enterprise.adbapi module to make my new blocking database connection non-blocking.

config = {"user": username, "password": password, "host": hostname, "database": database_name, "raise_on_warnings": True} cp = adbapi.ConnectionPool("mysql.connector", **config)

my test statements are defined below. I apologize that they are a bit of a frivolous example, but I know the results that I expect, and it should be enough to verify that I'm getting results for multiple statements.

    statement1 = "SELECT * FROM queue WHERE id = 27;"
    statement2 = "SELECT * FROM order WHERE id = 1;"
    statement_list = [statement1, statement2]
    statements = " ".join(statement_list)

The problem comes when I now try to execute the ConnectionPool method .runQuery()

    def _print_result(result):
        if result:
            print("this is a result")
            print(result)
        else:
            print("no result")
        reactor.stop()

    d = cp.runQuery(statements, multi=True)
    d.addBoth(_print_result)

this gets me the following result:

this is a result [Failure instance: Traceback: : No result set to fetch from.

How can I use Twisted's adbapi module to get the results that I know are there?

Answer 1

So, it turns out that when using adbapi.ConnectionPool.runQuery(), the default behavior is to send the result of the database interrogation to the cursor.fetchall() method. However, when using mysql.connector, this doesn't work, even without twisted. Instead one needs to iterate over the result set, and call fetchall() on each member of the set.

So, the way I solved this was with the following subclass.

from twisted.enterprise import adbapi

class NEWadbapiConnectionPool(adbapi.ConnectionPool):

def __init__(self, dbapiName, *connargs, **connkw):
    adbapi.ConnectionPool.__init__(self, dbapiName, *connargs, **connkw)

def runMultiQuery(self, *args, **kw):
    return self.runInteraction(self._runMultiQuery, *args, **kw)

def _runMultiQuery(self, trans, *args, **kw):
    result = trans.execute(*args, **kw)
    result_list = []
    for item in result:
        if item.with_rows:
            result_list.append(item.fetchall())
    return result_list

so now I create the following:

    def _print_result(result):
        if result:
            print("this is a result")
            print(result)
        else:
            print("no result")
        reactor.stop()

    cp = NEWadbapiConnectionPool("mysql.connector", **config)
    d = cp.runMultiQuery(statements, multi=True)
    d.addBoth(_print_result)

and get a list of the results for each statement.

I hope someone else finds this useful.