In python script i have insert query but when i want insert multiple columns in the same query it gives error

Question

In python script i have insert query but when i want insert multiple columns in the same query it gives error. but for single query it works perfectly. Below is my code. my database AWS S3.

A = [] #
for score_row in score:
    A.append(score_row[2])
    print("A=",A)

B = [] #
for day_row in score:
    B.append(day_row[1])
    print("B=",B)
for x,y in zip(A,B):
    sql = """INSERT INTO calculated_corr_coeff(date,Day) VALUES (?,?)"""
    cursor.executemany(sql, (x,),(y,))

when i replace above query with following sql insert statement it works perfect.

sql = """INSERT INTO calculated_corr_coeff(date,Day) VALUES (?)"""
cursor.executemany(sql, (x,))

dani herrera · Accepted Answer · 2019-01-09T08:56:13.777

Fix your code like this:

sql = """INSERT INTO calculated_corr_coeff(date,Day) VALUES (?,?)"""
cursor.execute(sql, (x,y,))  #<-- here

Because is just a onet insert ( not several inserts )

Explanation

I guess you are mistaked about number of inserts ( rows ) and number of paràmeters ( fields to insert on each row ). When you want to insert several rows, use executemany, just for one row you should to use execute. Second parapeter of execute is the "list" (or sequence ) of values to be inserted in this row.

Alternative

You can try to change syntax and insert all data in one shot using ** syntax:

values = zip(A,B)   #instead of "for"
sql = """INSERT INTO calculated_corr_coeff(date,Day) VALUES (?,?)"""
cursor.executemany(sql, **values )

Notice this approach don't use for statement. This mean all data is send to database in one call, this is more efficient.

In python script i have insert query but when i want insert multiple columns in the same query it gives error

1 Answers1