Re: [sqlalchemy] dealing with NULLS in 1-many relationships

[Greg Silverman]

Unfortunately, the data are out of our control. However, this solution
looks like it will do the job.

Thanks!

Greg--

On Mon, Jun 6, 2016 at 5:54 PM, Mike Bayer  wrote:

>
>
> On 06/06/2016 11:21 AM, Horcle wrote:
>
>> I have the following models:
>>
>> class LabResult(Model):
>> __tablename__ = 'cp_svc_lab_result'
>> id = Column(Integer, primary_key=True, autoincrement=True)
>> test_code = Column(String(255))
>> test_code_system = Column(String(255))
>> test_name = Column(String(255))
>> test_name_orig = Column(String(255))
>> proc_name = Column(String(255))
>> proc_code = Column(String(255))
>> proc_code_modifier = Column(String(255))
>> proc_code_system = Column(String(255))
>> result_value = Column(String(255))
>> result_value_num = Column(String(255))
>> result_value_num_orig = Column(String(255))
>> result_unit = Column(String(255))
>> result_unit_orig = Column(String(255))
>> ref_normal_min = Column(String(255))
>> ref_normal_max = Column(String(255))
>> result_characterization = Column(String(255))
>> collection_datetime = Column(DateTime)
>> result_datetime = Column(DateTime)
>> abnormal_flag = Column(String(255))
>> lab_status = Column(String(255))
>> result_comment = Column(UnicodeText)
>> component_comment = Column(UnicodeText)
>> order_id = Column(String(255))
>> order_num = Column(String(255))
>> order_priority = Column(String(255))
>> order_result_id = Column(String(255))
>> order_reviewed = Column(String(255))
>> order_type_orig = Column(String(255))
>> order_type_orig_id = Column(String(255))
>> result_code_orig = Column(String(255))
>> result_code_orig_system = Column(String(255))
>> result_status = Column(String(255))
>> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
>> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
>> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>>
>> and,
>>
>> class Provider(Model):
>> __tablename__ = 'cp_provider'
>> provider_id = Column(Integer, primary_key=True)
>> authorize_meds_yn = Column(String(80))
>> active_status = Column(String(80))
>> authorize_orders_yn = Column(String(80))
>> birth_date = Column(DateTime)
>> clinician_degree = Column(String(80))
>> clinician_title = Column(String(80))
>> country = Column(String(80))
>> dea_number = Column(String(80))
>> email = Column(String(80))
>> external_name = Column(String(80))
>> provider_e_prescribe_yn = Column(String(80))
>> inpatient_ordering_yn = Column(String(80))
>> name = Column(String(80))
>> npi = Column(String(80))
>> office_fax = Column(String(80))
>> office_phone = Column(String(80))
>> outpatient_ordering_yn = Column(String(80))
>> provider_type = Column(String(80))
>> referral_source_type = Column(String(80))
>> resident_yn = Column(String(80))
>> sex = Column(String(80))
>> surgical_pool_yn = Column(String(80))
>> transcription_user_yn = Column(String(80))
>> upin = Column(String(80))
>> encounter = relationship("EncounterList", backref=backref("Provider"),
>> lazy='dynamic')
>>
>> Where one provider can have multiple LabResults... How do I handle the
>> case when there may be a provider_id in the LabResult table, but not in
>> the Provider table (we are only keeping a subset of the provider list)?
>> I need to access the object Provider so that I can have access to all of
>> its attributes, such as Provider.name, etc. When I try this now, I get
>> an error that "Nonetype has attribute name." Ia there a way to set a
>> default value for when the result is NULL?
>>
>
> In relational database design, provider_id always must refer to a row in
> Provider.  If that's not the case, then your database is failing
> referential integrity and is mis-designed; the definition of a ForeignKey
> is that it's a constraint that indicates a remote primary key that must
> exist.
>
> If you're in some situation where this isn't actually happening and you
> need to work around it, it looks like you're just looking for a string
> "missing" instead of None?  This is just a Python access issue.   Use a
> method like "def get_provider_id()", or a synonym:
>
> class LabResult(Base):
>
> provider_id = Column(Integer)
>
> @synonym_for("_provider_id", map_column=True)
> @property
> def provider_id(self):
>return self._provider_id or "Missing"
>
>
>
>
>
>
>
>> 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+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.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To 

