python - 插入多个值 - sqlite3.InterfaceError : Error binding parameter 0 - probably unsupported type

Question

错误:

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

导致此错误发生的代码:

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)

我认为这是 user_id 的问题列采用字符串格式，但是当我去掉它们周围的引号时，我仍然遇到相同的错误。

Answer 1

您正在尝试插入 1 行以上，而不是 cursor.execute()使用 cursor.executemany() :

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

但是，还有另一个问题，因为您有重复的 user_id s 插入，这将导致 UNIQUE constraint failed错误。
确保所有 user_id s 是独一无二的。