sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and t-CodePudding

I'm trying to import data from a csv file to a existing database, the database has 4 columns called product_id,Firstname,Lastname,Address and this is the code for the csv import;

import sqlite3, csv

connection = sqlite3.connect("martin_DB3")
cursor = connection.cursor()

with open("lista.csv","r") as file:
    records = 0
    for i in file:
        cursor.execute("INSERT INTO DATABASE VALUES (?,?,?,?)", i.split(","))
        connection.commit()
        records  = 1
connection.close()
print ("Data has been transfered")

But I keep getting the error

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied.

What am I doing wrong?

CodePudding user response：

It seems you had the intention of using csv but then you didn't.

You can do something like this and it will work (even if you have missing values in the CSV):

with open("lista.csv", "r") as file:
    reader = csv.DictReader(file)
    for n, r in enumerate(reader):
        t = tuple(r.values())
        cursor.execute("INSERT INTO DATABASE VALUES (?,?,?,?)", t)
        connection.commit()
        print(n)

BTW: DATABASE is not a great name for a TABLE

CodePudding user response：

If one line in the file does not contain exactly 3 commas, the expression i.split(",") results in a list with a different length than 4.

In your example, the line apparently contained no commas.

You could test the length of the resulting list before passing it to cursor.execute and skip it if the length is not 4.