MariaDB / MySQL INSERT INTO / ON DUPLICATE KEY UPDATE in Python

Question

I’m trying to INSERT INTO / ON DUPLICATE KEY UPDATE taking the values from one table and inserting into another. I have the following Python code.

try:
    cursor.execute("SELECT LocationId, ProviderId FROM CQCLocationDetailsUpdates")
    rows = cursor.fetchall()
    for row in rows:
        maria_cnxn.execute('INSERT INTO CQCLocationDetailsUpdates2 (LocationId, ProviderId) VALUES (%s,%s) ON DUPLICATE KEY UPDATE ProviderId = VALUES(%s)', row)
        mariadb_connection.commit()

except TypeError as error:
    print(error)
    mariadb_connection.rollback()

If I change this script just to INSERT INTO it work fine, the problem seems to be when I add the ON DUPLICATE KEY UPDATE. What do I have wrong? LocationId is the PRIMARY KEY

I get this error.

Traceback (most recent call last):
  File "C:/Users/waynes/PycharmProjects/DRS_Dev/CQC_Locations_Update_MariaDB.py", line 228, in <module>
    maria_cnxn.execute('INSERT INTO CQCLocationDetailsUpdates2 (LocationId, ProviderId) VALUES (%s,%s) ON DUPLICATE KEY UPDATE ProviderId = VALUES(%s)', row)
  File "C:\Users\waynes\PycharmProjects\DRS_Dev\venv\lib\site-packages\mysql\connector\cursor.py", line 548, in execute
    stmt = RE_PY_PARAM.sub(psub, stmt)
  File "C:\Users\waynes\PycharmProjects\DRS_Dev\venv\lib\site-packages\mysql\connector\cursor.py", line 79, in __call__
    "Not enough parameters for the SQL statement")
mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

Can you try that with the parameters being (*row, row[-1]) instead of row in the execute call? — shmee
– shmee, Commented Feb 27, 2019 at 7:58

danblack · Accepted Answer · 2019-02-27 08:00:58Z

1

Your error is because row is a 2 element tuple and your SQL statement requires three %s vars.

It is however possible to use an INSERT .. SELECT .. ON DUPLICATE KEY like:

maria_cnxn.execute('INSERT INTO CQCLocationDetailsUpdates2 (LocationId, 
 ProviderId)
 SELECT LocationId, ProviderId
 FROM CQCLocationDetailsUpdates orig
 ON DUPLICATE KEY UPDATE CQCLocationDetailsUpdates2.ProviderID = orig.ProviderID')

Whenever you end up doing a loop around a SQL statement you should look to see if there is a SQL way of doing this.

answered Feb 27, 2019 at 8:00

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MariaDB / MySQL INSERT INTO / ON DUPLICATE KEY UPDATE in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related