Why Isn\'t My Python Code Inserting CSV Data into My MySQL Database?

Loading CSV Data into MySQL in Python

This code snippet attempts to load CSV data into a MySQL database table. However, the user is encountering an issue where nothing is being inserted into the table.

The code uses the csv and MySQLdb modules to read a CSV file and insert its data into a MySQL table called testcsv. The CSV file is expected to have three columns: names, classes, and mark.

Upon executing the code, the user reports no error messages, but the table remains empty. To resolve this issue, the code is missing a crucial step: committing the changes to the database.

In MySQL, changes made to a database, such as inserting new records, are not permanent until they are committed. By default, MySQL operates in autocommit mode, which means that each statement is committed automatically. However, in certain situations, such as when handling transactions, it is necessary to explicitly commit changes.

In the provided code, the following line is missing after inserting each row of data:

mydb.commit()

Adding this line will ensure that the changes made by each INSERT statement are committed to the database. Consequently, the data will be successfully inserted into the testcsv table. The updated code should look like this:

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()
#close the connection to the database.
cursor.close()
print "Done"

By committing the changes after each insert operation, the user can ensure that the data is permanently stored in the MySQL table.

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