sqlalchemy.text() used for qmark queries in SQLite

[marcelmindemann]

Hello,
I am using sqlc v1.27.0, sqlc-gen-python 1.2.0 and sqlalchemy 1.4.54.

Example query:

-- name: GetUser :one
SELECT * FROM users
WHERE id = ?

Generated code:

GET_USER= """-- name: get_user \\:one
SELECT id FROM users
WHERE id = ?
"""

class Querier:
    def __init__(self, conn: sqlalchemy.engine.Connection):
        self._conn = conn

    def get_user(self, *, id: Any) -> Optional[models.User]:
        row = self._conn.execute(sqlalchemy.text(GET_USER), {"p1": id}).first()
        if row is None:
            return None
        return models.User(
            id=row[0],
        )

This code does not execute. Running

users.get_user(id=1)

gives error

ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

This happens because sqlalchemy.text() expects bound parameters to use where id = :id style. By using qmark style bound parameters (where id = ?), this query is not recognized to be a prepared statement and is probably run as-is, and SQLalchemy does not send parameter values with it. Reference: https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-textual-sql

I cannot find a workaround for this. Using

-- name: GetUser :one
SELECT * FROM users
WHERE id = :id

or

-- name: GetUser :one
SELECT * FROM users
WHERE id = sqlc.arg(id)

yields

GET_USER= """-- name: get_tree \\:one
SELECT id, email, full_name FROM users
WHERE id = ?1

which causes the same error. If you want to use sqlalchemy.text(), you have to use :parameter syntax.

[baderj]

I ran into the same issue. Looks like the problem is known for quite some time #3. According to the language support page of sqlc, SQLite is not supported.

Would it be possible to throw an error when using the engine: sqlite? I think this would be preferable to having code generated that does not work.

[marcelmindemann]

Sure, throwing an error would be better than nothing, but fixing it would be even better of course. I think the fix should be fairly simple, since the generated Python code has a kwarg named after the SQL field id:

def get_user(self, *, id: Any)

So the rendering code should have knowledge somehow of this identifier, and can use it to correctly render named placeholders instead of question marks. If someone can point me towards a piece of code where it uses this knowledge to build the function header, I can try to prepare a fix.

[marcelmindemann]

For anyone running into the same issue: I found a workaround.
Change your prepared statements to use @placeholder syntax:

-- name: GetUser :one
SELECT * FROM users
WHERE id = @id 

and set the engine to postgresql instead of sqlite. The generated code will still work for SQLite because of SQLAlchemy.