I'm trying to generate a certain SQL statement with SQLAlchemy where there is an existing schema and data inside the database. I approach it like this:
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine, Table from sqlalchemy.ext.declarative import declarative_base Session = sessionmaker() engine = create_engine('sqlite:///example.db') Session.configure(bind=engine) session = Session() base = declarative_base() base.metadata.reflect(engine) table = Table('dane', base.metadata, autoload=True) q = session.query(table).filter(table.c.title == 'A') print(q)
When I examine the rendered (generated) query from the above code it generates this:
SELECT dane.title AS dane_title, dane.body AS dane_body FROM dane WHERE dane.title = ?
I do not need, however, a dynamic statement, I need the
? in the query to be exactly
A as would the
.filter call suggest. How can I achieve a constant
WHERE expression with SQLAlchemy?
SA actually does generate constant WHERE conditions. To see them you need to pass special kwargs to compile():
as in @metatoaster's answer below.
The query at generation is already static but does not look bounded is because
sqlalchemy always parameterize all query values no matter where they come from to prevent SQL injection attacks by default. This however does not mean the query is not static. For a complete example, create a table fully in memory (using sqlalchemy) plus other setup.
>>> from sqlalchemy import MetaData, create_engine, Table, Column, String >>> metadata = MetaData() >>> metadata.bind = create_engine('sqlite:///') >>> table = Table('table', metadata, ... Column('name', String(), nullable=False), ... ) >>> metadata.create_all() >>> table.insert().values(name='value').execute()
Get a generic query working to see that we got some data.
>>> from sqlalchemy.orm import sessionmaker >>> Session = sessionmaker() >>> Session.configure(bind=metadata.bind) >>> session = Session() >>> session.query(table).all() [('value',)]
Now, instead of supplying a string, we supply a variable to the query.
>>> value = 'value' >>> q = session.query(table).filter(table.c.name == value)
Change the value, execute the query
>>> value = 'somethingelse' >>> q.all() [('value',)]
It is as "static" as one might expect. Also note that in the documentation (the tutorial) linked earlier, the data is stored in the construct, just not rendered.