How to get raw sql from session.add() in sqlalchemy?

Question

Here is the answer for getting raw sql from insert and I am wondering whether I can get raw sql if I use session.add() such as:

session.add(ormclass).compile()

Answer 1

If you want SQLAlchemy to log the actual raw SQL queries you can always set echo=True on the create_engine method. I know this seems rudimentary but it's the easiest way to see executed queries.

engine = create_engine("postgresql://localhost/dbname", echo=True)

Answer 2

My temporal solution to this is that when an error happens, there will be an exception and in this exception there are the parameters and statement.

except Exception as inst:
    print(inst.statement % inst.params)

But the problem still exists because I cannot get the statement and parameters if there are no exceptions. In addition, there are no quotation marks for strings in the print so the string cannot be executed in mysql directly.

Answer 3

The way that I display the raw sql (which works most [but not all] of the time):

query = [my query that I created]
from sqlalchemy.dialects import mysql
str_query = query.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True})

Then I can just print the sql_query statement and it will show me the query with arguments. Some queries won't display, such as bulk inserts.