使用Python操作SQLite3执行UPDATE语句时触发OperationalError报错

问题描述 #

尝试在SQLite3数据库中更新数据，执行以下代码：

import sqlite3
import pandas as pd
import numpy as np  # 原代码遗漏该导入，补充以适配np.nan

con = sqlite3.connect(r'test.db')
sql = con.cursor()

general = pd.DataFrame(columns=['isin', 'issued_volume', 'amt_out', 'res_nominal_value', 'next_offer_date', 'offer_price', 'option_type'])
general.loc[len(general)] = ['test_isin', 200000, 200000, 500, np.nan, 100, 'put']
general.to_sql(name='general', con=con, index=False)

flds = ('issued_volume', 'amt_out', 'res_nominal_value', 'next_offer_date', 'offer_price', 'option_type')
update_values = (200000, 200000, 1000, '10.02.2022', 100, 'call', 'test_isin')
sql.execute(f"""UPDATE general SET {flds} = (?, ?, ?, ?, ?, ?)  WHERE isin = (?);""", update_values)
con.commit()

出现如下错误：

OperationalError                          Traceback (most recent call last)
<ipython-input-5-ca5d6f2301b7> in <module>()
      1 flds = ('issued_volume', 'amt_out', 'res_nominal_value', 'next_offer_date', 'offer_price', 'option_type')
      2 update_values = (200000, 200000, 1000, '10.02.2022', 100, 'call', 'test_isin')
----> 3 sql.execute(f"""UPDATE general SET {flds} = (?, ?, ?, ?, ?, ?)  WHERE isin = (?);""", update_values)
      4 con.commit()

OperationalError: near "(": syntax error

补充：代码在另一台机器正常运行，推测差异源于库版本，已尝试复制sqlite3文件夹到Anaconda3的Lib目录，当前在Jupyter Notebook运行。

错误原因 #

你使用的SET (列1,列2,...) = (值1,值2,...)批量更新语法，是SQLite 3.35.0及以上版本才支持的新特性（2021年3月发布）。当前运行环境的SQLite版本低于该版本，无法识别这种语法，因此抛出语法错误。

另一台机器能正常运行，是因为其SQLite版本满足要求。

解决方案 #

方案1：修改SQL语句兼容低版本（推荐） #

将批量赋值语法拆分为单个列的列名=?形式，所有SQLite版本都支持这种写法：

import sqlite3
import pandas as pd
import numpy as np

con = sqlite3.connect(r'test.db')
sql = con.cursor()

general = pd.DataFrame(columns=['isin', 'issued_volume', 'amt_out', 'res_nominal_value', 'next_offer_date', 'offer_price', 'option_type'])
general.loc[len(general)] = ['test_isin', 200000, 200000, 500, np.nan, 100, 'put']
general.to_sql(name='general', con=con, index=False)

flds = ('issued_volume', 'amt_out', 'res_nominal_value', 'next_offer_date', 'offer_price', 'option_type')
# 生成每个列的赋值语句，格式为 "列名=?"
set_clause = ", ".join([f"{field}=?" for field in flds])
update_values = (200000, 200000, 1000, '10.02.2022', 100, 'call', 'test_isin')
sql.execute(f"""UPDATE general SET {set_clause} WHERE isin = ?;""", update_values)
con.commit()
con.close()

方案2：升级SQLite版本 #

如果需要保留原语法，需将SQLite升级到3.35.0及以上版本：

• Python自带的sqlite3模块绑定的是系统或Python内置的SQLite库，直接复制文件夹无效。
• 可通过升级Python版本（高版本Python自带更新的SQLite），或者安装pysqlite3替代内置模块：

  pip install pysqlite3-binary
  

  代码中替换导入：

  import pysqlite3 as sqlite3
  

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