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? — Ian Wilson
– Ian Wilson, Commented Apr 17, 2023 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
– baxx, Commented Apr 18, 2023 at 0: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 — Marmite Bomber
– Marmite Bomber, Commented Apr 18, 2023 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
– baxx, Commented Apr 18, 2023 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
– Marmite Bomber, Commented Apr 19, 2023 at 9:12

Gord Thompson · Accepted Answer · 2023-04-19 14:36:17Z

5

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

answered Apr 19, 2023 at 14:36

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract raw sql from SqlAlchemy with replaced parameters

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related