"RETURNING" statement causes syntax error with SQLite backend

[ameryisafreeelf]

OS: macOS Monterey, 12.0.1 (M1 Pro) python version: Python 3.7 databases version: 0.5.5 database backend (mysql, sqlite or postgresql): sqlite database driver (aiopg, aiomysql etc.): aiosqlite

I'm trying to use the RETURNING key word in a raw query. RETURNING has been supported by SQLite since version 3.35* (I am using 3.37.2). I get this error: sqlite3.OperationalError: near "RETURNING": syntax error

Here's what I'm doing (basically):

# databases[sqlite] in requirements.txt
from databases import database

class BaseRepository:
    def __init__(self, db: Database) -> None:
        self.db = db


class MyRepository(BaseRepository):
    async def create_report(self, *):

        my_query = """ INSERT INTO my_table (name, description) VALUES ("a", "b") RETURNING *; """

        report = await self.db.fetch_one(query=my_query, values=query_val)                                # BREAKS

I was originally using a postgresql backend, and using RETURNING had no issues.

Seems like the issue is simply that having RETURNING in the query breaks the fetch_one. I had no issue writing that query directly into the SQLite shell, so the issue shouldn't be the query itself.

This closed issue seems to resolve by assuming that fetch_one is able to handle queries with RETURNING statements: https://github.com/encode/databases/issues/98.

Please let me know if you have any questions, thanks!

[aminalaee]

Hey, can you share the full traceback? Or the query logged by databases?

My guess is that since we're doing compile query with SQLAlchemy and the RETURNING clause is not yet supported by SQLAlchemy in SQLite.

[natskvi]

@ameryisafreeelf See this discussion thread, in particular this comment on how to patch SQLAlchemy to enable the .returning() syntax.

[opefeni]

@ameryisafreeelf, RETURNING is support from sqlite version 3.35.0 per the documentation of sqlite website

Please check the version of your sqlite and upgrade if necessary.