使用Python mysql.connector成功插入后，MySQL数据丢失

I'm using python mysql.connector for some database operation. My database table structure is like this:

我正在使用python mysql.connector进行一些数据库操作。我的数据库表结构如下:

I running a python script with Faker Package(fake-factory 0.5.0) to populate this database table. After insertion I run a query to verify that data is properly stored into the table. The python script shows all inserted data and finishes with exit code 0

我使用Faker Package(假工厂0.5.0)运行python脚本来填充此数据库表。插入后,我运行一个查询来验证数据是否正确存储到表中。 python脚本显示所有插入的数据,并以退出代码0结束

But when I explore that table through phpMyadmin it doesn't show those inserted rows. These inserted data doesn't persist in after the next run yet.

但是,当我通过phpMyadmin探索该表时,它不会显示那些插入的行。这些插入的数据在下次运行后仍未存在。

Here is my code:

这是我的代码:

import mysql.connector
from faker import Faker

fake = Faker()

cnx = mysql.connector.connect(user='root', password='001',
                              host='127.0.0.1',
                              database='smf')
cursor = cnx.cursor()

for i in range(1, 5):
    query = "insert into user " + "(userid, name) values("+ str(i) + ", '" + fake.name() + "')"
    cursor.execute(query)

query = "select * from user"
cursor.execute(query)

for (x) in cursor:
    print ("name = " + format(x))

cnx.close()

2 个解决方案

#1

By default Connector/Python turns autocommit off, and MySQL 5.5 and later uses transactional InnoDB tables, so it is necessary to commit your changes using the connection's commit() method. You could also roll back using the rollback() method.

默认情况下,Connector / Python关闭自动提交,MySQL 5.5及更高版本使用事务性InnoDB表,因此必须使用连接的commit()方法提交更改。您也可以使用rollback()方法回滚。

So, putting cnx.commit() command after cursor.execute(query) solves your problem.

因此,在cursor.execute(查询)之后放置cnx.commit()命令可以解决您的问题。

#2

You should have a cnx.commit after calling the first cursor.execute(query). The commit() function allows the data to be saved permanently.

调用第一个cursor.execute(查询)后,你应该有一个cnx.commit。 commit()函数允许永久保存数据。