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! 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 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.