Re: [sqlalchemy] dealing with NULLS in 1-many relationships

[Mike Bayer]

On 06/06/2016 11:21 AM, Horcle wrote:

I have the following models:

class LabResult(Model):
__tablename__ = 'cp_svc_lab_result'
id = Column(Integer, primary_key=True, autoincrement=True)
test_code = Column(String(255))
test_code_system = Column(String(255))
test_name = Column(String(255))
test_name_orig = Column(String(255))
proc_name = Column(String(255))
proc_code = Column(String(255))
proc_code_modifier = Column(String(255))
proc_code_system = Column(String(255))
result_value = Column(String(255))
result_value_num = Column(String(255))
result_value_num_orig = Column(String(255))
result_unit = Column(String(255))
result_unit_orig = Column(String(255))
ref_normal_min = Column(String(255))
ref_normal_max = Column(String(255))
result_characterization = Column(String(255))
collection_datetime = Column(DateTime)
result_datetime = Column(DateTime)
abnormal_flag = Column(String(255))
lab_status = Column(String(255))
result_comment = Column(UnicodeText)
component_comment = Column(UnicodeText)
order_id = Column(String(255))
order_num = Column(String(255))
order_priority = Column(String(255))
order_result_id = Column(String(255))
order_reviewed = Column(String(255))
order_type_orig = Column(String(255))
order_type_orig_id = Column(String(255))
result_code_orig = Column(String(255))
result_code_orig_system = Column(String(255))
result_status = Column(String(255))
patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
service_id = Column(Integer, ForeignKey('cp_service.service_id'))
provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))

and,

class Provider(Model):
__tablename__ = 'cp_provider'
provider_id = Column(Integer, primary_key=True)
authorize_meds_yn = Column(String(80))
active_status = Column(String(80))
authorize_orders_yn = Column(String(80))
birth_date = Column(DateTime)
clinician_degree = Column(String(80))
clinician_title = Column(String(80))
country = Column(String(80))
dea_number = Column(String(80))
email = Column(String(80))
external_name = Column(String(80))
provider_e_prescribe_yn = Column(String(80))
inpatient_ordering_yn = Column(String(80))
name = Column(String(80))
npi = Column(String(80))
office_fax = Column(String(80))
office_phone = Column(String(80))
outpatient_ordering_yn = Column(String(80))
provider_type = Column(String(80))
referral_source_type = Column(String(80))
resident_yn = Column(String(80))
sex = Column(String(80))
surgical_pool_yn = Column(String(80))
transcription_user_yn = Column(String(80))
upin = Column(String(80))
encounter = relationship("EncounterList", backref=backref("Provider"),
lazy='dynamic')

Where one provider can have multiple LabResults... How do I handle the
case when there may be a provider_id in the LabResult table, but not in
the Provider table (we are only keeping a subset of the provider list)?
I need to access the object Provider so that I can have access to all of
its attributes, such as Provider.name, etc. When I try this now, I get
an error that "Nonetype has attribute name." Ia there a way to set a
default value for when the result is NULL?


In relational database design, provider_id always must refer to a row in 
Provider.  If that's not the case, then your database is failing 
referential integrity and is mis-designed; the definition of a 
ForeignKey is that it's a constraint that indicates a remote primary key 
that must exist.


If you're in some situation where this isn't actually happening and you 
need to work around it, it looks like you're just looking for a string 
"missing" instead of None?  This is just a Python access issue.   Use a 
method like "def get_provider_id()", or a synonym:


class LabResult(Base):

