在 Python 中一次执行多个 MySQL 插入 [英] Executing multiple MySQL inserts at once in Python

问题描述

我有一个字符串，它基本上是多个插入语句的串联，例如

I have a string that is basically a concatenation of multiple insert statements such as

sql = INSERT INTO test (a,b,c) VALUES ("test","test",1);INSERT INTO test (a,b,c) VALUES ("2nd test","2nd test",6);

当我在 SQL 中将它作为查询运行时，它工作正常并插入两个语句.

When I run this in SQL as a query, it works fine and inserts both statements.

但是，当我使用以下命令在 python 中运行它时:

However, when I run it in python using the following:

cursor = db.cursor()
sql = INSERT INTO test (a,b,c) VALUES ("test","test",1);INSERT INTO test (a,b,c) VALUES ("2nd test","2nd test",6);
cursor.execute(sql)
db.commit()

我收到此错误:

ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

解决此问题并一次性执行多个语句的最佳方法是什么?

Whats the best way to resolve this and execute multiple statements in one go?

谢谢！

推荐答案

这个错误是关于 cursor.execute 每次运行只能处理一个 sql 的事实.你要么想循环它:

This error is about the fact that cursor.execute can handle only one sql per run. you either want to loop it:

sql = 'INSERT INTO test (a,b,c) VALUES (%s, %s, %s)'
for values in [("test","test",1), ("2nd test","2nd test",6)]
    cursor.execute(sql, values)

或立即执行:

sql = 'INSERT INTO test (a,b,c) VALUES ("test","test",1),("2nd test","2nd test",6)'
cursor.execute(sql)

这篇关于在 Python 中一次执行多个 MySQL 插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！