Create tables within a transaction

[Chris Miles]

I notice that a table create (and drop/etc) is always followed by an
implicit commit. Is it possible to suppress the commit or force SA to
create multiple tables in one transaction so that if any fail they can
all be rolled back?

Here's some code to demonstrate what I want. In this example, the
table creation fails half way through, but leaves 2 tables created. I
want the CREATEs rolled back if any of them fail.


import sqlalchemy as sa
engine = sa.create_engine('sqlite:///test1.sqlite')
engine.echo = True
metadata = sa.MetaData()

table1 = sa.Table("table1", metadata,
sa.Column('col1', sa.types.Integer,),
)
table2 = sa.Table("table2", metadata,
sa.Column('col1', sa.types.Integer,),
)
table3 = sa.Table("table3", metadata,
sa.Column('col1', sa.types.Integer,),
)

metadata.bind = engine
connection = engine.connect()
trans = connection.begin()
try:
table1.create()
table2.create()
table2.create()
table3.create()
trans.commit()
except:
trans.rollback()


I can't find away to link table.create() with the existing
transaction.

Note: I don't want to just use create_all(). This is part of an in-
house schema version control system.

Note 2: If I create tables from SQL (sending "CREATE TABLE ..."
strings) within the transaction then I get the desired behaviour.
However, I'd prefer to use table.create() for convenience (i.e. across
multiple engines).

Cheers,
Chris Miles

[Michael Bayer]

create() and create_all() take a "bind" argument which can be an
engine or connection. you want the connection in this case.

[Chris Miles]

That did the trick, thanks.

Well, actually, it did the trick for PostgreSQL but sqlite isn't
rolling back. The SA logs show the same commands are being sent to
both. Here's an example:

$ rm test1.sqlite
$ python sa_create_table_transaction_test.py
2009-02-06 13:39:29,006 INFO sqlalchemy.engine.base.Engine.0x..d0
BEGIN
2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table1 (
col1 INTEGER
)


2009-02-06 13:39:29,007 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table2 (
col1 INTEGER
)


2009-02-06 13:39:29,021 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0
CREATE TABLE table2 (
col1 INTEGER
)


2009-02-06 13:39:29,024 INFO sqlalchemy.engine.base.Engine.0x..d0 {}
2009-02-06 13:39:29,027 INFO sqlalchemy.engine.base.Engine.0x..d0
ROLLBACK
$ sqlite3 test1.sqlite
Loading resources from /Users/chris/.sqliterc
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .tables
table1 table2
sqlite>


And the updated test script:

# ----

import sqlalchemy as sa

engine = sa.create_engine('sqlite:///test1.sqlite')

#engine = sa.create_engine('postgres://localhost/test1')

engine.echo = True
metadata = sa.MetaData()

table1 = sa.Table("table1", metadata,
sa.Column('col1', sa.types.Integer,),
)
table2 = sa.Table("table2", metadata,
sa.Column('col1', sa.types.Integer,),
)
table3 = sa.Table("table3", metadata,
sa.Column('col1', sa.types.Integer,),
)

metadata.bind = engine
connection = engine.connect()
trans = connection.begin()
try:

table1.create(bind=connection)
table2.create(bind=connection)
table2.create(bind=connection)
table3.create(bind=connection)
trans.commit()
except:
trans.rollback()
# ----

Cheers,
Chris

[Michael Bayer]

sqlite doesn't include CREATE TABLE statements within the scope of a
transaction. I think that's a relatively rare behavior only seen in
Postgres, in fact - I dont think Oracle or MySQL have that behavior,
for example.

[Chris Miles]

Ok. I'll do some testing against other engines when I get a chance.
Thanks for helping.

Cheers
Chris Miles

[Chris Miles]

Manual testing with sqlite appears to show that CREATE TABLE is
transactional and can be rolled back. Consider::

$ sqlite3 test2.db

Loading resources from /Users/chris/.sqliterc
SQLite version 3.4.0
Enter ".help" for instructions

sqlite> BEGIN;
sqlite> CREATE TABLE test1 (foo INTEGER);
sqlite> CREATE TABLE test2 (foo INTEGER);
sqlite> COMMIT;
sqlite> .tables
test1 test2
sqlite> ^D
$ rm test2.db
$ sqlite3 test2.db

Loading resources from /Users/chris/.sqliterc
SQLite version 3.4.0
Enter ".help" for instructions

sqlite> BEGIN;
sqlite> CREATE TABLE test1 (foo INTEGER);
sqlite> CREATE TABLE test2 (foo INTEGER);
sqlite> ROLLBACK;
sqlite> .tables
sqlite>


Perhaps the behaviour I see through SA is a side effect of pysqlite?

Cheers,

Chris Miles

On Feb 6, 2:36 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:

[Michael Bayer]

maybe pysqlite, try it with pysqlite. SQLA has no specifics to PG
or SQLite that change what its doing.