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 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
-~----------~----~----~----~------~----~------~--~---
