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!

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

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

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

[Mike Bayer]

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+...@googlegroups.com
> <mailto:sqlalchemy+...@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.

[Greg Silverman]

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

Thanks! 

Greg--

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/fpuqyoPLfuc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Greg M. Silverman

Senior Developer/Informatician

Cardiovascular Informatics 

University of Minnesota

612-626-0919  

g...@umn.edu

 ›  flora-script ‹

 ›  grenzi.org  ‹

 ›  evaluate-it.org  ‹