0

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!

4
  • Try putting a comma at the end of your list of lists. More, try with a tuple instead of a list of lists :) Commented Nov 18, 2016 at 19:20
  • The way I'd understood it, by leaving the ? I could pass the arguments later in the .execute() call, whereas when I tried %s (unsuccessfully) I was also trying to pass the arguments as you said. I just tried to add .format as you suggested and adjusted the rest, but I got a ProgrammingError and was told to read up about my syntax near (?, ?, ?, ?). Commented Nov 18, 2016 at 19:24
  • @Dex'ter Thanks for your reply. I assume this is what you meant? sql = "INSERT INTO fruit(ID,NAME,COLOUR,SIZE) VALUES(?, ?, ?, ?)" vals = (row[0], row[1], row[2], row[3]) cursor.execute(sql, vals) Commented Nov 18, 2016 at 19:29
  • @Dex'ter Sorry, half comment was missing... That didn't work :/ Commented Nov 18, 2016 at 19:35

2 Answers 2

1

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]])
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you, I have found this syntax in other questions but I get this result: 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 205, in execute self.errorhandler(self, exc, value) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1366, "Incorrect integer value: 'ID' for column 'ID' at row 1")
Now that I see that, I think it's because the first line is actually the headers so it should be parsed differently. I'll see if it works if I skip that.
If first row are headers, add next(f) just after with(...) statement to skip it. Also, try casting to int().
Thank you so much! Could you clarify exactly where I'd put the next(f)? I can't quite make that work, sorry!
Try next line after with().
0

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

1 Comment

No joy I'm afraid. I also tried leaving the row[0] as is since it's an int in the database. I think my syntax is wrong but I don't know how.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.