create_all() only can determine the order of tables if you use ForeignKey and ForeignKeyConstraint objects correctly on the source Table objects and/or declarative classes.
See http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#many-to-many and http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#association-object for examples of these configurations. Note that mixing a fully mapped association object and "secondary" is a bit unusual and you'll want viewonly=True if you're doing that. On May 31, 2012, at 2:32 PM, Jeff wrote: > Perhaps it's relevant (though I suspect not) that the class Avalanche > actually contains: > > class Avalanche(Base): > .... > events = relationship("Event", > secondary=Avalanche_Event_Association) > > This is what prevents us from writing the classes in the following > order in the database definition .py file: > > class Event(Base): > ..... > > class Avalanche(Base): > .... > > Avalanche_Event_Association = Table('Avalanche_Event_Association', > .... > > Because Avalanche needs to reference Avalanche_Event_Association. I > hope, however, that the the create_all function is able to > appropriately create the tables anyway, regardless of their order in > the database definition .py file. > > Thanks! > > On May 31, 2:21 pm, Jeff <jeffalst...@gmail.com> wrote: >> The tables don't exist yet. The Base.metadata.create_all(engine) is to >> create them. >> >> Thanks! >> >> On May 30, 11:52 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: >> >> >> >> >> >> >> >>> This might be because the tables you're trying to reference are themselves >>> not InnoDB. Try running DESCRIBE on the referenced tables at the MySQL >>> console to help confirm this, as well as the same CREATE TABLE statement >>> below. >> >>> On May 30, 2012, at 11:31 PM, Jeff wrote: >> >>>> Having difficulty creating a database that includes the following >>>> plumbing: >> >>>> class Base(object): >>>> id = Column(Integer, primary_key=True) >>>> __table_args__ = {'mysql_engine': 'InnoDB'} >> >>>> Base = declarative_base(cls=Base) >> >>>> class Event(Base): >>>> .... >> >>>> Avalanche_Event_Association = Table('Avalanche_Event_Association', >>>> Base.metadata, >>>> Column('avalanche_id', Integer, ForeignKey('Avalanche.id')), >>>> Column('event_id', Integer, ForeignKey('Event.id')), >>>> mysql_engine='InnoDB') >> >>>> class Avalanche(Base): >>>> .... >> >>>> Doing Base.metadata.create_all(engine) yields: >> >>>> OperationalError: (OperationalError) (1005, "Can't create table >>>> 'alstottj.Avalanche_Event_Association' (errno: 150)") '\nCREATE TABLE >>>> `Avalanche_Event_Association` (\n\tavalanche_id INTEGER, \n\tevent_id >>>> INTEGER, \n\tFOREIGN KEY(avalanche_id) REFERENCES `Avalanche` (id), \n >>>> \tFOREIGN KEY(event_id) REFERENCES `Event` (id)\n)ENGINE=InnoDB\n >>>> \n' () >> >>>> Commenting out the line "mysql_engine='InnoDB'" removes the error and >>>> the tables are all created, but the association table is now MyISAM. >> >>>> I have some feelings on what could be causing the error, but they all >>>> seem improbable. Thoughts? >> >>>> -- >>>> You received this message because you are subscribed to the Google Groups >>>> "sqlalchemy" group. >>>> To post to this group, send email to sqlalchemy@googlegroups.com. >>>> To unsubscribe from this group, send email to >>>> sqlalchemy+unsubscr...@googlegroups.com. >>>> For more options, visit this group >>>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@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. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.