Repository navigation
Query compilation #6096
Description
Describe the bug
My query:
query = select([Department], limit=limit, offset=offset).where(
Department.department_id.in_([x.department_id for x in permitted_departments]))turns into:
SELECT department.department_id, department.department_name
FROM department
WHERE department.department_id IN ([POSTCOMPILE_department_id_1]) with error:
ERROR: syntax error at or near "["
Expected behavior
SELECT department.department_id, department.department_name
FROM department
WHERE department.department_id IN (%(department_id_1)s)Versions.
- OS: Debian buster
- Python: 3.8
- SQLAlchemy: 1.4.1
- Database: Postgres
Activity
CaselIT commentedon Mar 19, 2021
Hi,
How are you using the query? What you have posted seems like
str(query). That will not give you valid sql in the general case.noisywiz commentedon Mar 19, 2021
Hi, it's debug print
query.compile(dialect=postgresql.dialect())). Actualy I fetch this withdatabases.Database.fetch_allmethod (https://pypi.org/project/databases/) and I'm fine withSQLAlchemy==1.3.23CaselIT commentedon Mar 19, 2021
Hi,
In 1.4 how the query is complied has changed to enable the caching of the generated strings.
To enable compilation if the query in a more complete way, you can use:
compile(dialect=dialect(), compile_kwargs={"literal_binds"=True, "render_postcompile"=True})Note that non all datatypes support literal compile with
"literal_binds"=True, so it may error on more complex typesCaselIT commentedon Mar 20, 2021
@noisywiz do you have another more specific question or can we move this to a discussion?
noisywiz commentedon Mar 20, 2021
Yes close it. Thanks for help!