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?

mbelconis · Accepted Answer · 2016-04-07 15:16:36Z

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.

Emilio · Accepted Answer · 2020-07-08 13:54:30Z

0

RunQuery always expects results. The right way to do it is to call runOperation() which does not fetch results.

If you want to use .runQuery, it expects results to fetch so you need to return something

dbpool.runQuery(
        "UPDATE something SET col1=true WHERE some_id=123 RETURNING *"
    )

.runOperation does not expect results

dbpool.runOperation(
        "UPDATE something SET col1=true WHERE some_id=123"
    )

answered Jul 8, 2020 at 13:54

Emilio

2,8221 gold badge26 silver badges26 bronze badges

Collectives™ on Stack Overflow

Using MySQL.connector with Twisted Python to execute multiple queries

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related