python - How to solve pymysql.err.programmingError during upload using pymysql

Question

I want to create a dataframe and update it to mysql. If there is a duplicate key, it will be updated and if there is no duplicate key, it will be inserted.

user = 'test'
passw = '...'
host = '...'
port = '...'
database = '...'
conn = pymysql.connect(host=host,
                           port=port,
                           user=user,
                           password=passw,
                           database=database,
                           charset='utf8')
curs = conn.cursor()
data = list(dataframe.itertuples(index=False, name=None))

sql = "insert into naversbmapping(brand, startdate, enddate, cost, daycost) values (%s, %s, %s, %s, %s) on duplicate key update brand = %s, startdate = %s, enddate = %s, cost = %s, daycost = %s"
curs.executemany(sql, data)
conn.commit()
conn.close()

However, I get the following error. How do I fix it?

pymysql.err.ProgrammingError: (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 '%s, startdate = %s, enddate = %s, cost = %s, daycost = %s' at line 1")
)

question from:https://stackoverflow.com/questions/66061064/how-to-solve-pymysql-err-programmingerror-during-upload-using-pymysql

Answer 1

You use following MySQL constriuct so that you don't need the data twice as you have the double number of values on your original, but are only sending it once

$sql = "INSERT INTO naversbmapping(brand, startdate, enddate, cost, daycost) VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE brand = VALUES(brand), startdate = VALUES(startdate), enddate = VALUES(enddate), cost = VALUES(cost), daycost = VALUES(daycost)")