[sqlalchemy] Re: Can't make an association table use InnoDB
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.
[sqlalchemy] Re: Can't make an association table use InnoDB
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.
Re: [sqlalchemy] Re: Can't make an association table use InnoDB
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.
[sqlalchemy] Re: Can't make an association table use InnoDB
Thanks! I don't quite follow the statement about fully mapped
association table being unusual. 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
Re: [sqlalchemy] Re: Can't make an association table use InnoDB
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,
[sqlalchemy] Re: Can't make an association table use InnoDB
Well, one of the worst things that can happen in programming has
happened: It now works, and I don't know why _ I didn't change
anything that I know of, and I definitely didn't change the
capitalization. Guess I'll just slowly back away from the machine and
hope everything stays that way.
Thanks for the tip on capitalization, though. Good to know!
On May 31, 3:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
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.
test.py
1KViewDownload
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-...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,
