Odtnhj

I am trying to insert data to my postgres table after reading a xlsx file.
I need to validate the xlsx sheet data before I insert it into my table.

I am working on pandas dataframe df = pd.read_excel('/Users/ankitg3-mac/Downloads/medical_plans/%s' % filename)

I am using sqlalchemy as my ORM tool.

My model class:

 class MedicalPlan(Base):
 __tablename__ = "medical_plans"

 id = Column(Integer, nullable=False , primary_key=True)
 issuer_id = Column(Integer, ForeignKey('issuers.id'), nullable=False)
 service_area_id = Column(Integer)
 name = Column(String)
 on_exchange = Column(Boolean)
 off_exchange = Column(Boolean)
 starting_percentage_fpl = Column(REAL, nullable=False , default=0)
 ending_percentage_fpl = Column(REAL, nullable=False, default=0)
 metal_level_name = Column(String)
 network_type = Column(String)
 type = Column(String)
 is_age_29_plan = Column(Boolean)
 original_medicare = Column(Boolean)
 default_bhp = Column(Boolean, default=False)
 sort_rank_override = Column(Integer)
 recommended = Column(Boolean, default=False)
 comparable_individual_plan_id_trash = Column(Integer)
 group_or_individual_plan_type = Column(String)
 hios_plan_identifier = Column(String)

I am doing a bulk insert using a list of dictionaries.

conn.execute(MedicalPlan.__table__.insert(), medicalPlan_dict)

My medicalPlan_dict looks like below:

[u'default_bhp': False, u'price_period': u'Monthly', u'plan_description': '', u'sbc_download_url': '', u'price_note': '', u'starting_percentage_fpl': 0, u'is_uhc_plan': False, 'issuer_id': 440, u'part_b_deductible': '', u'promotional_label': '', u'metal_level_name': u'Silver', u'network_url': '', u'group_or_individual_plan_type': u'Group', u'treatment_cost_calculator_url': '', u'hios_plan_identifier': u'99844RI1800001', u'original_medicare': False, u'part_d_prescription_coverage': '', u'recommended': False, u'off_exchange': True, u'is_age_29_plan': False, u'type': u'MetalPlan', u'ending_percentage_fpl': 0, u'plan_detail_footer': '', u'formulary_url': '', u'plan_detail_items': '', u'highlight_6': '', u'highlight_4': '', u'highlight_5': '', u'hsa_eligible': False, u'highlight_3': u'PCP 20% coinsurance', u'highlight_1': u'Silver', u'name': u'WI 80 INDEMNITY 18 OPTION 1 SILVER RX $10/45/90/25%', u'network_description': '', u'plan_detail_header': '', 'service_area_id': 1, u'data_sourced_from': u'uhc', u'plan_year': 2018, u'highlight_2': u'Indemnity', u'on_exchange': False, u'network_type': u'Indemnity', u'default_bhp': False, u'price_period': u'Monthly', u'plan_description': '', u'sbc_download_url': '', u'price_note': '', u'starting_percentage_fpl': 0, u'is_uhc_plan': False, 'issuer_id': 484, u'part_b_deductible': '', u'promotional_label': '', u'metal_level_name': u'Silver', u'network_url': '', u'group_or_individual_plan_type': u'Group', u'treatment_cost_calculator_url': '', u'hios_plan_identifier': u'99806CAAUSJ-TMP1', u'original_medicare': False, u'part_d_prescription_coverage': '', u'recommended': False, u'off_exchange': True, u'is_age_29_plan': False, u'type': u'MetalPlan', u'ending_percentage_fpl': 0, u'plan_detail_footer': '', u'formulary_url': '', u'plan_detail_items': '', u'highlight_6': '', u'highlight_4': '', u'highlight_5': '', u'hsa_eligible': False, u'highlight_3': u'PCP 20% coinsurance', u'highlight_1': u'Silver', u'name': u'WI 80 INDEMNITY 18 OPTION 1 SILVER RX $10/45/90/25%', u'network_description': '', u'plan_detail_header': '', 'service_area_id': 1, u'data_sourced_from': u'uhc', u'plan_year': 2018, u'highlight_2': u'Indemnity', u'on_exchange': False, u'network_type': u'Indemnity', u'default_bhp': False, u'price_period': u'Monthly', u'plan_description': '', u'sbc_download_url': '', u'price_note': '', u'starting_percentage_fpl': 0, u'is_uhc_plan': False, 'issuer_id': 440, u'part_b_deductible': '', u'promotional_label': '', u'metal_level_name': u'Silver', u'network_url': '', u'group_or_individual_plan_type': u'Group', u'treatment_cost_calculator_url': '', u'hios_plan_identifier': u'99844RI1800002', u'original_medicare': False, u'part_d_prescription_coverage': '', u'recommended': False, u'off_exchange': True, u'is_age_29_plan': False, u'type': u'MetalPlan', u'ending_percentage_fpl': 0, u'plan_detail_footer': '', u'formulary_url': '', u'plan_detail_items': '', u'highlight_6': '', u'highlight_4': '', u'highlight_5': '', u'hsa_eligible': False, u'highlight_3': u'PCP 20% coinsurance', u'highlight_1': u'Silver', u'name': u'WI 80 INDEMNITY 18 OPTION 1 SILVER RX $10/45/90/25%', u'network_description': '', u'plan_detail_header': '', 'service_area_id': 1, u'data_sourced_from': u'uhc', u'plan_year': 2018, u'highlight_2': u'Indemnity', u'on_exchange': False, u'network_type': u'Indemnity']

I need to validate the data before I insert it into my table.
I read about sqlalchemy validations, and tried something like below, assuming that it will take care of the validations at the model level.

@validates('hios_plan_identifier')
 def validate_hios_plan_identifier(self, key, hios_plan_identifier):
 assert '/A(d5[A-Z]2[a-zA-Z0-9]3,7-TMP|d5[A-Z]2d3,7(-?d2)*)z/,' in hios_plan_identifier
 return hios_plan_identifier

I need to put few validations on each variable. Only the rows which passes should be inserted.

I am not sure how to work on validations at model level. How can I achieve this.

Two options:

• Add a CheckConstraint on the column and add your regex there, see
  https://www.postgresql.org/docs/9.3/functions-matching.html . That works in whatever way you throw the data into the database.


• Use event based validation as demonstrated here , create a list of objects you want to insert and use session.add_all() for batch insert.