CODEWITHSUNDEEP
pythonmysql
Kshahnazari
Kshahnazari

Reputation: 135

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

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

Upvotes: 1

Views: 585

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55620

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).

Upvotes: 1

Related Questions