Python mysql-connector 多插入不工作? [英] Python mysql-connector multi insert not working?

问题描述

我刚刚开始使用适用于 Python 的 Mysql 连接器库，但似乎遇到了一个无法解决的问题.

I just started using the Mysql Connector library for Python and I seem to be running into an issue I can't get my head around.

我让 Python 动态生成要在 MySQl 数据库上执行的 Insert 语句.

I have Python dynamically generate Insert statements to be executed on MySQl database.

我在一个字符串中将它们批处理在一起，然后在一个 cusrsor.execute() 语句中一起执行每个 n 但是由于某种原因，当我检查数据库和表时，新行无处可去被发现.

I batch them together in one string and then I execute every n together in the one cusrsor.execute() statement however for some reason when I check the DB and table, the new rows are nowhere to be found.

我什至只用两个插入语句一起尝试过，即使我调用执行，调用提交，然后按该顺序关闭游标和连接，它们仍然没有持久化到数据库中.

I tried it with even just two insert statements together and they still didn't get persisted to the DB even though I call execute, call a commit and then close the cursor and connection in that order.

如果我在执行命令中只有一个 insert 语句并且我删除了 multi 标志，那么它工作正常.

If I have just one insert statement in the execute command and I remove the multi flag then it works fine.

有人知道为什么会发生这种情况吗?

Does anyone know why this could be happening?

示例代码:

from mysql import connector
my_sql_connection = connector.connect(user='user', password='pass',
                                              host='hostname',
                                              database='db')
mysql_cursor = my_sql_connection.cursor()

statement = "Insert into table (col) values (1); Insert into table (col) values (2);"

mysql_cursor.execute(statement, multi=True)
my_sql_connection.commit()
mysql_cursor.close()
my_sql_connection.close()

调用此代码后，没有错误，但表中没有显示新行.

After this code is called, there are no errors however no new rows show up in the table.

推荐答案

你应该尝试使用 executemany 而不是带有 multi 标志的 execute，它通常更容易实现.

You should try using executemany instead of execute with the multi flag, it's generally much simpler to implement.

另外，我建议你切换到 PyMySQL，它有一个非常完整的文档和我的经验开箱即用"的效果比 mysql 连接器库要好得多.

Also,I would recommend you switch to PyMySQL, it has a very complete documentation and from my experience works much better 'out of the box' than the mysql connector library.

这篇关于Python mysql-connector 多插入不工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！