Nicholas Nicholas - 20 days ago 7
MySQL Question

CSV to SQL through Python: passing multiple arguments

I am a python beginner, and I'm trying to parse a CSV file and add its contents to a database.

My current code:

with open('fruit.csv', 'rb') as f:
reader = csv.reader(f, delimiter='\t', quoting=csv.QUOTE_NONE)
for row in reader:
sql = "INSERT INTO fruit(ID,NAME,COLOUR,SIZE) VALUES(?, ?, ?, ?)"
try:
cursor.execute(sql, [ row[0], row[1], row[2], row[3] ] )
db.commit()
print('done')
except csv.Error as e:
print('error: ' + e)
db.rollback()


My current error:

Traceback (most recent call last):
File "mysqltest.py", line 23, in <module>
cursor.execute(sql, (row[0], row[1], row[2], row[3]))
File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 187, in execute
query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting


I read answers to a lot of other questions here on SE but I still can't understand how to pass several values within a row as separate values to an SQL string.

Not sure if relevant but this is the structure of the table I'm trying to write to:

TABLE FRUIT (
ID int primary key NOT NULL,
NAME CHAR(20) NOT NULL,
COLOUR CHAR(20),
SIZE CHAR(20))


If someone could explain what I'm doing wrong, it'd be really appreciated!

Answer

In most Python API modules, MySQL uses the %s operator and not ?.

sql = "INSERT INTO fruit(ID, NAME, COLOUR, SIZE) VALUES(%s, %s, %s, %s)"
try:
    cursor.execute(sql, [row[0], row[1], row[2], row[3]])