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

Question

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.

this post may help you

kiranr
– kiranr

2021-02-03 14:10:51 +00:00
Commented Feb 3, 2021 at 14:10 — kiranr
– kiranr, Commented Feb 3, 2021 at 14:10

forpas · Accepted Answer · 2021-02-03 14:17:40Z

2

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.

answered Feb 3, 2021 at 14:17

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Benjamin McDowell Over a year ago

Thanks, that works perfectly. The duplicate values were only there to bypass another error which appears to have disappeared now.

Benjamin McDowell Over a year ago

What does .commit() do?

forpas Over a year ago

commit() saves all the changes you made to the database and makes them visible to other connections to the database. You can read more here: docs.python.org/3/library/sqlite3.html

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related