sqlite3.programmingerror incorrect number of bindings supplied

Question

Python 2.7 and sqlite3

I create a table like this:

#create table
connector.execute('''CREATE TABLE mytable (
column1,
column2,
column3
''')

Then I iterate through a .csv file like this:

with open("mycsv.csv") as f:
for i in f:
    del splitted[:]
    del myData[:]
    splitted = i.split(",")
    for j in splitted:
        myData.append(tuple([j]))

which gives me this output:

print myData
> [('testtext1',), ('testtext2',), ('testtext3\n',)]

However, when I try to insert this into my db like so:

connector.executemany('''INSERT INTO mytable (
    column1,
    column2,
    column3
    ) VALUES (
    ?,?,?)''', myData)

I get the error

sqlite3.programmingerror incorrect number of bindings supplied. The current state uses 3, and tehre are 1 supplied.

Joe Young · Accepted Answer · 2015-09-10 08:46:53Z

What you have is a list of 3 tuples, all containing 1 column each.

You want all 3 columns to make up the contents of 1 tuple. Then you want to add that tuple to a list. Try something like this:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

cur.execute('''CREATE TABLE mytable
             (column1 text, column2 text,column3 text)''')

myData = []
with open("mycsv.csv") as f:
    for line in f.readlines():
        line = line.rstrip('\n')
        (col1, col2, col3) = line.split(',')
        mytuple = (col1, col2, col3)
        myData.append(mytuple)

cur.executemany('INSERT INTO mytable (column1, column2, column3) VALUES (?,?,?)', myData)

for row in cur.execute('SELECT * FROM mytable'):
    print row

Output:

(csv2sqlite)macbook:csv2sqlite joeyoung$ python csv2sqlite.py 
(u'testtext1', u'testtext2', u'testtext3')
(u'testtext1', u'testtext2', u'testtext3')
(u'testtext1', u'testtext2', u'testtext3')

Input:

(csv2sqlite)macbook:csv2sqlite joeyoung$ cat mycsv.csv 
testtext1,testtext2,testtext3
testtext1,testtext2,testtext3
testtext1,testtext2,testtext3

Collectives™ on Stack Overflow

sqlite3.programmingerror incorrect number of bindings supplied

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