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