CODEWITHSUNDEEP
pythonmysqlsqlcsvtypeerror
Nicholas
Nicholas

Reputation: 89

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!

Upvotes: 0

Views: 318

Answers (2)

Parfait
Parfait

Reputation: 107767

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]])

Upvotes: 1

Noah.Kim
Noah.Kim

Reputation: 119

I think type error. So, you could try below code.

use the str() function

cursor.execute(sql, [ str(row[0]), str(row[1]), str(row[2]), str(row[3]) ] )

Upvotes: 0

Related Questions