provider_id = Column(Integer)

@synonym_for("_provider_id", map_column=True)
@property
def provider_id(self):
   return self._provider_id or "Missing"








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


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

[sqlalchemy] Re: dealing with NULLS in 1-many relationships

[Horcle]

I would ultimately like to do something like:

provider_id = Column(Integer, 
func.coalesce(ForeignKey('cp_provider.provider_id'), 'Missing'), 
nullable=True)

but this is not working... I also tried using coalesce with a primaryjoin 
condition on 

encounter = relationship("EncounterList", backref=backref("Provider"), 
lazy='dynamic')

but neither did this.



On Monday, June 6, 2016 at 10:46:23 AM UTC-5, Horcle wrote:
>
> So I can set it to a default value, such as 'N/A' - something similar to 
> the MSSQL function ISNULL that I can use in the class definition.
>
> On Monday, June 6, 2016 at 10:41:08 AM UTC-5, Horcle wrote:
>>
>> I'm basically looking for something I can add to the backref or 
>> ForeignKey definition for the case that the value of provider_id is None.
>>
>> On Monday, June 6, 2016 at 10:21:39 AM UTC-5, Horcle wrote:
>>>
>>> I have the following models:
>>>
>>> class LabResult(Model):
>>> __tablename__ = 'cp_svc_lab_result'
>>> id = Column(Integer, primary_key=True, autoincrement=True)
>>> test_code = Column(String(255))
>>> test_code_system = Column(String(255))
>>> test_name = Column(String(255))
>>> test_name_orig = Column(String(255))
>>> proc_name = Column(String(255))
>>> proc_code = Column(String(255))
>>> proc_code_modifier = Column(String(255))
>>> proc_code_system = Column(String(255))
>>> result_value = Column(String(255))
>>> result_value_num = Column(String(255))
>>> result_value_num_orig = Column(String(255))
>>> result_unit = Column(String(255))
>>> result_unit_orig = Column(String(255))
>>> ref_normal_min = Column(String(255))
>>> ref_normal_max = Column(String(255))
>>> result_characterization = Column(String(255))
>>> collection_datetime = Column(DateTime)
>>> result_datetime = Column(DateTime)
>>> abnormal_flag = Column(String(255))
>>> lab_status = Column(String(255))
>>> result_comment = Column(UnicodeText)
>>> component_comment = Column(UnicodeText)
>>> order_id = Column(String(255))
>>> order_num = Column(String(255))
>>> order_priority = Column(String(255))
>>> order_result_id = Column(String(255))
>>> order_reviewed = Column(String(255))
>>> order_type_orig = Column(String(255))
>>> order_type_orig_id = Column(String(255))
>>> result_code_orig = Column(String(255))
>>> result_code_orig_system = Column(String(255))
>>> result_status = Column(String(255))
>>> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
>>> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
>>> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>>>
>>> and,
>>>
>>> class Provider(Model):
>>> __tablename__ = 'cp_provider'
>>> provider_id = Column(Integer, primary_key=True)
>>> authorize_meds_yn = Column(String(80))
>>> active_status = Column(String(80))
>>> authorize_orders_yn = Column(String(80))
>>> birth_date = Column(DateTime)
>>> clinician_degree = Column(String(80))
>>> clinician_title = Column(String(80))
>>> country = Column(String(80))
>>> dea_number = Column(String(80))
>>> email = Column(String(80))
>>> external_name = Column(String(80))
>>> provider_e_prescribe_yn = Column(String(80))
>>> inpatient_ordering_yn = Column(String(80))
>>> name = Column(String(80))
>>> npi = Column(String(80))
>>> office_fax = Column(String(80))
>>> office_phone = Column(String(80))
>>> outpatient_ordering_yn = Column(String(80))
>>> provider_type = Column(String(80))
>>> referral_source_type = Column(String(80))
>>> resident_yn = Column(String(80))
>>> sex = Column(String(80))
>>> surgical_pool_yn = Column(String(80))
>>> transcription_user_yn = Column(String(80))
>>> upin = Column(String(80))
>>> encounter = relationship("EncounterList", backref=backref("Provider"), 
>>> lazy='dynamic')
>>>
>>> Where one provider can have multiple LabResults... How do I handle the 
>>> case when there may be a provider_id in the LabResult table, but not in the 
>>> Provider table (we are only keeping a subset of the provider list)? I need 
>>> to access the object Provider so that I can have access to all of its 
>>> attributes, such as Provider.name, etc. When I try this now, I get an error 
>>> that "Nonetype has attribute name." Ia there a way to set a default value 
>>> for when the result is NULL? 
>>>
>>> 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+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.

