Load CSV data into MySQL in Python


Question

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.

1
36
4/21/2013 6:58:24 AM

Accepted 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"
66
4/14/2012 3:11:44 PM

The above answer seems good. But another way of doing this is adding the auto commit option along with the db connect. This automatically commits every other operations performed in the db, avoiding the use of mentioning sql.commit() every time.

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

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon