This worked, btw.
Thanks!
On Wednesday, June 1, 2016 at 5:45:45 PM UTC-5, Horcle wrote:
>
> Ha! Yes, I should not have taken this literally. Will try tomorrow and let
> you know the outcome.
>
> Thanks!
>
> On Wednesday, June 1, 2016 at 3:55:35 PM UTC-5, Mike Bayer wrote:
>>
>> there's no "eager" strategy. you'd want something like lazy="joined" or
>> something like that. check the docs.
>>
>>
>>
>> On 06/01/2016 04:19 PM, Horcle wrote:
>> > Hi Mike,
>> > Just verifying:
>> >
>> > Should
>> >
>> > class Patient(Model):
>> > encounters = relationship(
>> > "Encounter",
>> > backref_populates='patient',
>> > lazy='dynamic')
>> >
>> > be
>> >
>> > class Patient(Model):
>> > encounters = relationship(
>> > "Encounter",
>> > back_populates='patient',
>> > lazy='dynamic')
>> >
>> >
>> > If this is true, I am getting an error that
>> >
>> > Exception: can't locate strategy for <class
>> > 'sqlalchemy.orm.relationships.RelationshipProperty'> (('lazy',
>> 'eager'),)
>> >
>> > Btw, we rebuilt the database using patient_id as a primary key.
>> >
>> > Thanks for your help!
>> >
>> > Greg--
>> >
>> > On Tuesday, May 31, 2016 at 3:49:29 PM UTC-5, Mike Bayer wrote:
>> >
>> > 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 sqlalchemy+...@googlegroups.com <javascript:>
>> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
>> > > To post to this group, send email to sqlal...@googlegroups.com
>> > <javascript:>
>> > > <mailto:sqlal...@googlegroups.com <javascript:>>.
>> > > Visit this group at https://groups.google.com/group/sqlalchemy
>> > <https://groups.google.com/group/sqlalchemy>.
>> > > For more options, visit https://groups.google.com/d/optout
>> > <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 sqlalchemy+...@googlegroups.com
>> > <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> > To post to this group, send email to sqlal...@googlegroups.com
>> > <mailto:sqlal...@googlegroups.com>.
>> > 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
