VS Code SQLite扩展批量插入数据仅第一条生效，求解决办法

一、批量插入失败的问题修复 #

你的SQL代码存在三个关键问题，导致第二条数据无法插入：

1. 自增id列手动赋值错误：id是INTEGER PRIMARY KEY AUTOINCREMENT，应让数据库自动生成id，无需手动传入字符串类型的id值，否则会触发类型不匹配异常。
2. 列顺序不匹配：INSERT指定的列顺序是id, filename, title, keywords, author, year, filepath，但你在VALUES中把year（整数2013）放在了author的位置，而author要求是TEXT类型，第一条数据因SQLite弱类型特性侥幸插入，第二条数据的author值是文本，和前一条的类型冲突导致插入失败。
3. 字符串末尾多余换行符：filepath字符串末尾的换行符可能导致SQL语法解析错误。

修正后的SQL代码如下：

CREATE TABLE IF NOT EXISTS papers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  filename TEXT NOT NULL,
  title TEXT NOT NULL,
  keywords TEXT NOT NULL,
  author TEXT NOT NULL,
  year INTEGER NOT NULL,
  filepath TEXT NOT NULL
);

-- 移除id列，由数据库自动生成；调整列顺序匹配VALUES中的数据
INSERT INTO papers (filename, title, keywords, author, year, filepath) 
VALUES 
("full01_Munandar_Geothermal resources development in Indonesia", 
"Geothermal resources development in Indonesia", 
"geothermal; Indonesia; vocanic; non-vocanic", 
"A. Munandar; S. Widodo", 
2013, 
"\\well-srv04\data\Technical Resources\Papers, books and publications - External to Quest\Conferences\AGS 2013\2013Paper\full01_Munandar_Geothermal resources development in Indonesia.pdf"),

("full04_TaeJongLee_YR 2013 country update on geothermal energy in Korea", 
"Yr 2013 Country Update on Geothermal Energy in Korea",
"geothermal heat pump (GHP); enhanced geothermal system (EGS); direct use; power generation; EGS potential; technological roadmap (TRM)", 
"T.J. Lee; Y. Song", 
2013, 
"\\well-srv04\data\Technical Resources\Papers, books and publications - External to Quest\Conferences\AGS 2013\2013Paper\full04_TaeJongLee_YR 2013 country update on geothermal energy in Korea.pdf");

二、SQLite批量插入的正确方式 #

SQLite不需要每次插入都编写完整的INSERT INTO...VALUES语句，常用的批量插入方式有两种：

1. 多值批量插入：就是你尝试的写法，在VALUES后用逗号分隔多个值组，一次插入多条数据，语法格式和上面修正后的代码一致。
2. Python代码中使用executemany()：在Python操作SQLite时，推荐用参数化查询结合executemany()方法，既高效又能避免SQL注入，示例代码如下：

import sqlite3

conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()

# 定义插入语句
insert_sql = """INSERT INTO papers (filename, title, keywords, author, year, filepath)
                VALUES (?, ?, ?, ?, ?, ?)"""

# 准备批量数据
data_list = [
    ("full01_Munandar_Geothermal resources development in Indonesia", 
     "Geothermal resources development in Indonesia", 
     "geothermal; Indonesia; vocanic; non-vocanic", 
     "A. Munandar; S. Widodo", 
     2013, 
     "\\well-srv04\\data\\Technical Resources\\Papers, books and publications - External to Quest\\Conferences\\AGS 2013\\2013Paper\\full01_Munandar_Geothermal resources development in Indonesia.pdf"),
    ("full04_TaeJongLee_YR 2013 country update on geothermal energy in Korea", 
     "Yr 2013 Country Update on Geothermal Energy in Korea",
     "geothermal heat pump (GHP); enhanced geothermal system (EGS); direct use; power generation; EGS potential; technological roadmap (TRM)", 
     "T.J. Lee; Y. Song", 
     2013, 
     "\\well-srv04\\data\\Technical Resources\\Papers, books and publications - External to Quest\\Conferences\\AGS 2013\\2013Paper\\full04_TaeJongLee_YR 2013 country update on geothermal energy in Korea.pdf")
]

# 执行批量插入
cursor.executemany(insert_sql, data_list)
conn.commit()
conn.close()

内容的提问来源于stack exchange，提问作者Alec