SQLAlchemy: how to print real query from db session

Question

There are other questions similar to mine but none of them can help me find a solution. I have a query that is

db.session.query(cls).filter_by(id=id).delete()

and I'm trying to print the exact query. I have tried to following

#1

a = db.session.query(cls).filter_by(id=id).delete()
print str(a) # gives an error

#2

a = db.session.query(cls).filter_by(id=id).delete()
print (a.statement.compile(dialect=postgresql.dialect())) # gives error like 
# AttributeError: 'int' object has no attribute 'statement'

#3

query = db.session.query(cls)
print(str(query.statement.compile(
            dialect=postgresql.dialect(),
            compile_kwargs={"literal_binds": True})))


# prints SELECT person_data.id, person_loc.name, person.address, person.id from person_table
   # doesn't show the id deletion in the query

How can I print the exact query the db session is doing during the delete?

Usually the only interesting element of a delete statement is the selection criteria, so I would print the query without the call to `.delete()`. — snakecharmerb, Mar 26 '22 at 11:19

score 1 · Answer 1 · answered Mar 25 '22 at 04:32

The Query.delete() usage is executed immediately so you won't be able to print the statement. I think the return value is the number of rows deleted so that is why you receive that " int' object has no attribute 'statement'". So you can't print that directly other than just using echo flag.

If you are using SQLAlchemy 1.4+ in 2.0 style you can use the delete core construct like q = delete(cls).where(cls.id == id) to generate the statement and then print that. You would actually run it with db.session.execute(q) from that example. There are a lot of caveats to printing bound params (like id).

The caveats are explained in how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined

You could also use the echo flag to print all statements to the logs but that doesn't inline the parameters.

There is a similar question from a long time ago that now has a link to the sqlalchemy doc which I included above. This question involves printing the special orm query delete() method though.

SQLAlchemy: print the actual query

SQLAlchemy: how to print real query from db session

1 Answers1