BUG: pymysql executemany不支持insert on duplicate key update

pymysql的executemany()方法支持传入单个SQL和一个sequence of records(sequence or mapping)来同时写入多条数据。

例如:

sql = "insert into t(c1, c2) values(%s, %s)"
args = [(1, 2), (3, 4)]
cursor.executemany(sql, args)
# If args is a list or tuple, %s can be used as a placeholder in the query.  
# If args is a dict, %(name)s can be used as a placeholder in the query.

这样可以同时将(1,2) (3,4)两条记录写入数据库,至于性能取决于executemany自身的实现,并不是说一定比多次execute()快,只能说有利于代码简化。

查看了下executemany的代码发现,executemany只对insert/replace语句有加速效果,只有这两种语句pymysql会一次性拼接完成发至mysql server端,其他语句依然是循环调用execute()。

executemany的问题:

经实测发现,pymysql的executemany 不支持insert into ... on duplicate key update ...语句, 而sqlalchemy支持。

当在pymysql executemany()中使用insert into ... on duplicate key update ...语句时,需要双倍的参数,例如:

cursor.executemany("insert into t(c1, c2) values(%s, %s) on duplicate key update c1=%s, c2=%s", [(1, 2)*2, (3, 4)*2])

按常规思维来看,我们已为每条记录的写入提供了全部需要的4个参数,sql应该可以正确被渲染、执行,但实测executemany()会报占位符渲染失败相关的错误:TypeError: not all arguments converted during string formatting

而我们使用execute()语句测试一切正常:

cursor.execute("insert into t(c1, c2) values(%s, %s) on duplicate key update c1=%s, c2=%s", (1, 2)*2)

查看executemany的代码可以发现,executemany仅对insert/replace语句有加速效果,这是因为针对这两种语句pymysql做了集体拼接,减少了多次执行的round-trip耗时,然而在集体拼接过程中,解析sql的正则表达式并没有去解析on duplicate key update之后的占位符,这导致这部分占位符无法被渲染。

替代之路有两条:

  1. 使用execute语句,pymysql的execute()经实测可以为insert into ... on duplicate key update ...语句正确渲染双倍参数。
  2. 使用mysql VALUES()函数可以在update字句中引用insert阶段的值:
cursor.executemany("insert into t(c1, c2) values(%s, %s) on duplicate key update c1=Values(c1), c2=Values(c2)", [(1, 2), (3, 4)])

我们只需要传输一遍字段值即可。

相关issue: New executemany() implementation supporting UPDATE query #506

PSs: 截止2024.9.11 最新的v1.1.1版本此问题依旧。

posted @ 2024-09-11 17:35  realcp1018  阅读(30)  评论(0编辑  收藏  举报