On Thursday, January 3, 2013 3:13:58 PM UTC-6, Michael Bayer wrote:
>
>
> On Jan 3, 2013, at 4:01 PM, Chuck Bearden wrote:
>
> Python 2.7.2
> SQLAlchemy 0.8.0b2
> Ubuntu 11.10
>
> Consider the script below. The idea is that I have a medical record, and I
> want to store diagnoses of two kinds: referring diagnoses and
> post-evaluation diagnoses. I want to store them in a single table (this
> constraint is given by the project I inherited). By means of the
> relationships & association proxies defined below, I can correctly store
> diagnoses of both sorts. However, I am unable to retrieve only the
> diagnoses of one sort or the other by means of the proxy. Is there a way to
> incorporate a filter expression in a relationship/association_proxy to
> enable retrieval of only certain values from the proxied table? Or are
> there other techniques (short of creating separate tables for referring &
> post-evaluation diagnoses)?
>
> Thanks for any suggestions!
>
>
>
> the ultimate relationship() that's being proxied needs to filter on the
> "dxtype" attribute using a custom primaryjoin condition. See
> http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#specifying-alternate-join-conditionsfor
> an example of how this looks.
>
Perfect! Thank you for the pointer.
Chuck
#----- script ------#
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import (
Column,
Integer,
ForeignKey,
String,
)
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import relationship
Base = declarative_base()
class MedicalRecord(Base):
__tablename__ = 'medicalrecord'
id = Column(Integer, primary_key=True)
patientname = Column(String(80))
# Use the Diagnoses table with dxtype='referring'
rel_referring_diagnoses = relationship(
"Diagnoses",
cascade="all,delete-orphan"
)
referring_diagnoses = association_proxy(
'rel_referring_diagnoses',
'diagnosis',
creator=lambda dx: Diagnoses(diagnosis=dx, dxtype='referring')
)
# Use the Diagnoses table with dxtype='posteval'
rel_posteval_diagnoses = relationship(
"Diagnoses",
cascade="all,delete-orphan"
)
posteval_diagnoses = association_proxy(
'rel_posteval_diagnoses',
'diagnosis',
creator=lambda dx: Diagnoses(diagnosis=dx, dxtype='posteval')
)
class Diagnoses(Base):
__tablename__ = 'diagnoses'
id = Column(Integer, primary_key=True)
patient_id = Column(Integer, ForeignKey('medicalrecord.id', \
ondelete='cascade'))
diagnosis = Column(String(80))
dxtype = Column(String(40))
if __name__ == '__main__':
engine = create_engine('postgresql://foo:bar@localhost/baz')
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
new_record = MedicalRecord()
new_record.patientname = 'Fred'
session.add(new_record)
new_record.referring_diagnoses.append('runny nose')
session.commit()
# This will print 'runny nose'; I want it to print nothing
print new_record.posteval_diagnoses
# Database has:
# baz=> select * from diagnoses;
# id | patient_id | diagnosis | dxtype
# ----+------------+------------+-----------
# 1 | 1 | runny nose | referring
# (1 row)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/WEfhR1WcGosJ.
To post to this group, send email to [email protected] <javascript:>
.
To unsubscribe from this group, send email to
[email protected] <javascript:>.
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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/aBICNiOX-yYJ.
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.
