SQLite and Python, TypeError: not all arguments converted during string formatting

Question

I'm working some code that inserts csv rows into an SQLite database using Python. I've been using it for awhile, and it's been working. However, there was an update to the CSV file but only rows were added, not columns. Now when I run the code, I receive "TypeError: not all arguments converted during string formatting". I've tried everything I could think of and find, and I still get this error. This is the code that executes the insertion portion of the database

fin = open("file.csv", "rb")
creader = csv.reader(fin, delimiter=',')

sql_insert = "INSERT INTO WR_Training VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"  

# Iterate through creader and insert the values into the table
# Pop off the 3 resources columns and insert the others 

    for row in creader:
        row.pop(27)
        row.pop(26)
        row.pop(25)
        if row[2] != "":
            cu.execute(sql_insert, row)

fin.close()
cx.commit()
cx.close()

Originally, there are 29 columns all together, which is why I use row.pop to pop off the columns that I don't want, therefore I also don't include them in the original creation of the table as well. So it's storing 26 columns out of 29. I used to use %s instead of ? for the sql command, however %s didn't work and now ? doesn't work as well.

Any suggestions would be very helpful!

Thank you

Have you verified that row has the number of columns you think it should by looking at len(row)? Are you certain this matches the number of ? markers in your SQL statement? — larsks
– larsks, Commented Mar 31, 2012 at 1:32
Try print(len(row)) right before you execute to make sure it's as exactly long as you think it is. Python is telling you it's longer than 26 items. — kindall
– kindall, Commented Mar 31, 2012 at 1:32
I never thought of doing that, thats a good way to test it. I tried it, and it says 26 unfortunately — user1186173
– user1186173, Commented Mar 31, 2012 at 2:02
Then it shouldn't complain. I don't know what type row is, but maybe you could slice it instead. row = row[0:26] That should always make it the right length. If there's an error in the data, that might still break. M$ CSV is Broken As Designed. — XTL
– XTL, Commented Apr 3, 2012 at 6:07
That did solve the issue, however, I have it popping off 25, 26, and 27 because I don't want those specific columns to be included in each row when it's added to the database. When I do row = row[0:26], it includes all columns up to 26, I still want the last column in the CSV file. Thank you for your suggestion though, it definitely shed some light on the issue — user1186173
– user1186173, Commented Apr 4, 2012 at 20:41

user1186173 · Accepted Answer · 2012-04-04 20:55:05Z

1

for row in creader:
    row = row[0:29]

    row.pop(27)
    row.pop(26)
    row.pop(25)
    if row[2] != "":
        cu.execute(sql_insert, row)

This works, thanks to XTL's comment

answered Apr 4, 2012 at 20:55

user1186173

5654 gold badges7 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite and Python, TypeError: not all arguments converted during string formatting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related