SQL DUPLICATE KEY UPDATE Execute many fails with not all arguments converted during string formatting

Question

I'm trying to use the MYSQL execute many but I can't figure out why I'm getting the error below:

my code:

import mysql.connector
import pymysql

mydb = pymysql.connect(
    host="",
    user="",
    passwd="",
    database="",
)
mycursor = mydb.cursor()
sqlinputs = [(1, 2, 3, 4), (5, 6, 7, 8)]
Suc = mycursor.executemany(
    "INSERT INTO DotaMatches (GameID,GameSEQ,skill_level) Values (%s,%s,%s) ON DUPLICATE KEY UPDATE skill_level= %s ",
    sqlinputs,
)

The exception I am getting is

Exception has occurred: TypeError
not all arguments converted during string formatting
  File "/home/khashayar/Desktop/projects/DotaPredictor/Dota2/trr.py", line 12, in <module>
    Suc = mycursor.executemany(

I tried different things. removing the ON DUPLICATE part and passing 3 tuples of size 3 works fine without any error. Executing them row by mycursor.execute(query,sqlinputs[0]) works fine. The issue is only when I'm adding the ON DUPLICATE part and putting it inside the executemany command. I tried many things but I can't seem to find the problem. The types in the SQL are all INT type but changing the input here str or int doesn't help aswell. The code below works without any problem.

sqlinputs = (1, 2, 3, 4)
Suc = mycursor.execute(
    "INSERT INTO DotaMatches (GameID,GameSEQ,skill_level) Values (%s,%s,%s) ON DUPLICATE KEY UPDATE skill_level= %s ",
    sqlinputs,
)

python version is 3.8.3

The beginning of the error message should show the compiled statement and hint at what’s wrong. Please share the full error message. — s3dev
– s3dev, Commented Nov 15, 2020 at 8:29
I added the full error message. Nothing else is being printed out. — Kshahnazari
– Kshahnazari, Commented Nov 15, 2020 at 8:42

snakecharmerb · Accepted Answer · 2020-11-15 14:52:17Z

1

This looks like a bug in PyMySQL, which the maintainers do not intend to fix.

For the use case in the question - inserting a value into a column but updating the column with a different value if a duplicate key is encountered - the simplest solution is to loop over the input list and execute each set of values individually:

for inp in sqlinputs:
    cursor.execute(sql_stmt, inp)

If the use cause is to assign the same value to the column(s) whether inserting or updating, then the method in this answer may be applied (the answer is for MySQLdb, but it works for PyMySQL too).

answered Nov 15, 2020 at 14:52

snakecharmerb

57.2k13 gold badges137 silver badges200 bronze badges

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

Collectives™ on Stack Overflow

SQL DUPLICATE KEY UPDATE Execute many fails with not all arguments converted during string formatting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related