使用变量执行SQLite的REPLACE INTO时触发sqlite3.OperationalError

问题分析 #

1. f-string拼接SQL的错误：
   原代码用f-string直接将路径拼接到SQL语句中，生成的SQL会是类似REPLACE INTO dirs_to_process (__dirpath) VALUES /home/user的格式，路径中的/会被SQL解析器判定为语法错误，同时这种写法存在SQL注入风险。

2. 参数化查询的写法错误：
   你尝试的(dirpathstring)不是合法的元组，Python会将其解析为单个变量而非元组，导致sqlite3无法识别参数，从而报错。

正确写法 #

方式一：循环单条插入（修正参数化写法） #

将参数改为带逗号的元组，确保sqlite3能正确识别参数：

dbcursor.execute('SELECT DISTINCT __dirpath FROM alib where sqlmodded > 0')
dirpaths = dbcursor.fetchall()
dirpaths.sort()
dbcursor.execute('create table IF NOT EXISTS dirs_to_process (__dirpath blob PRIMARY KEY);')
for dirpath in dirpaths:
    dirpathstring = dirpath[0]
    # 注意元组末尾的逗号，这是单个元素元组的正确写法
    dbcursor.execute("REPLACE INTO dirs_to_process (__dirpath) VALUES (?)", (dirpathstring,))

方式二：批量插入（更高效） #

使用executemany一次性插入所有路径，减少数据库交互次数：

dbcursor.execute('SELECT DISTINCT __dirpath FROM alib where sqlmodded > 0')
dirpaths = dbcursor.fetchall()
dirpaths.sort()
dbcursor.execute('create table IF NOT EXISTS dirs_to_process (__dirpath blob PRIMARY KEY);')
# 转换为适合executemany的元组列表
dirpath_params = [(dp[0],) for dp in dirpaths]
dbcursor.executemany("REPLACE INTO dirs_to_process (__dirpath) VALUES (?)", dirpath_params)

关键说明 #

• 参数化查询是操作SQLite3的最佳实践，既能避免语法错误，又能防止SQL注入。
• 单个元素的元组必须添加末尾的逗号，否则Python会将其视为普通变量而非序列类型。

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