Python2.7+SQLite3使用DATE(SUBSTR())的DELETE、SELECT语句不生效问题

问题根因排查方向 #

• 数据库路径不匹配：这是最高概率的原因，Python脚本连接的SQLite数据库文件和DB Browser中打开的不是同一个。如果你的DB变量用的是相对路径，可能是脚本运行时的工作目录发生了变化，导致实际连接到了其他位置的空白/旧版本库文件，自然不会匹配到目标数据，也不会抛出错误。你可以在代码中加入以下语句打印实际连接的库绝对路径，和DB Browser打开的文件路径做比对：

  import os
  print(os.path.abspath(DB))
  

• 时区不一致问题：SQLite的date('now')默认返回UTC时区的日期，而DB Browser执行SQL时默认会使用系统本地时区。如果你的本地时区和UTC存在时差，就会出现DB Browser用本地时间能匹配到数据，Python用UTC时间匹配不到的情况。比如当前你本地是10月1日，但UTC时间还是9月30日，就会导致日期比对不命中。
• 日期格式异常：少量checkout字段的内容不符合dd/mm/YYYY格式，比如分隔符异常、位数不足，会导致substr拼接后的字符串无法被DATE函数解析，返回NULL，而NULL参与大小比较的结果永远为假，所以不会被匹配到。

修复方案 #

1. 修正日期获取逻辑，强制使用本地时区，避免时区差问题：
   把你SQL语句中的date('now')替换为date('now', 'localtime')，修改后的DELETE语句示例：

   cur.execute("DELETE FROM CHECK_TABLE WHERE DATE(substr(checkout,7,4)||substr(checkout,4,2)||substr(checkout,1,2))<=DATE(strftime('%Y%m%d',date('now', 'localtime')))")
   

2. 优化SELECT语句的参数拼接逻辑，避免SQL注入风险，改用参数化查询写法：

   cur.execute("SELECT DISTINCT car FROM CHECK_TABLE WHERE car=? AND DATE(substr(checkout,7,4)||substr(checkout,4,2)||substr(checkout,1,2))<=DATE(strftime('%Y%m%d',date('now', 'localtime')))", (car,))
   

3. 增加调试输出确认执行结果，执行删除/查询后打印影响行数，验证是否匹配到了目标数据：

   # DELETE语句执行后打印影响行数
   cur.execute(...)
   print(f"删除行数：{cur.rowcount}")
   

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