Python and SQLite3 ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 1 supplied

Question

I am trying to load a csv file into my database table and keep getting this ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 1 supplied. I think it may have something to do with my c.execute but am unsure what the fix would be. I attached my code below for trying to insert the CSV into my single table.

My code is

import sqlite3
import csv

# creating my first database that will be used for the assignment.
conn = sqlite3.connect('Assignment3.db')
c = conn.cursor()

#Creating table- CCSubset with all of the required fields
c.execute("drop table if exists CCSubset")
c.execute("""CREATE TABLE CCSubset (
                CCSubset_id integer not null primary key,
                CCSubset_limit int,
                CCSubset_sex varchar(10),
                CCSubset_edu varchar(10),
                CCSubset_marr varchar(10),
                CCSubset_age int,
                CCSubset_pay int,
                CCSubset_bill int,
                CCSubset_payamt int,
                CCSubset_default int)""")
conn.commit()
f=open('CCSubset.csv')
insert sql = "insert into CCSubset values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    for row in str(csv.reader(f)):
    c.execute(insert_sql, row)

The error I receive is: ProgrammingERROR: Incorrect number of binding supplied. The current statement uses 10, and there are 1 supplied.

Here is the updated code:

# creating my first database that will be used for the assignment.
conn = sqlite3.connect('Assignment3.db')
c = conn.cursor()

#Creating table- CCSubset with all of the required fields
c.execute("drop table if exists CCSubset")
c.execute("""CREATE TABLE CCSubset (
                CCSubset_id integer not null primary key,
                CCSubset_limit int,
                CCSubset_sex varchar(10),
                CCSubset_edu varchar(10),
                CCSubset_marr varchar(10),
                CCSubset_age integer,
                CCSubset_pay int,
                CCSubset_bill int,
                CCSubset_payamt int,
                CCSubset_default int)""")
conn.commit()
f=open('CCSubset.csv') 
for row in str(reader):
    insert_sql = "insert into CCSubset values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    print(row)


conn.commit()

The output I receive now is:

<
c
s
v
.
D
i
c
t
R
e
a
d
e
r

o
b
j
e
c
t

a
t

0
x
7
f
9
b
c
8
0
7
2
4
e
0
>

So I am at a complete stand still and don't know what I need to do. Any and all help would be appreciated.

does unpacking the tuple c.execute(insert_sql, *row) do anything for you? — Alan Hoover
@AlanHoover Still get Incorrect number of bindings if I plug it into the first code and if I plug into the 2nd set of code I tried I still receive the key error. — gravity00
It looks like this when I do a print(row) and delete the execute and str from str(reader) but a lot more lines: OrderedDict([('ID', '1'), ('LIMIT_BAL', '20000'), ('SEX', '2'), ('EDUCATION', '2'), ('MARRIAGE', '1'), ('AGE', '24'), ('PAY', '2'), ('BILL_AMT', '3913'), ('PAY_AMT', '0'), ('DefaultNext', '1')]) OrderedDict([('ID', '2'), ('LIMIT_BAL', '120000'), ('SEX', '2'), ('EDUCATION', '2'), ('MARRIAGE', '2'), ('AGE', '26'), ('PAY', '-1'), ('BILL_AMT', '2682'), ('PAY_AMT', '0'), ('DefaultNext', '1')]) — gravity00
Try that with the one that gives a list instead of a dict (the one you did not open with a DictReader()) — Alan Hoover

Alan Hoover Alan Hoover · Accepted Answer · 2020-05-22T04:24:10

Now I see your problem. You are converting the list that csv.reader() gives you to a string. Another time you were using the DictReader from csv. You just need the result from csv.reader() like this:

with open(CCSubset.csv) as csvfile:
    filereader = csv.reader(csvfile)
    for row in filereader:
        c.execute(insert_sql, row)

Python and SQLite3 ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 1 supplied

1 Answers