python script for inserting batch data into mysql table

Question

I'm writing a python script for inserting a csv file into a mysql table. The problem I am having is that it is only inserting one line and terminating after that.

#!/usr/bin/python

import sys
import csv 
import MySQLdb

db = MySQLdb.connect(host="localhost",user="root",passwd="password",db="AM")
cur = db.cursor()


f = open(sys.argv[1],'rt')
try:
    reader = csv.reader(f)
    headers = next(reader)
    for row in reader:
        col1 = row[0]
        col2 = row[1]
        col3 = row[2]
        col4 = row[3]
        col5 = row[4]
        col6 = row[5]
except:
     print sys.exc_info()


sql = "Insert ignore into my_table(col1, col2, col3, col4, col5, col6) values ('%s', '%s', '%s', '%s', '%s', '%s');" % (col1, col2, col3, col4, col5, col6)
try:
    cur.execute(sql)
    db.commit()
except:
    db.rollback()
finally:
    db.close() 

Answer 1

sql = "Insert ignore into table(a,b,c,d,e,f) values ('%s', '%s', '%c', '%s', '%s', '%s');" % (list1, list2, list3, list4, list5, list6)

Is your table actually named "table"? If so, you should surround it in double quotes like so:

sql = "Insert ignore into \"table\"(a,b,c,d,e,f) values ('%s', '%s', '%c', '%s', '%s', '%s');" % (list1, list2, list3, list4, list5, list6)

table is a SQL keyword.