On Oct 14, 2011, at 9:45 PM, Mark Erbaugh wrote:
> There are two tables pump and curve. The curve table has three fields,
> curve_pn, head and gpm. The design is that the rows with the same curve_pn
> value represent x,y points (head,gpm) on a pump performance curve. Each row
> in the pump table has a curve_pn column that links to the performance curve
> for that pump. The same performance curve can apply to multiple pumps.
>
> To me it seems that there is a many-many relationship, yet there is no
> association table. This design works fine in straight SQL. To model it in
> SQLAlchemy, do I need to add an association table? For the purposes of this
> application, the data is read-only, but if it were not, if the data for a
> curve were to change, I would want it to change for all the pumps that use
> that curve_pn.
SQLAlchemy's rules are more relaxed than relational database rules here, which
would definitely require that you use proper foreign keys. In SQLA's case it
populates local to remote from A->B as the configuration tells it to, does a
join on lookup, and primaryjoin/foreign_keys does what you need:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
bs_ = relationship("B", primaryjoin="A.data==B.data", foreign_keys="B.data")
def __repr__(self):
return "A(data=%r)" % self.data
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
data = Column(String)
def __repr__(self):
return "B(data=%r)" % self.data
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)
b1, b2, b3, b4, b5 = B(), B(), B(), B(), B()
s.add_all([
A(data="a", bs_=[b1, b2, b3]),
A(data="a", bs_=[b1, b2, b3]),
A(data="b", bs_=[b4, b5]),
A(data="b", bs_=[b4, b5]),
])
s.commit()
aa1, aa2, ab1, ab2 = s.query(A).order_by(A.id).options(eagerload(A.bs_)).all()
print aa1.bs_
print aa2.bs_
print ab1.bs_
print ab2.bs_
--
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.
