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
– snakecharmerb, Commented Mar 26, 2022 at 11:19

Ian Wilson · Accepted Answer · 2022-03-25 04:32:19Z

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

Collectives™ on Stack Overflow

SQLAlchemy: how to print real query from db session

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related