Python and SQLite: Not all arguments converted during string formatting error

Question

I have seen a few of these errors on Stackoverflow but non of the answers seem to be working when I implement them.

import csv, sqlite, sys

conn = sqlite.connect('Database_Practice.db')
mouse = conn.cursor()

input = open('DATA.csv','rb')
my_reader = csv.reader(input, delimiter = ',',quotechar='|')

mouse.execute("DROP TABLE IF EXISTS Example_Input;")
mouse.execute("CREATE TABLE Example_Input (ID INTEGER, Name VARCHAR, Job VARCHAR, Salary INTEGER);")

try:
    to_database = [entry for entry in my_reader]
    for block in to_database:
        mouse.execute("INSERT INTO Example_Input (ID,Name,Job,Salary) VALUES (?,?,?,?)", block)
finally:
    conn.commit()
    input.close()

I know the variable is getting the variables, as if I comment out the INSERT line I get the output:

['1', 'Billy', 'Tech', '888']
['2', 'Jane', 'Sales', '777']
['3', 'Scott', 'Tech', '667']
['4', 'Harris', 'Tech', '444']
['5', 'Gray Skull', 'Sales', '333']
['6', 'Barbarosa', 'Assistant', '200']
['7', 'Nick', 'Janitor', '100']

but after a long time of trying to tweak it, I can't seem to get it to stop giving me the error:

 File "Another_Try.py", line 18, in ?
    mouse.execute("INSERT INTO Example_Input (ID,Name,Job,Salary) VALUES (?,?,?,?)", block[0:4])
  File "/usr/lib64/python2.4/site-packages/sqlite/main.py", line 255, in execute
    self.rs = self.con.db.execute(SQL % parms)
TypeError: not all arguments converted during string formatting

Any advice?

EDIT:

try:
    to_database = [entry for entry in my_reader]
    for block in to_database:
#       mouse.execute("INSERT INTO Example_Input (ID,Name,Job,Salary) VALUES (?,?,?,?)", block)
        print block

Can you show the code you used to generate the results that you say you get "if I comment out the INSERT line"? That output is not helpful unless we know exactly how it was created. I don't see anywhere in your code where you are actually creating that output. — Mark Hildreth
– Mark Hildreth, Commented Jan 2, 2014 at 17:41

SeanTater · Accepted Answer · 2014-01-02 17:59:11Z

1

I ran your code, replaced sqlite with sqlite3, and appended this:

print mouse.execute("select * from Example_Input").fetchall()

It appears to work with reasonable CSV. Maybe you should check if the CSV is actually what you are expecting. If the quotes aren't right, you could end up getting more than four columns, and that would explain the error.

Incidently, it is handier to replace the loop with this:

try:
    mouse.executemany("INSERT INTO Example_Input (ID,Name,Job,Salary) VALUES (?,?,?,?)", my_reader)
finally:
    conn.commit()
    i.close()

answered Jan 2, 2014 at 17:59

SeanTater

1821 silver badge5 bronze badges

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

3 Comments

TheGoblinPopper Over a year ago

Thanks, I did try to import sqlite3 and I got an error. I am assuming there might be an issue with with versions I am running then... which might explain why non of the other solutions I have looked up are working.

SeanTater Over a year ago

Indeed. I can only find sqlite3, not sqlite, in the documentation.

TheGoblinPopper Over a year ago

After updating python to 2.7.2 from 2.4, it worked like a charm... of all the errors to get...lol. Well, at least I know now.

Collectives™ on Stack Overflow

Python and SQLite: Not all arguments converted during string formatting error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related