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.

Answer 1

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