2

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
ulissebu123
  • 19
  • 3
  • @PatrickArtner thanks for your answer. Do you know how i could reduce my list "dfGB", because this list has 120 values and there could be the solution reducing this list. – ulissebu123 May 22 '22 at 09:12

1 Answers1

0

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?

Patrick Artner
  • 50,409
  • 9
  • 43
  • 69
  • @uli misplaced ) .. fixed – Patrick Artner May 22 '22 at 09:17
  • Now i have filled in the code from you, thank you very much! Do you know from where there are 12 values? because i have in every case just 10 values. ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 12 supplied. – ulissebu123 May 22 '22 at 09:25
  • @Uli no I do not know, but what I see are plenty of "float" values in your file and only "int" values in your table ... you seem do do somthing weird. You may want to provide a [mre] with hardcoded data that replicates your problem. – Patrick Artner May 22 '22 at 09:39