Search code examples
pythonmysqllistlist-comprehensionexecutemany

MySQL insert with List Comprehensions

I've been trying to convert most of my mysqlclient execute commands to be done in a single line by making use of List Comprehensions. An example of this is the following snippet:

def org(conn, cursor, target_org=None, target_sid=None):
    try:
        if target_sid is not None:
            target_org = [{"sid": target_sid}]
        cursor.executemany(
            "INSERT INTO `Orgs` (`sid`) VALUES (%s);",
            [[Org['sid']] for Org in target_org])
        conn.commit()

...

If I, however, wanted a MySQL query that had multiple columns to be filled such as 

"INSERT INTO `Citizens`(`Handle`,`Org`,`Role`,`Rank`,`Visibility`,`Stars`,`Type`)VALUE(%s,%s,%s,%s,%s,%s,%s)"

How could I implement a List Comprehension receiving a list member composed of lists with the data for each member to work with the executemany command like the example above?

For example, to grab the Handle column only it would look something like

[Mem['handle'] for Mem in member]
# (cursor.executemany must always have a list as second argument)

member sample:

[{'roles': [], 'rank': 'No SCB account', 'type': 'main', 'stars': 2, 'visibility': 'visible', 'sid': 'imperium', 'handle': 'freakyeagle'}, {'roles': [], 'rank': 'Fleet Member', 'type': 'main', 'stars': 1, 'visibility': 'visible', 'sid': 'imperium', 'handle': 'cadimus'}, {'roles': [], 'rank': 'Fleet Member', 'type': 'main', 'stars': 1, 'visibility': 'visible', 'sid': 'imperium', 'handle': 'belleal'}]
Solution

  • You can use mappings with executemany. Interpolation will work based on your dictionary. Since your target_org is list of dictionary with key 'sid' so you can do something like:

    cursor.executemany( "INSERT INTO Orgs (sid) VALUES (%(sid)s);", target_org)

    I think this will work for your second problem as well.

    See: https://www.python.org/dev/peps/pep-0249/#paramstyle for parameter in python db-api