[sqlalchemy] Re: dealing with NULLS in 1-many relationships

[Horcle]

So I can set it to a default value, such as 'N/A' - something similar to 
the MSSQL function ISNULL that I can use in the class definition.

On Monday, June 6, 2016 at 10:41:08 AM UTC-5, Horcle wrote:
>
> I'm basically looking for something I can add to the backref or ForeignKey 
> definition for the case that the value of provider_id is None.
>
> On Monday, June 6, 2016 at 10:21:39 AM UTC-5, Horcle wrote:
>>
>> I have the following models:
>>
>> class LabResult(Model):
>> __tablename__ = 'cp_svc_lab_result'
>> id = Column(Integer, primary_key=True, autoincrement=True)
>> test_code = Column(String(255))
>> test_code_system = Column(String(255))
>> test_name = Column(String(255))
>> test_name_orig = Column(String(255))
>> proc_name = Column(String(255))
>> proc_code = Column(String(255))
>> proc_code_modifier = Column(String(255))
>> proc_code_system = Column(String(255))
>> result_value = Column(String(255))
>> result_value_num = Column(String(255))
>> result_value_num_orig = Column(String(255))
>> result_unit = Column(String(255))
>> result_unit_orig = Column(String(255))
>> ref_normal_min = Column(String(255))
>> ref_normal_max = Column(String(255))
>> result_characterization = Column(String(255))
>> collection_datetime = Column(DateTime)
>> result_datetime = Column(DateTime)
>> abnormal_flag = Column(String(255))
>> lab_status = Column(String(255))
>> result_comment = Column(UnicodeText)
>> component_comment = Column(UnicodeText)
>> order_id = Column(String(255))
>> order_num = Column(String(255))
>> order_priority = Column(String(255))
>> order_result_id = Column(String(255))
>> order_reviewed = Column(String(255))
>> order_type_orig = Column(String(255))
>> order_type_orig_id = Column(String(255))
>> result_code_orig = Column(String(255))
>> result_code_orig_system = Column(String(255))
>> result_status = Column(String(255))
>> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
>> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
>> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>>
>> and,
>>
>> class Provider(Model):
>> __tablename__ = 'cp_provider'
>> provider_id = Column(Integer, primary_key=True)
>> authorize_meds_yn = Column(String(80))
>> active_status = Column(String(80))
>> authorize_orders_yn = Column(String(80))
>> birth_date = Column(DateTime)
>> clinician_degree = Column(String(80))
>> clinician_title = Column(String(80))
>> country = Column(String(80))
>> dea_number = Column(String(80))
>> email = Column(String(80))
>> external_name = Column(String(80))
>> provider_e_prescribe_yn = Column(String(80))
>> inpatient_ordering_yn = Column(String(80))
>> name = Column(String(80))
>> npi = Column(String(80))
>> office_fax = Column(String(80))
>> office_phone = Column(String(80))
>> outpatient_ordering_yn = Column(String(80))
>> provider_type = Column(String(80))
>> referral_source_type = Column(String(80))
>> resident_yn = Column(String(80))
>> sex = Column(String(80))
>> surgical_pool_yn = Column(String(80))
>> transcription_user_yn = Column(String(80))
>> upin = Column(String(80))
>> encounter = relationship("EncounterList", backref=backref("Provider"), 
>> lazy='dynamic')
>>
>> Where one provider can have multiple LabResults... How do I handle the 
>> case when there may be a provider_id in the LabResult table, but not in the 
>> Provider table (we are only keeping a subset of the provider list)? I need 
>> to access the object Provider so that I can have access to all of its 
>> attributes, such as Provider.name, etc. When I try this now, I get an error 
>> that "Nonetype has attribute name." Ia there a way to set a default value 
>> for when the result is NULL? 
>>
>> 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+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.

