CODEWITHSUNDEEP
pythonsqlalchemy
racic
racic

Reputation: 1275

How do I generate constant WHERE condition in SQL Alchemy?

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?

UPDATE

SA actually does generate constant WHERE conditions. To see them you need to pass special kwargs to compile():

.compile(compile_kwargs={"literal_binds": True})

as in @metatoaster's answer below.

Upvotes: 0

Views: 856

Answers (1)

metatoaster
metatoaster

Reputation: 18898

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.

Upvotes: 1

Related Questions