you should use "dynamic" and your relationships are mis-configured with
mis-use of the backref() construct, as you've constructed relationships
on both sides that are mutual you'd use back_populates:
class Encounter(Model):
patient = relationship(
"Patient",
back_populates='encounters',
lazy='eager')
class Patent(Model):
encounters = relationship(
"Encounter",
backref_populates='patient',
lazy='dynamic')
Also the foreign key from Encounter.patient_id to Patient.patient_id
does not illustrate a unique index in use and will not be accepted by
all database backends as a real constraint unless one is added (and
regardless, performance will suffer without an index on this column).
Typically, Encounter.patient_id would refer to the primary key of
Patient which is Patient.id.
On 05/31/2016 04:38 PM, Horcle wrote:
I guess my question is: How can I efficiently load Patient and its
related Encounters? I have tried various loading strategies of dynamic,
joined (I would think this would be the desired option), subquery, no
load, etc., and it these do not load. On the other hand, I can load
Encounter just fine and access an instance of the Patient object.
On Tuesday, May 31, 2016 at 11:20:37 AM UTC-5, Horcle wrote:
I have the following two models:
|
ClassEncounter(Model):
__tablename__ ='cp_service'
id =Column(Integer,primary_key=True,autoincrement=True)
master_service_id =Column(String(255))
admission_datetime =Column(DateTime)
admission_provider_id =Column(String(255))
admission_status =Column(String(255))
age_at_visit =Column(String(255))
attending_provider_id =Column(String(255))
center =Column(String(255))
department_name =Column(String(255))
discharge_datetime =Column(DateTime)
encounter_category =Column(String(255))
encounter_class =Column(String(255))
encounter_date =Column(DateTime)
encounter_setting =Column(String(255))
encounter_type =Column(String(255))
primary_care_provider_id =Column(String(255))
primary_service_yn =Column(String(255))
provider_id =Column(String(255))
serv_area_id_orig =Column(String(255))
service_id =Column(Integer)
patient_id =Column(Integer,ForeignKey('cp_patient.patient_id'))
patient
=relationship("Patient",backref=backref("patientEncounter"),lazy='eager',primaryjoin="Patient.patient_id==Encounter.patient_id")
|
|
classPatient(Model):
__tablename__ ='cp_patient'
id =Column(Integer,primary_key=True,autoincrement=True)
first_name =Column(String(100))
middle_name =Column(String(80))
last_name =Column(String(80))
sex =Column(String(80))
race =Column(String(80))
birth_date =Column(DateTime)
ethnicity =Column(String(80))
race =Column(String(80))
patient_id =Column(Integer)
mrn =Column(String(80))
title =Column(String(80))
suffix =Column(String(80))
name_alias =Column(String(80))
addr_1 =Column(String(80))
addr_2 =Column(String(80))
addr_3 =Column(String(80))
city =Column(String(80))
state =Column(String(80))
country =Column(String(80))
zip =Column(String(80))
encounter
=relationship("Encounter",backref=backref("Patient"),lazy='dynamic',primaryjoin="Patient.patient_id==Encounter.patient_id")
|
Where one patient can have many encounters.
I am able to load the patent model fine in a scaffolded list when I
comment out the encounter relationship in the last line (there are
roughly 35K patients), but when I link it to Encounters (1.7
million), then I am running into major performance issues. I tried
using lazy='dynamic', but got an error that:
|
Onrelationship Encounter.patient,'dynamic'loaders cannot be used
withmany-to-one/one-to-one relationships and/oruselist=False.
|
What other options are there available to not load all the relations
until needed?
Thanks!
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
