tortoise.exceptions.OperationalError: near “ORDER“: syntax error

使用make test_sqlite进行tortoise-orm的单元测试时，报了如下错误：

...
execute_fetchall
    return await self._execute(self._execute_fetchall, sql, parameters)
  File "/Users/mac10.12/Library/Caches/pypoetry/virtualenvs/tortoise-orm-iT379CwK-py3.10/lib/python3.10/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/Users/mac10.12/Library/Caches/pypoetry/virtualenvs/tortoise-orm-iT379CwK-py3.10/lib/python3.10/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
  File "/Users/mac10.12/Library/Caches/pypoetry/virtualenvs/tortoise-orm-iT379CwK-py3.10/lib/python3.10/site-packages/aiosqlite/core.py", line 81, in _execute_fetchall
    cursor = self._conn.execute(sql, parameters)
sqlite3.OperationalError: near "ORDER": syntax error

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/mac10.12/github/tortoise-orm/tests/test_update.py", line 158, in test_update_with_limit_ordering
    await Tournament.filter(name="1").limit(1).order_by("-id").update(name="2")
  File "/Users/mac10.12/github/tortoise-orm/tortoise/queryset.py", line 1111, in _execute
    return (await self._db.execute_query(str(self.query), self.values))[0]
  File "/Users/mac10.12/github/tortoise-orm/tortoise/backends/sqlite/client.py", line 36, in translate_exceptions_
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: near "ORDER": syntax error
============================================================== short test summary info ===============================================================
FAILED tests/test_queryset.py::TestQueryset::test_delete - tortoise.exceptions.OperationalError: near "ORDER": syntax error
FAILED tests/test_queryset.py::TestQueryset::test_delete_limit - tortoise.exceptions.OperationalError: near "LIMIT": syntax error
FAILED tests/test_queryset.py::TestQueryset::test_delete_limit_order_by - tortoise.exceptions.OperationalError: near "ORDER": syntax error
FAILED tests/test_update.py::TestUpdate::test_update_with_limit_ordering - tortoise.exceptions.OperationalError: near "ORDER": syntax error
ERROR tests/test_default.py
ERROR tests/test_two_databases.py
4 failed, 1038 passed, 85 skipped, 4 xfailed, 2 errors in 31.25s
make: *** [test_sqlite] Error 1

其中python版本为3.10，sqlite3版本为3.32.3 2020-06-18 14:16:19

操作系统为Mac 11.7.4

解决：参照这个https://github.com/tortoise/tortoise-orm/issues/923

一、安装新版sqlite3

STEM=sqlite-src-3410000
FILE=$STEM.zip

if [ -d $STEM ]; then
  echo Folder $STEM exists.
else
  if [ -f $FILE ]; then
    echo File $FILE exists.
  else
    wget https://www.sqlite.org/$(date +'%Y')/$FILE
  fi
  unzip $FILE
fi

cd $STEM && \
./configure --prefix=/usr/local --disable-static --enable-update-limit && \
make sqlite3.c && \
make install && \
echo Done.

打印版本号验证安装结果：

/usr/local/bin/sqlite3 --version
# 3.41.0 2023-02-21 18:09:37 05941c2a04037fc3ed2ffae11f5d2260706f89431f463518740f72ada350866d

二、修改python链接的sqlite3版本

*我的是Mac系统，export LD_LIBRARY_PATH="/usr/local/lib"的方式没用*

1. 展示当前版本

python -c 'import _sqlite3 as m;print(m.sqlite_version)'
# 3.40.1

2. 查找该版本所在目录

sudo find / -name "*sqlite*"

从屏幕里找到旧版sqlite3的路径：/usr/local/Cellar/sqlite/3.40.1

3. 将lib里的文件指向新版本

cd /usr/local/Cellar/sqlite/3.40.1/lib
mv libsqlite3.a libsqlite3.a_3.40.1
ln -s /usr/local/lib/libsqlite3.a libsqlite3.a
mv libsqlite3.0.dylib libsqlite3.0.dylib_3.40.1
ln -s /usr/local/lib/libsqlite3.0.dylib libsqlite3.0.dylib

重新执行步骤1，发现python里的sqlite3版本已经是3.41.0

这时候再执行make test_sqlite就不再报OperationalError的错误了