Is it possible to rollback create_all in sqlalchemy?

Question

According to THIS question for some DBMSs it is possible to rollback CREATE TABLE statement. Particularry it is possible for sqlite (although it is undocumented).

So my question is, is it possible to rollback create_all in sqlalchemy? I was trying to write some test code, but it seems not to work:

>>> engine = create_engine('sqlite:///:memory:')
>>> engine
Engine(sqlite:///:memory:)
>>> Session = sessionmaker(bind=engine)
>>> connection = engine.connect()
>>> session = Session(bind=connection)
>>> engine.table_names()
[]
>>> transaction = connection.begin()
>>> Base = declarative_base()
>>> class Test(Base):
...    __tablename__ = 'TEST'
...    id = Column(Integer, primary_key=True)
...
>>> Base.metadata.bind = engine
>>> Base.metadata.create_all()
>>> engine.table_names()
[u'TEST']
>>> transaction.rollback()
>>> session.close()
>>> connection.close()
>>> engine.table_names()
[u'TEST']

If you want to do within the **ORM** itself (for test case etc.), check: https://stackoverflow.com/questions/15774899/how-do-you-create-a-table-with-sqlalchemy-within-a-transaction-in-postgres — Nishant, Jul 09 '22 at 10:50

score 5 · Accepted Answer · edited Jan 12 '16 at 09:32

5

Use Postgresql or SQL Server. MySQL, Oracle do not support transactional DDL. Recent versions of SQLite do appear to support transactional DDL. The Python sqlite3 driver however does not.
simple recipe:
```
with engine.begin() as conn:
    metadata.create_all(conn)
```
if you raise an exception inside the "with:", the transaction will be rolled back.

want to see it yourself, OK:

from sqlalchemy import inspect   # need to be running 0.8 for this

with engine.connect() as conn:
   trans = conn.begin()
   metadata.create_all(conn)
   inspector = inspect(conn)
   table_names = inspector.get_table_names()
   trans.rollback()
   inspector = inspect(conn)
   rolled_back_table_names = inspector.get_table_names()

to gain an understanding of transactional scope, I recommend you read http://docs.sqlalchemy.org/en/latest/core/connections.html

edited Jan 12 '16 at 09:32

Jeff Widman

22,014
12
72
88

answered Mar 29 '13 at 14:28

zzzeek

72,307
23
193
185

Thanks for your answer. To be precise I do know that it is possible for engines you have mentioned. As I understand correctly your answer means: it is not possible in SQLAlchemy. (because it IS possible to rollback create table in sqlite client - sqlite3). Is that correct? – running.t Mar 30 '13 at 14:30
edited. it's the sqlite3 driver, which has the above mentioned issue as well as other [long unresolved](http://bugs.python.org/issue9924) issues regarding its poor handling of transaction state. – zzzeek Mar 30 '13 at 18:17

Is it possible to rollback create_all in sqlalchemy?

1 Answers1