MySQL insert with List Comprehensions

Question

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'}]

You can use mappings with `executemany`. why not something like this -> cursor.executemany( "INSERT INTO `Orgs` (`sid`) VALUES (%(sid)s);", target_org) — sagarchalise, Nov 06 '15 at 15:29
@sagarchalise I just tried that and it worked perfectly, thank you. Is that appliable to my second example, when I have multiple fields? — Bernardo Meurer, Nov 06 '15 at 16:04

score 2 · Accepted Answer · answered Nov 06 '15 at 16:11

2

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

answered Nov 06 '15 at 16:11

sagarchalise

992
8
14

If on the `member` list (composed of dictionaries) I have `mem['sid'] for mem in member` but I want `['sid']`to be associated with the Org column would I do `...(%(sid)s)` or `...(%(Org)s)` – Bernardo Meurer Nov 06 '15 at 16:15
1

If your key is 'sid' it should be '%(sid)s' – sagarchalise Nov 06 '15 at 16:16
Okay, got it, testing it out now. – Bernardo Meurer Nov 06 '15 at 16:19
Hmm, I'm getting `There was a problem inserting member(s): (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '),'Recruit','visible',0,'main'),\n('alixander','imperium',(),'Fleet Member','visi' at line 2")` – Bernardo Meurer Nov 06 '15 at 16:28
`cursor.executemany("INSERT INTO `Citizens` (`Handle`,`Org`,`Role`, " "`Rank`,`Visibility`,`Stars`,`Type`) VALUES " "(%(handle)s,%(sid)s,%(roles)s,%(rank)s," "%(visibility)s,%(stars)s,%(type)s);", member)` – Bernardo Meurer Nov 06 '15 at 16:29
Please paste you 'member' variable sample on your question – sagarchalise Nov 06 '15 at 16:32
Good idea, should have done that from the beginning, my bad. Added. – Bernardo Meurer Nov 06 '15 at 16:35
I think your empty list with 'roles' is creating a problem. – sagarchalise Nov 06 '15 at 16:39
Darn it, I'll see if I can pre-process it somewhere else to make it be "None" when it's empty. – Bernardo Meurer Nov 06 '15 at 16:40
Okay, I treated the data so there are no empty lists, now this is coming along: There was a problem inserting member(s): (1241, 'Operand should contain 1 column(s)') – Bernardo Meurer Nov 06 '15 at 17:18
Please check what query is executed. You may need more data treatment as well. – sagarchalise Nov 06 '15 at 17:26
Was about to do that, this is what's being run: b"INSERT INTO `Citizens` (`Handle`,`Org`,`Role`, `Rank`,`Visibility`,`Stars`,`Type`) VALUES \n('freakyeagle','imperium','None','No SCB account','visible',2,'main'),\n('cadimus','imperium','None','Fleet Member','visible',1,'main'),\n('belleal','imperium','None','Fleet Member','visible',1,'main'),\n('boodan','imperium','None','Fleet Member','visible',1,'main'),\n('thorias','imperium','None','Fleet Member','visible',1,'main'),\n('valkrie','imperium','None','Fleet Member','visible',1,'main'), – Bernardo Meurer Nov 06 '15 at 17:27
So, I fixed it today, it was some mysterious bug with my filters that I am yet to comprehend entirely, but it's fixed. Thank's a lot for the help and patience! – Bernardo Meurer Nov 07 '15 at 20:53

MySQL insert with List Comprehensions

1 Answers1