On May 31, 2012, at 3:49 PM, Jeff wrote:
> Thanks! I don't quite follow the statement about "fully mapped
> association table" being unusual.
your name "Avalanche_Event_Association" with CamelCase made me think it was
mapped class, but this is not the case as you have it as a Table.
the problem might be those uppercase names you're using in your ForeignKey
declarations, as your MySQL may or may not actually be case sensitive. The
attached script works for me on OSX, however MySQLs case sensitivity is
platform-dependent. Keep all the tablenames totally lower case with MySQL as
its a nightmare with case sensitivity. Note SQLAlchemy treats names that
aren't all lower case as case sensitive.
> The first Many-to-Many example you
> linked was the structure I copied when making my own tables here. Have
> I deviated from it in some way? Or should the example on the site have
> viewonly=True, if being used with InnoDB? Perhaps I just wasn't being
> clear in my reproducing them here. Just once again now, with the
> additional relevant bits in:
>
> 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):
> events = relationship("Event",
> secondary=Avalanche_Event_Association)
> ....
>
> Doing Base.metadata.create_all(engine) yields an error creating the
> Avalanche_Event_Association table.
>
> On May 31, 3:28 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> 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.
>>
>> Seehttp://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#many-to-...andhttp://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#associat...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
>>> 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.
>
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
class Base(object):
id = Column(Integer, primary_key=True)
__table_args__ = {'mysql_engine': 'InnoDB'}
Base = declarative_base(cls=Base)
class Event(Base):
__tablename__ = 'Event'
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):
__tablename__ = 'Avalanche'
events = relationship("Event", secondary=Avalanche_Event_Association)
engine = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(engine)--
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.
