PyMySQL executemany with ON DUPLICATE

Question

I have a list composed of dictionaries called member that I was inserting on a database like so

    # Executes query for each dictionary in member.
    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)
    # Commits changes to the database.
    conn.commit()

It worked perfectly, but as I began having issues with duplicate members I decided to add an ON DUPLICATE clause. The idea is that if a member is duplicate we want to update his Org column by concatenating the new data. So I changed the code to this

    # Executes query for each dictionary in member.
    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) ON DUPLICATE"
                       " KEY UPDATE `Org`=concat(ifnull(`Org`, \"\"), "
                       "\", \", %(sid)s);", member)
    # Commits changes to the database.
    conn.commit()

However in doing so I got the following error:

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 '('real_lethality','000',0,'Master','visible',5,'affiliate') ON DUPLICATE KEY UPD' at line 3")
Would you like to see the failed SQL query?[Y/n]
b'INSERT INTO `Citizens` (`Handle`,`Org`,`Role`, `Rank`,`Visibility`,`Stars`,`Type`) VALUES \n(\'fullmetaljim\',\'000\',\'Founder\',\'Master\',\'visible\',5,\'main\') ON DUPLICATE KEY UPDATE `Org`=concat(ifnull(`Org`, ""), ", ", \'000\'),\n(\'real_lethality\',\'000\',0,\'Master\',\'visible\',5,\'affiliate\') ON DUPLICATE KEY UPDATE `Org`=concat(ifnull(`Org`, ""), ", ", \'000\')\n;'

Have I implemented the ON DUPLICATE wrongly? How can I make this work correctly?

Sample member:

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

EDIT: As it seems this might be a bug in PyMySQL itself, would anyone be able to confirm so?

correct me if I m wrong - if duplicate key exists you want to update its org column only with all the new values conctenated and that too only if org column is null (since you used 'ifnull')..i.e org will contain all the new values together if its null.. — Manoj Salvi, Nov 24 '15 at 18:02
@ManojSalvi, almost correct. If there is a duplicate key the new values of *only* the `Org` column should be concatenated to the original ones. Is my `ifnull` misplaced? — Bernardo Meurer, Nov 24 '15 at 18:04

score 1 · Answer 1 · answered Nov 23 '15 at 14:26

Looking at your code and query from the error I'd agree that the constructed query by executemany method is incorrect. I'd recommend you build your insert in more manual manner looping through the parameters and just execute it without executemany method.

Also MySQL AFAIK is perfectly ok with using single quotes so if you put query in double quotes use single quotes as internal string qualifiers - this way you don't need to escape all the time.

genespos · Answer 2 · 2015-11-25T19:21:29.350

I think there is a syntax error in:

ON DUPLICATE KEY UPDATE `Org`=concat(ifnull(`Org`, ""), ", ",

You need two double quotes (or two single quote)

ON DUPLICATE KEY UPDATE `Org`=concat(ifnull(`Org`, ""), "", "",

Pay attention it seems you have two times this issue because the command is repeated (??)

This is the query you posted:

INSERT INTO `Citizens` (`Handle`,`Org`,`Role`, `Rank`,`Visibility`,`Stars`,`Type`)
VALUES \n(\'fullmetaljim\',\'000\',\'Founder\',\'Master\',\'visible\',5,\'main\')
ON DUPLICATE KEY UPDATE `Org`=concat(ifnull(`Org`, ""), ", ", \'000\'),\n(\'real_lethality\',\'000\',0,\'Master\',\'visible\',5,\'affiliate\')
ON DUPLICATE KEY UPDATE `Org`=concat(ifnull(`Org`, ""), ", ", \'000\')\n;

I think you might be mistaken. I wanted to concatenate `', '` to the string in order for the final effect to be something of the kind `Org1, Org2, Org3`. In your suggestion I'm just concatenating two empty spaces up to no effect, correct? — Bernardo Meurer, Nov 26 '15 at 17:42

score 0 · Answer 3 · answered Jun 28 '16 at 15:51

I do not know if the problem is already solved but I recently had to the run the query

    **INSERT INTO .... ON DUPLICATE KEY UPDATE**

    // Assuming conn as a connection to a MySQL database and member a dict with the required variables
    cursor = conn.cursor()

    query = (member['handle'], member['org'], member['role'], member['rank'], 
              member['visibility'], member['stars'], member['type']) // Tuple object of the required values from the dict

    cursor.execute("""
                      INSERT INTO Citizens 
                      (Handle,Org,Role,Rank,Visibility,Stars,Type)
                      VALUES(%s,%s,%s,%s,%s,%s,%s)
                      Handle = VALUES(Handle)
                      Org= VALUES(Org)
                      Role= VALUES(Role)
                      Rank= VALUES(Rank)
                      Visibility= VALUES(Visibility)
                      Stars= VALUES(Stars)
                      Type= VALUES(Type)
                   """, query)
    conn.commit()

PyMySQL executemany with ON DUPLICATE

3 Answers3