[sqlalchemy] Re: dealing with NULLS in 1-many relationships

[Horcle]

I'm basically looking for something I can add to the backref or ForeignKey 
definition for the case that the value of provider_id is None.

On Monday, June 6, 2016 at 10:21:39 AM UTC-5, Horcle wrote:
>
> I have the following models:
>
> class LabResult(Model):
> __tablename__ = 'cp_svc_lab_result'
> id = Column(Integer, primary_key=True, autoincrement=True)
> test_code = Column(String(255))
> test_code_system = Column(String(255))
> test_name = Column(String(255))
> test_name_orig = Column(String(255))
> proc_name = Column(String(255))
> proc_code = Column(String(255))
> proc_code_modifier = Column(String(255))
> proc_code_system = Column(String(255))
> result_value = Column(String(255))
> result_value_num = Column(String(255))
> result_value_num_orig = Column(String(255))
> result_unit = Column(String(255))
> result_unit_orig = Column(String(255))
> ref_normal_min = Column(String(255))
> ref_normal_max = Column(String(255))
> result_characterization = Column(String(255))
> collection_datetime = Column(DateTime)
> result_datetime = Column(DateTime)
> abnormal_flag = Column(String(255))
> lab_status = Column(String(255))
> result_comment = Column(UnicodeText)
> component_comment = Column(UnicodeText)
> order_id = Column(String(255))
> order_num = Column(String(255))
> order_priority = Column(String(255))
> order_result_id = Column(String(255))
> order_reviewed = Column(String(255))
> order_type_orig = Column(String(255))
> order_type_orig_id = Column(String(255))
> result_code_orig = Column(String(255))
> result_code_orig_system = Column(String(255))
> result_status = Column(String(255))
> patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
> service_id = Column(Integer, ForeignKey('cp_service.service_id'))
> provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))
>
> and,
>
> class Provider(Model):
> __tablename__ = 'cp_provider'
> provider_id = Column(Integer, primary_key=True)
> authorize_meds_yn = Column(String(80))
> active_status = Column(String(80))
> authorize_orders_yn = Column(String(80))
> birth_date = Column(DateTime)
> clinician_degree = Column(String(80))
> clinician_title = Column(String(80))
> country = Column(String(80))
> dea_number = Column(String(80))
> email = Column(String(80))
> external_name = Column(String(80))
> provider_e_prescribe_yn = Column(String(80))
> inpatient_ordering_yn = Column(String(80))
> name = Column(String(80))
> npi = Column(String(80))
> office_fax = Column(String(80))
> office_phone = Column(String(80))
> outpatient_ordering_yn = Column(String(80))
> provider_type = Column(String(80))
> referral_source_type = Column(String(80))
> resident_yn = Column(String(80))
> sex = Column(String(80))
> surgical_pool_yn = Column(String(80))
> transcription_user_yn = Column(String(80))
> upin = Column(String(80))
> encounter = relationship("EncounterList", backref=backref("Provider"), 
> lazy='dynamic')
>
> Where one provider can have multiple LabResults... How do I handle the 
> case when there may be a provider_id in the LabResult table, but not in the 
> Provider table (we are only keeping a subset of the provider list)? I need 
> to access the object Provider so that I can have access to all of its 
> attributes, such as Provider.name, etc. When I try this now, I get an error 
> that "Nonetype has attribute name." Ia there a way to set a default value 
> for when the result is NULL? 
>
> 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+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.

