时间戳

首页 > 解决方案 > peewee.OperationalError 运行查询

sqlite - peewee.OperationalError 运行查询

问题描述

当我运行以下代码时，我很难确定产生异常的原因。任何帮助，将不胜感激。

请注意，该错误实际上是由 sqlite 报告为 SQL 语法错误。

数据库是 sqlite 版本 3.7.17，我正在使用 peewee 版本 3.13.3 和 Python 3.6.8。操作系统是 Centos 7。

from peewee import *
from covid.models import Confirmed
query = Confirmed.select(
    fn.SUM(Confirmed.count)
    .over(
        order_by=[Confirmed.date],
        start=Window.preceding(3)
    )
    .alias('rsum')
).where(Confirmed.uid == 84036061)
print(query)
for q in query:
    print(q)

确认模型是：

class Confirmed(BaseModel):
    uid = ForeignKeyField(Domain)
    date = DateField()
    count = IntegerField()

输出是：

SELECT SUM("t1"."count") OVER (ORDER BY "t1"."date" ROWS 3 PRECEDING) AS "rsum" FROM "confirmed" AS "t1" WHERE ("t1"."uid_id" = 84036061)
Traceback (most recent call last):
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: near "(": syntax error

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "tests/x.py", line 14, in <module>
    for q in query:
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 6797, in __iter__
    self.execute()
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 1886, in inner
    return method(self, database, *args, **kwargs)
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 1957, in execute
    return self._execute(database)
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 2129, in _execute
    cursor = database.execute(self)
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 3112, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 3106, in execute_sql
    self.commit()
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 2873, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/home/cp/sw/make/covid/venv/lib64/python3.6/site-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: near "(": syntax error

标签： sqlitepeewee

解决方案

正如DinoCoderSaurus正确指出的那样，CentOS 7 预装的 SQLite 版本 3.7.17 不提供 Window 功能支持。

为了其他可能遇到相同问题的人的利益，以下是我解决问题的方法：

为了使用当前的 SQLite 版本，我将它安装在应用程序的 venv 中，方法是：

pip install pysqlite3-binary

（有关 venv 的更多信息，请参阅：https ://docs.python.org/3/library/venv.html ）

SQLite 被 CentOS 广泛使用，因此出于向后兼容的原因，替换 /usr/bin 中的系统范围版本并不是一个好主意。

我还应该注意，当我尝试时：

pip install pysqlite3  # Instead of pysqlite3-binary

当我尝试卸载时，安装“成功”完成：

pip uninstall pysqlite3

pip 发出此警告：

警告：跳过 pysqlite3，因为它没有安装。

推荐阅读