Extract raw sql from SqlAlchemy with replaced parameters

Question

Given a sql query such as

query = """
select some_col from tbl where some_col > :value
"""

I'm executing this with sqlalchemy using

connection.execute(sa.text(query), {'value' : 5})

Though this does what's expected, I would like to be able to get the raw sql, with replaced parameters. Meaning I would like a way to be able to get

select some_column from tbl where some_column > 5

I've tried to echo the sql using:

    engine = sa.create_engine(
        '<CONNECTION STRING>',
        echo=True,
    )

But this didn't replace the parameters.

If there's not a way to do this in sqlalchemy, but is a way using something like psycopg2 (as long as the syntax :value doesn't change) then that would be of interest.

Does this answer your question? [How do I get a raw, compiled SQL query from a SQLAlchemy expression?](https://stackoverflow.com/questions/4617291/how-do-i-get-a-raw-compiled-sql-query-from-a-sqlalchemy-expression) — Ian Wilson, Apr 17 '23 at 22:13
@IanWilson thanks - I'm not sure how I would apply that answer to my question. From the context lower down on the page it seems that `q` is different to the raw string that I have in my example. — baxx, Apr 18 '23 at 00:04
You have to understand that for a query with parametriesed *columns* you'll need a *dynamic SQL*. Search for "SqlAlchemy dynamic SQL" e.g. [here](https://stackoverflow.com/q/46598276/4808122) — Marmite Bomber, Apr 18 '23 at 15:07
@MarmiteBomber thanks - sorry but I don't understand why this is dynamic in the sense of what you've linked. I know exactly what I want, there's nothing here which is variable or dynamic - it's just parameterised. — baxx, Apr 18 '23 at 17:20
You will never get those two queries `select col1 from T` and `select col2 from T` to work with a parameter `'col1'` and `'col2'`. You can't pass *table* or *column* names in a way as you pass a *value* in `where col = :id` - you **need a dynamic SQL** @baxx — Marmite Bomber, Apr 19 '23 at 09:12
@MarmiteBomber Sorry to cause confusion there I wasn't aware - I have updated the post to be clearer in that case. — baxx, Apr 19 '23 at 09:49

score 1 · Accepted Answer · answered Apr 19 '23 at 14:36

The SQLAlchemy documentation for Rendering Bound Parameters Inline explains how we can use literal_binds:

query = "select some_col from tbl where some_col > :value"
print(
    sa.text(query)
    .bindparams(value=5)
    .compile(compile_kwargs={"literal_binds": True})
)
# select some_col from tbl where some_col > 5

Extract raw sql from SqlAlchemy with replaced parameters

1 Answers1