Re: [sqlalchemy] Re: Dealing with large collections in a scaffolded application

[Horcle]

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 > > '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 

[sqlalchemy] dealing with NULLS in 1-many relationships

[Horcle]

I have the following models:

class LabResult(Model):
__tablename__ = 'cp_svc_lab_result'
id = Column(Integer, primary_key=True, autoincrement=True)
test_code = Column(String(255))
test_code_system = Column(String(255))
test_name = Column(String(255))
test_name_orig = Column(String(255))
proc_name = Column(String(255))
proc_code = Column(String(255))
proc_code_modifier = Column(String(255))
proc_code_system = Column(String(255))
result_value = Column(String(255))
result_value_num = Column(String(255))
result_value_num_orig = Column(String(255))
result_unit = Column(String(255))
result_unit_orig = Column(String(255))
ref_normal_min = Column(String(255))
ref_normal_max = Column(String(255))
result_characterization = Column(String(255))
collection_datetime = Column(DateTime)
result_datetime = Column(DateTime)
abnormal_flag = Column(String(255))
lab_status = Column(String(255))
result_comment = Column(UnicodeText)
component_comment = Column(UnicodeText)
order_id = Column(String(255))
order_num = Column(String(255))
order_priority = Column(String(255))
order_result_id = Column(String(255))
order_reviewed = Column(String(255))
order_type_orig = Column(String(255))
order_type_orig_id = Column(String(255))
result_code_orig = Column(String(255))
result_code_orig_system = Column(String(255))
result_status = Column(String(255))
patient_id = Column(Integer, ForeignKey('cp_patient.patient_id'))
service_id = Column(Integer, ForeignKey('cp_service.service_id'))
provider_id = Column(Integer, ForeignKey('cp_provider.provider_id'))

and,

class Provider(Model):
__tablename__ = 'cp_provider'
provider_id = Column(Integer, primary_key=True)
authorize_meds_yn = Column(String(80))
active_status = Column(String(80))
authorize_orders_yn = Column(String(80))
birth_date = Column(DateTime)
clinician_degree = Column(String(80))
clinician_title = Column(String(80))
country = Column(String(80))
dea_number = Column(String(80))
email = Column(String(80))
external_name = Column(String(80))
provider_e_prescribe_yn = Column(String(80))
inpatient_ordering_yn = Column(String(80))
name = Column(String(80))
npi = Column(String(80))
office_fax = Column(String(80))
office_phone = Column(String(80))
outpatient_ordering_yn = Column(String(80))
provider_type = Column(String(80))
referral_source_type = Column(String(80))
resident_yn = Column(String(80))
sex = Column(String(80))
surgical_pool_yn = Column(String(80))
transcription_user_yn = Column(String(80))
upin = Column(String(80))
encounter = relationship("EncounterList", backref=backref("Provider"), lazy=
'dynamic')

Where one provider can have multiple LabResults... How do I handle the case 
when there may be a provider_id in the LabResult table, but not in the 
Provider table (we are only keeping a subset of the provider list)? I need 
to access the object Provider so that I can have access to all of its 
attributes, such as Provider.name, etc. When I try this now, I get an error 
that "Nonetype has attribute name." Ia there a way to set a default value 
for when the result is NULL? 

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

[sqlalchemy] How to insert using subquery that return multiple fields?

[Kawing Chiu]

I'm trying to implement the common "insert if not exists" pattern using
sqlalchemy, but after a day of googling can't find out how to do it:

My query is like (sqlite backend):

INSERT INTO memos(id,text) SELECT 5, 'text to insert' WHERE NOT EXISTS(SELECT 1 
FROM memos WHERE id = 5 AND text = 'text to insert');


I know how to create the subquery, but just can't figure out 
how to pass the result of the subquery to insert, especially
 this subquery returns 2 fields.

Can anyone please help? 





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