PyMySQL executemany with DUPLICATE enabled

Question

I have a list consisting of dictionaries memberthat I inserted into such a database

    # 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 fine, but when I had problems with duplicate members, I decided to add a sentence ON DUPLICATE. The idea is that if a member is duplicated, we want to update its column Orgby combining 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, I received 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;'

Am I doing it wrong ON DUPLICATE? How can I do this job right?

An example 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: It seems like this might be a PyMySQL error itself, can anyone confirm this?

Answer 1

, , executemany . , - executemany.

MySQL AFAIK , , , - .

Answer 2

, :

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

( )

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

, , (??)

:

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;

Answer 3

, ,

    **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()