Why Doesn\'t My Python Code Insert CSV Data into MySQL?

Loading CSV Data into MySQL in Python

When attempting to load CSV data into a MySQL table using Python, users may encounter a scenario where the code executes without errors but fails to insert data into the table. To address this issue, it is crucial to understand the role of mydb.commit() in this process.

To begin, the mydb.connect() function establishes a connection to the database. Once a cursor is created, the CSV data is read and inserted into the table using the INSERT INTO statement. However, it is essential to perform a mydb.commit() after the insertion to save the changes to the database. Without this step, the data remains in an uncommitted state and will not be visible in the table.

Here is the modified code with mydb.commit() added:

import csv
import MySQLdb

mydb = MySQLdb.connect(host='localhost',
    user='root',
    passwd='',
    db='mydb')
cursor = mydb.cursor()

csv_data = csv.reader(file('students.csv'))
for row in csv_data:

    cursor.execute('INSERT INTO testcsv(names, \
          classes, mark )' \
          'VALUES("%s", "%s", "%s")', 
          row)
mydb.commit() # Save changes to database
#close the connection to the database.
cursor.close()
print "Done"

By including mydb.commit(), you ensure that the data is successfully inserted into the MySQL table.

The above is the detailed content of Why Doesn\'t My Python Code Insert CSV Data into MySQL?. For more information, please follow other related articles on the PHP Chinese website!