It's not a basic question at all as this is a rare edge case, and it's not a
"foreign key" by definition. You need to relate the two tables based on a SQL
function, in this case a concatenation. In some cases this can be tricky, and
there's improvements in 0.8 to address that, though in this case it seems to
work without too much difficulty:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class A(Base):
__tablename__ = "a"
id1 = Column(String, primary_key=True)
id2 = Column(String, primary_key=True)
bs = relationship("B",
primaryjoin="B.a_id == A.id1 + A.id2",
foreign_keys="B.a_id",
viewonly=True)
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(String)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(id1="x", id2="y", bs=[
B(a_id="xy"),
B(a_id="xy")
]),
A(id1="q", id2="p", bs=[
B(a_id="qp")
])
])
s.commit()
print s.query(A).first().bs
for a in s.query(A).options(joinedload(A.bs)):
print a.bs
On Jun 16, 2012, at 10:45 AM, Thierry Florac wrote:
>
> Hi,
>
> I have a (probably) quite basic foreign key problem.
>
> My parent table has a composite primary key based on two (fixed width)
> string attributes; in my child tables, this key is stored in a single
> attribute which stores the concatenation of the two parent key
> attributes.
>
> So, is there any easy way to build an SQLAlchemy relation between these
> two tables ?
>
> Best regards,
> Thierry
>
> --
> 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.
