CODEWITHSUNDEEP
sqltransactionssqlalchemyrollbackddl
running.t
running.t

Reputation: 5709

Is it possible to rollback create_all in sqlalchemy?

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']

Upvotes: 5

Views: 2055

Answers (1)

zzzeek
zzzeek

Reputation: 75207

  1. 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.

  2. 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.

  3. 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

Upvotes: 5

Related Questions