0

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.

racic
  • 1,235
  • 11
  • 20
  • 2
    Rendering the query that way will always show a bind, but the query itself definitely has exactly `A`. What exactly do you mean? If you want a static statement, why not just write it out as is? – metatoaster Oct 25 '17 at 07:10
  • This query is an example, I have a more complex query built with SQLAlchemy and there are `bindparam`s there. What I cannot seem to achieve is the mix of the two - exact `WHERE` and a data-bound `WHERE`. – racic Oct 25 '17 at 07:20
  • 1
    Try `print(q.statement.compile(compile_kwargs={"literal_binds": True}))`; I am operating on that you only wanted to see the query, as you have not described _why_ you need this and _what_ you are using this for. – metatoaster Oct 25 '17 at 07:21
  • 1
    If that does exactly what you wanted, it is a duplicate of https://stackoverflow.com/questions/4617291/how-do-i-get-a-raw-compiled-sql-query-from-a-sqlalchemy-expression; otherwise edit your question to be more specific by including what exactly you want to achieve and/or why you need this. – metatoaster Oct 25 '17 at 07:23
  • What I needed is exactly what I've written - a `WHERE` condition that matches a constant string value. The answer is, I suppose, that SA does what it's told, it just wasn't clear (to a SA newbie) how the actual query looked like. I edited the question to state 'constant' instead of 'static'. Thank you very much for the quick clarification! This question is not a duplicate however, as seeing the resultant query was not the goal. – racic Oct 25 '17 at 07:47
  • It is still not clear as to **why** you are doing this, but more in-depth explanation given anyway... – metatoaster Oct 25 '17 at 08:30

1 Answers1

1

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.

metatoaster
  • 17,419
  • 5
  • 55
  • 66