Getting "sqlite3.ProgrammingError: Incorrect number of bindings supplied" when splitting l-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 case, the line apparently contained no commas. This may happen, for example, if there are empty lines in the file.

You could test the length of the resulting list before passing it to cursor.execute and skip it if the length is not 4. In order to do this, you have to assign it to a variable first:

for i in file:
    values = i.split(",")
    if len(values) != 4:
        continue
    cursor.execute("INSERT INTO DATABASE VALUES (?,?,?,?)", values)