SQL connection problems. ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 120 supplied

user_19172774 0
Viewed 38

I have seen there are a lot of posts like this. I have also considered the feedback on the posts but there is a new error regarding incorrect number of bindings.

I created a table on SQL

conn = sqlite3.connect('AQM_2022.db')
c = conn.cursor()
c.execute('''CREATE TABLE Reg2
         (CPI,
         UNR           INT    NOT NULL,
         M1            INT     NOT NULL,
         M2            INT     NOT NULL,
         IMP            INT     NOT NULL,
         EXP            INT     NOT NULL,
         RetailSales            INT     NOT NULL,
         GBBalance            INT     NOT NULL,
         PPI            INT     NOT NULL,
         const INT)''')
print("Table created successfully")*

And i want to export following numbers to my SQL database:

index1=dfGB.index.strftime('%Y-%m-%d %H-%M-%S')
dfGB['Date1']=index1
dfGB.head(5)

I converted it into lists

records_to_insert = dfGB.values.tolist()
records_to_insert

But when i want to export it to SQL:

c = conn.cursor()
c.executemany("INSERT INTO Reg2(CPI,UNR,M1,M2,IMP,EXP,RetailSales,GBBalance,PPI,const) VALUES (?,?,?,?,?,?,?,?,?,?)", [records_to_insert])    
conn.commit()
con.close()

The following error pops up: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 120 supplied.

Does somebody know what the problem could be?

Best regards

Data dfGB

data to list

sqlite sql python
1 Answers

You need to provide a list of rows to sqlite3.Cursor.executemany:

You are providing a flat list of 120 values.

Something along the lines of

recs = dfGB.values.tolist()
recs = [recs [v:v+10] for v in range(0,len(recs), 10]

should provide you with a correctly chunked list of list of 10 items each.

If your list goes into million of elements you may want to chunk iteratively instead of creating a new list: How do you split a list into evenly sized chunks?

user_75053950
Related