Executing Non-ORM statements in views triggers ROLLBACK instead of COMMIT

[redbaron4]

I have a Pyramid application (SQLAlchemy-2.0) which has a delete operation which does not manipulate ORM objects but deletes using the underlying Table instance (non-ORM way)

The session is registered

_SESSION = scoped_session(sessionmaker())
register(_SESSION)

_ENGINE = engine_from_config(settings, prefix)
_SESSION.configure(bind=_ENGINE)

The function in question works on tables

def delete_from_table(table, session, id_collection, attrname="event_id"):
    d = table.delete()
    ax = getattr(table.c, attrname)
    d2 = d.where(ax.in_(id_collection))
    session.execute(d2)

and invoked using

delete_from_table(MyModel.__table__, session, all_list)

The problem is that at end of the execution a ROLLBACK is issued when connection is returned to pool instead of a COMMIT even if no error is encountered.

My understanding is that Zope DataManager issues a rollback on read-only transactions and the following line does not trigger in this case (as transaction is non ORM), hence transaction is marked as read-only and no COMMIT is issued

zope.sqlalchemy/src/zope/sqlalchemy/datamanager.py

Line 339 in 35b239a

if execute_state.is_orm_statement and dml:

Is there a reason why execute_state.is_orm_statement is checked here?

As a workaround I have to manually call mark_changed(session) in the function after session.execute()