Thanks for the fast help, Francisco! That tip about the metadata
object is very handy. I'm now able to migrate using DeclarativeBase.
Copy-and-paste just got much easier! One thing, I do have to include
the 'useexisting':True in table_args, even for new tables such as
Account. Otherwise I get: "Table 'account' is already defined for
this MetaData instance". Something going on under the SQLAlchemy hood
that I don't understand, I guess.
I'm still not able to wrap table creates within transactions. I am
using InnoDB, and I think there are some other engines for mySQL
(falcon) that support transactions, but I can't figure out how to make
it happen for table creation. This is just one of several things I
tried including autocommit=false, and the transaction object. It is a
bit of an inconvenience while debugging your code, but manageable.
...
Session = sessionmaker(autocommit=True) # Or False if you want to do a
manual commit with each DB change
Session.configure(bind=migrate_engine)
session = Session()
mysql_engine_type = 'InnoDB'
...
class Account (DeclarativeBase):
__tablename__= 'account'
__table_args__ = {'mysql_engine':mysql_engine_type,
'useexisting':True} <<=== IMPORTANT TO ADD
id = Column(Integer, primary_key=True)
tm_customer_id = Column(Integer, ForeignKey('company.id'),
nullable=False)
parent_id = Column(ForeignKey('account.id'), nullable=True)
name = Column(Unicode(20), nullable=True)
description = Column(Unicode(100), nullable=True)
balance = Column(Numeric(scale=2, asdecimal=True))
isHidden = Column(BOOLEAN)
type = Column (String(15))
... Other classes here...
def upgrade():
try:
session.begin() # Start transaction, but tables are always
committed
(See below)
DeclarativeBase.metadata.tables[Account.__tablename__].create(migrate_engine)
DeclarativeBase.metadata.tables[AccountingOperator.__tablename__].create(migrate_engine)
# This next line causes an exception. If a
transaction, none previous would commit
DeclarativeBase.metadata.tables[FAILONItemGroup.__tablename__].create(migrate_engine)
session.commit() # End transaction for table creation
session.begin() # Start transaction for inserts, alters, etc.
# Adding data, cols, etc is transactional and behaves
as expected
...
session.commit()
print "UPGRADE SUCCESSFUL"
except:
print "UPGRADE FAILURE, ROLLING BACK"
session.rollback()
Now the SQL echo when the Account table is created. The commit is
still there.
Upgrading...2010-06-18 00:39:13,507 INFO sqlalchemy.engine.base.Engine.
0x...09d0
CREATE TABLE account (
id INTEGER NOT NULL AUTO_INCREMENT,
tm_customer_id INTEGER NOT NULL,
parent_id INTEGER,
name VARCHAR(20),
description VARCHAR(100),
balance NUMERIC(10, 2),
`isHidden` BOOL,
type VARCHAR(15),
PRIMARY KEY (id),
FOREIGN KEY(tm_customer_id) REFERENCES company (id),
FOREIGN KEY(parent_id) REFERENCES account (id)
)ENGINE=InnoDB
2010-06-18 00:39:13,507 INFO sqlalchemy.engine.base.Engine.0x...09d0
()
2010-06-18 00:39:13,571 INFO sqlalchemy.engine.base.Engine.0x...09d0
COMMIT
2010-06-18 00:39:13,573 INFO sqlalchemy.engine.base.Engine.0x...09d0
On Jun 17, 6:00 am, Francisco Souza <franci...@franciscosouza.net>
wrote:
> > Hi ,
>
> > [...]
>
> > But do I do this? Part of the problem is that I don't know of a way
> > to generate tables other than create_all() (or drop_all()) when using
> > declarative syntax. Is there another way?
>
> Hi Shane :)
>
> When you bind your declarative base to a metadata, the "metadata" object has
> a attribute called "tables", wich is a dict with all tables containeds in
> this metadata. So, you can do a create for a single table easily.
>
> >>> Base.metadata.tables['test_case'].create(engine)
>
> You can call it on your upgrade function inside the migration module.
>
> 2) Can MySQL wrap Table.create() in a transaction so that if something fails
>
> > during upgrade/downgrade I don't litter up my DB with tables? I don't think
> > MySQL can, but maybe I am wrong. I think some others (PostgreSQL) can.
>
> I do not know how to answer this question, becausa I am not experienced with
> MySQL, but I can check this out. What I know is that MySQL supports
> transactions only when you are working with InnoDB engine.
>
> Best wishes,
> Francisco Souza
> Software developer at Giran and also full time
> Open source evangelist at full time
>
> http://www.franciscosouza.com.br
> Twitter: @franciscosouza
> (27) 3026 0264
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
