MySQL data being lost after getting inserted successfully with Python mysql.connector

Question

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

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

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.

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()

Answer 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.

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