How to Obtain the Actual SQL Query Executed in SQLAlchemy?

Printing Actual SQL Queries in SQLAlchemy

Executing SQL queries in SQLAlchemy involves binding parameters to prevent SQL injection attacks. However, obtaining the actual SQL query, including the values, can be challenging.

General Solution

The simplest method to retrieve the SQL query as a string is to use the str() function:

<code class="python">print(str(statement))  # for both ORM Query and SQL statements</code>

Bound Parameters Implementation

By default, SQLAlchemy binds parameters for security reasons. You can suppress this behavior using the literal_binds flag in compile_kwargs:

<code class="python">from sqlalchemy.dialects import postgresql

print(
    statement.compile(compile_kwargs={"literal_binds": True})
)</code>

Note that literal binding is only supported for basic types like integers and strings.

Customizing Parameter Conversion

If you need to convert more complex types, you can create a custom TypeDecorator with a process_literal_param method:

<code class="python">class MyFancyType(TypeDecorator):
    impl = Integer

    def process_literal_param(self, value, dialect):
        return "my_fancy_formatting(%s)" % value

print(
    tab.select().where(tab.c.x > 5).compile(
        compile_kwargs={"literal_binds": True})
)</code>

This will produce a query like:

<code class="sql">SELECT mytable.x
FROM mytable
WHERE mytable.x > my_fancy_formatting(5)</code>

The above is the detailed content of How to Obtain the Actual SQL Query Executed in SQLAlchemy?. For more information, please follow other related articles on the PHP Chinese website!