Unable to use f-string in SqlAlchemy ORM

[ruslaniv]

• asyncpg version: 0.23.0
• PostgreSQL version: 12
• Python version: 3.9.4
• Platform: OSX
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: pipenv
• If you built asyncpg locally, which version of Cython did you use?: N/A
• Can the issue be reproduced under both asyncio and uvloop?: N/A

I'm trying to build a SQL statement with LIKE expression and a pattern but I'm getting an error.

Here is what I had in the code originally:

statement = select(Customer).options(sqlalchemy.orm.joinedload(Customer.transactions)).where(Customer.l_name.ilike(name))

and this worked great.

But if I want to use this statement with a % pattern like this:

statement = f'select(Customer).options(sqlalchemy.orm.joinedload(Customer.transactions)).where(Customer.l_name.ilike("%{name}%"))'

I get this error:

sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.PostgresSyntaxError'>: syntax error at or near "("
[SQL: select(Customer).options(sqlalchemy.orm.joinedload(Customer.transactions)).where(Customer.l_name.ilike("%%turing%%"))]

It does substitute the variable with a parameter but for some reason it adds an extra pair of percent signs and crashes. How can I solve this problem?

[hrist0stoichev]

There is a big difference between statement = select(...) and statement = "select(...)"

The first one is actually calling select and the second one is just a string. You should try something like that though I haven't tested it:

statement = select(Customer).options(sqlalchemy.orm.joinedload(Customer.transactions)).where(Customer.l_name.ilike(f'%{name}%'))