CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
Jeff Widman
Jeff Widman

Reputation: 23482

Flask-SQLAlchemy: How can I call db.create_all() and db.drop_all() without triggering database commits?

I have a fairly standard Flask-SQLAlchemy setup connected to a PostgreSQL 9.4 database.

For testing purposes, I'd like to call db.create_all() within the scope of a database transaction, run my test, then call db.drop_all()--all without committing that database transaction. This is possible because PostgreSQL wraps DDL in transactions.

However, whenever I call db.create_all() or db.drop_all(), Flask-SQLAlchemy issues a COMMIT after every individual table is created. Similarly, it issues a COMMIT after each table is dropped.

I have COMMIT_ON_TEARDOWN = True, but AFAIK this should only matter for the final commit--it shouldn't cause the intermediate commits happening after each table.

How do I change this behavior so that the tables are created or dropped without automatically triggering database commits?

Upvotes: 4

Views: 2355

Answers (2)

agroszer
agroszer

Reputation: 85

I'm an sqlalchemy noob, but this seems to do the trick:

with contextlib.closing(engine.connect()) as con:
    trans = con.begin()
    database.metadata.create_all(bind=con)
    trans.commit()

Upvotes: 0

Andrei Sura
Andrei Sura

Reputation: 2604

It looks like sqlalchemy implements the create_all() with autocommit = True so probably there is no way to change this behavior.

The relevant code: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/engine/base.py#L199

https://github.com/zzzeek/sqlalchemy/blob/859379e2fcc4506d036700ba1eca4c0ae526a8ee/lib/sqlalchemy/sql/ddl.py#L60

Update:

My answer is correct for MySQL which does not support nested transactions ==> https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html. As @Jeff Widman mentioned in his answer there is a way to do it for PostgreSQL "by replacing the global session with one running in a nested transaction"

Upvotes: 2

Related Questions