"RETURNING" not work on Linux
DragonGamees opened this issue · 3 comments
Description
I have exception
sqlite3.OperationalError: near "RETURNING": syntax error
SQL request
INSERT INTO "images" ("file_name") VALUES (?) RETURNING "id";
This problem is only in Linux, there is no such problem in Windows
Details
- OS:
5.10.0-11-amd64 #1 SMP Debian 5.10.92-1 (2022-01-18) x86_64 GNU/Linux
- Python version:
3.11.3
- aiosqlite version:
0.20.0
- Can you repro on 'main' branch?
I haven't tried it, I install it via pip
- Can you repro in a clean virtualenv?
Yes
This is not a problem with aiosqlite. This is because your Python runtime is compiled against an older version of SQLite that doesn’t support the RETURNING
clause:
The RETURNING syntax has been supported by SQLite since version 3.35.0 (2021-03-12).
https://sqlite.org/lang_returning.html
On my own Debian machine, Python is compiled against SQLite 2.6.0:
amethyst@nago ~ » python3.11
Python 3.11.2 (main, Apr 19 2023, 16:21:24) [GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
You will either need to find an alternative Python package that includes a newer build of SQLite, or you will need to build your own version with pyenv or similar.
However, this does not prevent you from working in windows
Python 3.11.7 (tags/v3.11.7:fa7a6f2, Dec 4 2023, 19:24:49) [MSC v.1937 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
For example
import asyncio
import aiosqlite
import sqlite3
async def main():
print(f"SQLITE3: {sqlite3.version}")
print(f"aiosqlite: {aiosqlite.__version__}")
db = await aiosqlite.connect(":memory:")
async with db.execute('''CREATE TABLE "images"(id INTEGER PRIMARY KEY,"file_name" TEXT)'''):
pass
async with db.execute('''INSERT INTO "images" ("file_name") VALUES (?) RETURNING "id";''',("sample_name", )) as c:
print(f"ID: {await c.fetchall()}")
async with db.execute('''INSERT INTO "images" ("file_name") VALUES (?) RETURNING "id";''',("sample_name", )) as c:
print(f"ID: {await c.fetchall()}")
asyncio.run(main())
Output
SQLITE3: 2.6.0
aiosqlite: 0.20.0
ID: [(1,)]
ID: [(2,)]
Sorry, I checked the wrong attribute. The correct value is sqlite3.sqlite_version
, which on my debian box is 3.34.1
— still too old for RETURNING
support:
>>> sqlite3.sqlite_version
'3.34.1'
Which matches the output from sqlite itself on the same machine:
$ sqlite3 --version
3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
Can you confirm your local sqlite version, and whether that matches what you see on Windows?