Helen Neely Helen Neely - 2 months ago 7
MySQL Question

Load CSV data into MySQL in Python

Not sure what I'm missing here but this code runs without any error message, but there's nothing in the table. I'm loading a CSV values in three columns into mysql table

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


Would appreciate if someone else could have a look.

Thank you.

Answer

I think you have to do mydb.commit() all the insert into.

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