inserting multiple values - sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type

user_11774730 0
Viewed 320

Error:

cursor.execute("INSERT INTO details (user_id, first_name, surname, role, make, model, colour, reg) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", details_default_values) sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Code that causes this error to occur:

connection = sqlite3.connect('collyers_car_park.db')

cursor = connection.cursor()

# create details table
details_table = """CREATE TABLE IF NOT EXISTS
details(
user_id INTEGER PRIMARY KEY,
first_name TEXT,
surname TEXT,
role TEXT,
make TEXT,
model TEXT,
colour TEXT,
reg TEXT)"""

details_default_values = [
    ('1','Bob','Smith','Staff','Lamborgini','Aventador', 'Red', 'RE05 KDJ'),
    ('2','Sarah','McDonald','Staff','Ferrari','LaFerrari', 'Yellow', 'TY07 PER'),
    ('3','Will','Stevenson','Student','Bugatti','Veyron', 'Green', 'RE62 LKD'),
    ('4','Steve','Swimswam','Student','Renault','Clio', 'Pink', 'RE66 KPO'),
    ('5','Harry','Reeto','Visitor','VW','Up!', 'Blue', 'RZ05 FSD'),
    ('5','Harry','Reeto','Visitor','VW','Up!', 'Blue', 'RZ05 FSD'),
    ('5','Harry','Reeto','Visitor','VW','Up!', 'Blue', 'RZ05 FSD'),
    ('5','Harry','Reeto','Visitor','VW','Up!', 'Blue', 'RZ05 FSD'),
]

cursor.execute("INSERT INTO details (user_id, first_name, surname, role, make, model, colour, reg) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", details_default_values)

I thought it was an issue with the user_id column being in a string format, but I still get the same error when I get rid of the inverted commas around them.

sql-insert list sqlite python database
1 Answers

You are trying to insert more than 1 rows, so instead of cursor.execute() use cursor.executemany():

cursor.executemany("INSERT INTO ....", details_default_values)
connection.commit()

But, there is another problem, because you have duplicate user_ids to insert and this will result in a UNIQUE constraint failed error.
Make sure that all user_ids are unique.

user_104988280
Related