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 <mike...@zzzcomputing.com> 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 >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto: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 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+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. > -- Greg M. Silverman Senior Developer/Informatician Cardiovascular Informatics <http://www.med.umn.edu/cardiology/> University of Minnesota 612-626-0919 g...@umn.edu › flora-script <http://flora-script.grenzi.org/> ‹ › grenzi.org ‹ › evaluate-it.org ‹ -- 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.