Executing raw SQL on the SQLAlchemy engine versus a session

Question

What is the difference between executing raw SQL on the SQLAlchemy engine and the session? Specifically against a MSSQL database.

engine.execute('DELETE FROM MyTable WHERE MyId IN(1, 2, 3)')

versus

session.execute('DELETE FROM MyTable WHERE MyId IN(1, 2, 3)')

I've noticed that executing the SQL on the session, cause MSSQL to 'hang'.

Perhaps someone has an idea on how these two executions are different, or perhaps someone can point me where to further investigate.

You can set echo=True on your create_engine call and every query will be printed out. Check what are the differences between the two. — Dekel
engine.execute(..) might create a new transaction, whereas session.execute(..) uses the current transaction of the session. I guess the difference might be related to that. Try to enable SQL logging as per @Dekel's comment to understand why? or perform session.commit() or session.rollback() just before session.execute to test this hypothesis. — van

indi indi · Accepted Answer · 2015-09-06T02:25:11

The reason why MSSQL Server was hanging, was not because of the difference between calling execute on the engine or the session, but because a delete was being called on the table, without a commit, and then a subsequent read.

Executing raw SQL on the SQLAlchemy engine versus a session

1 Answers