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

2016-06-06 Thread 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

2016-06-06 Thread 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] dealing with NULLS in 1-many relationships

2016-06-06 Thread 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.