I met an error when I run
import datetime
import json
import pymysql
config={
'host':...,
'user':...,
'password':...,
'database':...,
'charset':'utf8mb4',
'cursorclass':pymysql.cursors.DictCursor,
}
with pymysql.connect(**config) as conn:
with conn.cursor() as cur:
sql="""
create table if not exists test(
route VARCHAR(8),
direction INT,
timestamp DATETIME,
stations JSON,
PRIMARY KEY (route, direction)
)
"""
cur.execute(sql, )
now=datetime.datetime.now()
j=json.dumps({})
ret1={
'route': 'N2',
'direction': 0,
'timestamp': now,
'stations' : j,
}
ret2={
'route': 'N1',
'direction': 0,
'timestamp': now,
'stations' : j,
}
sql="""
insert into test
values ( %(route)s, %(direction)s, %(timestamp)s, %(stations)s )
on duplicate key update timestamp=%(timestamp)s , stations=%(stations)s
"""
# These two lines will runs as expected,
# cur.execute(sql, ret1)
# conn.commit()
# but these two will raise an error.
cur.executemany(sql, [ret1,ret2])
conn.commit()
The error message is like
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 '%(timestamp)s , stations=%(stations)s' at line 3")
Is this a bug of pymysql or my mistake?
pymysql version "1.1.1"
python version "3.12.8"
mysql version "8.0.41-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))"
Don't use placeholders in the ON DUPLICATE KEY UPDATE clause. executemany() gets its benefit in INSERT statements by duplicating the VALUES list with each dictionary in the parameters list, so the query looks like:
INSERT INTO test
VALUES (...), (...)
But you can't replicate the ON DUPLICATE UPDATE clause similarly, so it leaves the placeholders in the query, which results in a syntax error.
Instead, you can tell MySQL to gets the values to update from the VALUES clause automatically.
sql="""
insert into test
values ( %(route)s, %(direction)s, %(timestamp)s, %(stations)s ) AS new
on duplicate key update timestamp=new.timestamp, stations=new.stations
"""
If you need to support MySQL 5.x, use VALUES(columnName) instead of new.columnName.