use inherit_condition:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import String, Column, Integer, ForeignKey
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('mysql://scott:ti...@localhost/test', echo=True)
Base = declarative_base()
# B is the superclas of S
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
type = Column(String(1))
__mapper_args__ = {'polymorphic_on': type,
'polymorphic_identity':'b'}
__table_args__ = {'mysql_engine':'InnoDB'}
# S is a subclass of B and also references an object of the B
hierarchy via a foreign key
class S(B):
__tablename__ = 's'
id = Column(Integer, ForeignKey('b.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('b.id'))
b = relation('B', backref='s_collection',
primaryjoin='S.b_id==B.id')
__table_args__ = {'mysql_engine':'InnoDB'}
__mapper_args__ = {'polymorphic_identity': 's',
'inherit_condition':id==B.id}
Base.metadata.create_all(engine)
On May 23, 2009, at 9:51 AM, sven-eric wrote:
>
> Dear Group,
>
> I have a problem with setting up self referential relations within a
> joined table inheritance scheme and declarative mapping. Let's say I
> have a base class B with a derived class S. S has a self-referential
> many-to-one relationship to B (and with that also to all of B's
> derived classes). The declarative definition seems to compile fine if
> a database system without native support for foreign keys is used
> (like sqlite), but breaks down with systems like InnoDB on mysql where
> foreign keys are supported natively (in the latter case I get a
> "tables have more than one foreign key constraint relationship between
> them. Please specify the 'onclause' of this join explicitly" error).
>
> So, the following testcase works on sqlite but fails if I move to SQL
> and InnoDB (after setting SQL server in the engine declaration and the
> InnoDB table in the __table_args__ of each class). Could anyone give
> me advice for setting up this kind of relation on InnoDB? Thanks a
> lot.
>
> -sven-eric
>
> ----------------
>
> import sqlalchemy
> from sqlalchemy import create_engine
> from sqlalchemy import String, Column, Integer, ForeignKey,
> ForeignKeyConstraint
> from sqlalchemy.orm import relation, backref
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = create_engine('sqlite:///:memory:', echo=False)
> Base = declarative_base()
>
> # B is the superclas of S
> class B(Base):
> __tablename__ = 'b'
> id = Column(Integer, primary_key=True)
> type = Column(String(1))
> __mapper_args__ = {'polymorphic_on': type, 'polymorphic_identity':
> 'b'}
>
> # S is a subclass of B and also references an object of the B
> hierarchy via a foreign key
> class S(B):
> __tablename__ = 's'
> __table_args__ = (ForeignKeyConstraint(['b_id'], ['b.id']))
> __mapper_args__ = {'polymorphic_identity': 's'}
> id = Column(Integer, ForeignKey('b.id'), primary_key=True)
> b_id = Column(Integer)
> b = relation('B', backref='s_collection', primaryjoin='S.b_id==B.id')
>
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
