def populate_db(): config = ConfigParser.ConfigParser() config.read(local_settings_path) db_url = config.get('app:main', 'sqlalchemy.url') from sqlalchemy import create_engine engine = create_engine(db_url) from ..scripts.initializedb import populate_db as populate populate(engine, drop_all=True) shape = MultiPolygon([ Polygon([(0, 0), (0, 1), (1, 1), (1, 0), (0, 0)]) ]) geometry = from_shape(shape, 4326) div_level_1 = AdministrativeDivision(**{ 'code': 10, 'leveltype_id': 1, 'name': u'Division level 1' }) div_level_1.geom = geometry DBSession.add(div_level_1) div_level_2 = AdministrativeDivision(**{ 'code': 20, 'leveltype_id': 2, 'name': u'Division level 2' }) div_level_2.parent_code = div_level_1.code div_level_2.geom = geometry DBSession.add(div_level_2) shape = MultiPolygon([ Polygon([(0, 0), (0, 1), (.5, 1), (.5, 0), (0, 0)]) ]) geometry = from_shape(shape, 4326) div_level_3_1 = AdministrativeDivision(**{ 'code': 30, 'leveltype_id': 3, 'name': u'Division level 3 - 1' }) div_level_3_1.parent_code = div_level_2.code div_level_3_1.geom = geometry div_level_3_1.hazardcategories = [] shape = MultiPolygon([ Polygon([(.5, 0), (.5, 1), (1, 1), (1, 0), (.5, 0)]) ]) geometry = from_shape(shape, 4326) div_level_3_2 = AdministrativeDivision(**{ 'code': 31, 'leveltype_id': 3, 'name': u'Division level 3 - 2' }) div_level_3_2.parent_code = div_level_2.code div_level_3_2.geom = geometry div_level_3_2.hazardcategories = [] category_eq_hig = HazardCategory(**{ 'general_recommendation': u'General recommendation for EQ HIG', }) category_eq_hig.hazardtype = DBSession.query(HazardType) \ .filter(HazardType.mnemonic == u'EQ').one() category_eq_hig.hazardlevel = DBSession.query(HazardLevel) \ .filter(HazardLevel.mnemonic == u'HIG').one() associate_admindiv_hazardcategory(div_level_3_1, category_eq_hig, u'test') associate_admindiv_hazardcategory(div_level_3_2, category_eq_hig, u'test') climate_rec = ClimateChangeRecommendation() climate_rec.text = u'Climate change recommendation' climate_rec.administrativedivision = div_level_1 climate_rec.hazardtype = DBSession.query(HazardType) \ .filter(HazardType.mnemonic == u'EQ').one() DBSession.add(climate_rec) technical_rec = TechnicalRecommendation(**{ 'text': u'Recommendation #1 for earthquake, applied to' ' hazard categories HIG, MED and LOW' }) association = HazardCategoryTechnicalRecommendationAssociation(order=1) association.hazardcategory = category_eq_hig technical_rec.hazardcategory_associations.append(association) DBSession.add(technical_rec) technical_rec = TechnicalRecommendation(**{ 'text': u'Educational web resources on earthquakes and' ' seismic hazard' }) association = HazardCategoryTechnicalRecommendationAssociation(order=1) association.hazardcategory = category_eq_hig technical_rec.hazardcategory_associations.append(association) DBSession.add(technical_rec) category_fl_med = HazardCategory(**{ 'general_recommendation': u'General recommendation for FL MED', }) category_fl_med.hazardtype = DBSession.query(HazardType) \ .filter(HazardType.mnemonic == u'FL').one() category_fl_med.hazardlevel = DBSession.query(HazardLevel) \ .filter(HazardLevel.mnemonic == u'MED').one() associate_admindiv_hazardcategory(div_level_3_1, category_fl_med, u'test') DBSession.add(div_level_3_1) associate_admindiv_hazardcategory(div_level_3_2, category_fl_med, u'test') DBSession.add(div_level_3_2) further_resource = FurtherResource(**{ 'text': u'Educational web resources on earthquakes and' + ' seismic hazard', 'url': u'http://earthquake.usgs.gov/learn/?source=sitemap' }) association = HazardCategoryFurtherResourceAssociation(order=1) association.hazardcategory = category_eq_hig further_resource.hazardcategory_associations.append(association) DBSession.add(further_resource) # Add further resource for one division only further_resource = FurtherResource(**{ 'text': u'Further resource specific to division level 3 - 2', 'url': u'http://domain.com/the/document/url.txt' }) association = HazardCategoryFurtherResourceAssociation(order=2) association.hazardcategory = category_eq_hig association.administrativedivision = div_level_3_2 further_resource.hazardcategory_associations.append(association) DBSession.add(further_resource)
def import_recommendations(): engine = engine_from_config(settings, 'sqlalchemy.') DBSession.configure(bind=engine) with transaction.manager: DBSession.query(HazardCategoryTechnicalRecommendationAssociation) \ .delete() DBSession.query(TechnicalRecommendation).delete() # First load general recommendations with open('data/general_recommendations.csv', 'rb') as csvfile: recommendations = csv.reader(csvfile, delimiter=',') for row in recommendations: hazardcategory = DBSession.query(HazardCategory) \ .join(HazardLevel) \ .join(HazardType) \ .filter(HazardLevel.mnemonic == row[1]) \ .filter(HazardType.mnemonic == row[0]) \ .one() hazardcategory.general_recommendation = row[2] DBSession.add(hazardcategory) categories = [] for type in [u'EQ', u'FL', u'CY', u'TS', u'CF', u'VA', u'DG']: for level in [u'HIG', u'MED', u'LOW', u'VLO']: hazardcategory = DBSession.query(HazardCategory) \ .join(HazardLevel) \ .join(HazardType) \ .filter(HazardLevel.mnemonic == level) \ .filter(HazardType.mnemonic == type) \ .one() categories.append(hazardcategory) # Then technical recommendations hctra = HazardCategoryTechnicalRecommendationAssociation with open('data/technical_recommendations.csv', 'rb') as csvfile: recommendations = csv.reader(csvfile, delimiter=',') next(recommendations, None) # skip the headers for row in recommendations: technical_rec = TechnicalRecommendation(**{ 'text': row[0] }) associations = technical_rec.hazardcategory_associations # the other columns are hazard category (type / level) for col_index in range(1, 28): value = row[col_index] if value is not '' and value is not 'Y': association = hctra(order=value) association.hazardcategory = categories[col_index - 1] associations.append(association) DBSession.add(technical_rec) # Climate change recommendations DBSession.query(ClimateChangeRecommendation).delete() # hazard types and corresponding columns hazard_types = [ (u'FL', 6), (u'EQ', 7), (u'CY', 8), (u'CF', 9), (u'DG', 10), (u'TS', 11), (u'VA', 12), (u'LS', 13), ] with open('data/climate_change_recommendations.csv', 'rb') as csvfile: countries = csv.reader(csvfile, delimiter=',') next(countries, None) # skip the headers for row in countries: division = DBSession.query(AdministrativeDivision) \ .filter(AdministrativeDivision.code == row[1]) \ .one_or_none() if not division: continue for hazard_type, column in hazard_types: text = row[column] if text == 'NA': continue climate_rec = ClimateChangeRecommendation() climate_rec.text = row[column] climate_rec.administrativedivision = division climate_rec.hazardtype = DBSession.query(HazardType) \ .filter(HazardType.mnemonic == hazard_type).one() DBSession.add(climate_rec)