def read(self, database='project'): """ Read a cons_practice.lum text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 4, 'cons_practice.lum') d = { 'name': val[0].lower(), 'usle_p': val[1], 'slp_len_max': val[2], 'description': val[3] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Cons_prac_lum, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Cons_prac_lum, data)
def read(self, database='project'): """ Read a cntable.lum text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 cns = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 8, 'cntable.lum') cn = { 'name': val[0].lower(), 'cn_a': val[1], 'cn_b': val[2], 'cn_c': val[3], 'cn_d': val[4], 'description': val[5], 'treat': val[6], 'cond_cov': val[7] } cns.append(cn) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Cntable_lum, cns) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Cntable_lum, cns)
def read(self, database='project'): """ Read a ovn_table.lum text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 5, 'ovn_table.lum') d = { 'name': val[0].lower(), 'ovn_mean': val[1], 'ovn_min': val[2], 'ovn_max': val[3], 'description': val[4] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Ovn_table_lum, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Ovn_table_lum, data)
def read(self, database ='project'): """ Read a chem_app.ops text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 9, 'chem_app.ops') d = { 'name': val[0].lower(), 'chem_form': val[1], 'app_typ': val[2], 'app_eff': val[3], 'foliar_eff': val[4], 'inject_dp': val[5], 'surf_frac': val[6], 'drift_pot': val[7], 'aerial_unif': val[8] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Chem_app_ops, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Chem_app_ops, data)
def read(self, database ='project'): """ Read a sweep.ops text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 3, 'sweep.ops') d = { 'name': val[0].lower(), 'swp_eff': val[1], 'frac_curb': val[2] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Sweep_ops, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Sweep_ops, data)
def read(self, database='project'): """ Read a filterstrip.str text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 5, 'filterstrip.str') d = { 'name': val[0].lower(), 'flag': 0, 'fld_vfs': val[1], 'con_vfs': val[2], 'cha_q': val[3], 'description': val[4] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Filterstrip_str, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Filterstrip_str, data)
def read(self, database ='project'): """ Read a harv.ops text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 5, 'harv.ops') d = { 'name': val[0].lower(), 'harv_typ': val[1], 'harv_idx': val[2], 'harv_eff': val[3], 'harv_bm_min': val[4] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Harv_ops, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Harv_ops, data)
def read(self, database='project'): """ Read a tiledrain.str text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 9, 'tiledrain.str') d = { 'name': val[0].lower(), 'dp': val[1], 't_fc': val[2], 'lag': val[3], 'rad': val[4], 'dist': val[5], 'drain': val[6], 'pump': val[7], 'lat_ksat': val[8] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Tiledrain_str, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Tiledrain_str, data)
def read(self, database='project'): """ Read a grassedww.str text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 8, 'grassedww.str') d = { 'name': val[0].lower(), 'flag': 0, 'mann': val[1], 'sed_co': val[2], 'dp': val[3], 'wd': val[4], 'len': val[5], 'slp': val[6], 'description': val[7] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Grassedww_str, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Grassedww_str, data)
def save_constituents_ini(project_db, ini_table, ini_item_table, rel_col_name, row1='plant', row2='soil'): SetupProjectDatabase.init(project_db) parser = reqparse.RequestParser() parser.add_argument('items', type=list, location='json') args = parser.parse_args(strict=False) ini_item_table.delete().execute() ini_table.delete().execute() for item in args['items']: m = ini_table(name=item['name']) m.save() rows = [] for row in item['rows']: rows.append({ rel_col_name: m.id, 'name_id': row['name_id'], row1: row[row1], row2: row[row2] }) db_lib.bulk_insert(project_base.db, ini_item_table, rows)
def read_const_data(self): with open(self.file_name, mode='r') as csv_file: dialect = csv.Sniffer().sniff(csv_file.readline()) csv_file.seek(0) replace_commas = dialect is not None and dialect.delimiter != ',' csv_reader = csv.DictReader(csv_file, dialect=dialect) rows = [] for row in csv_reader: rec = db.Recall_rec.get_or_none( db.Recall_rec.name == row['name']) if rec is not None: db.Recall_rec.update(rec_typ=4).where( db.Recall_rec.id == rec.id).execute() db.Recall_dat.delete().where( db.Recall_dat.recall_rec_id == rec.id).execute() if replace_commas: for key in row: row[key] = row[key].replace(',', '.', 1) row['recall_rec_id'] = rec.id row['yr'] = 0 row['jday'] = 0 row['mo'] = 0 row['day_mo'] = 0 row['ob_typ'] = 'pt_cnst' row['ob_name'] = row['name'] row.pop('name', None) rows.append(row) db_lib.bulk_insert(project_base.db, db.Recall_dat, rows)
def read_data(self, recall_rec_id, delete_existing, rec_typ): with open(self.file_name, mode='r') as csv_file: dialect = csv.Sniffer().sniff(csv_file.readline()) csv_file.seek(0) replace_commas = dialect is not None and dialect.delimiter != ',' csv_reader = csv.DictReader(csv_file) rows = [] """ recTypOptions: [ { value: 1, text: 'Daily' }, { value: 2, text: 'Monthly' }, { value: 3, text: 'Yearly' }, { value: 4, text: 'Constant' } ] """ for row in csv_reader: if replace_commas: for key in row: row[key] = row[key].replace(',', '.', 1) row['recall_rec_id'] = recall_rec_id rows.append(row) if delete_existing: db.Recall_dat.delete().where( db.Recall_dat.recall_rec_id == recall_rec_id).execute() db_lib.bulk_insert(project_base.db, db.Recall_dat, rows) db.Recall_rec.update(rec_typ=rec_typ).where( db.Recall_rec.id == recall_rec_id).execute()
def read(self, database='project'): """ Read a cal_parms.cal text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 values = [] for line in file: if i > 3: val = line.split() self.check_cols(val, 5, 'cal_parms') v = { 'name': val[0].lower(), 'obj_typ': val[1], 'abs_min': val[2], 'abs_max': val[3], 'units': val[4] if val[4] != 'null' else None } values.append(v) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, project_db.Cal_parms_cal, values) else: db_lib.bulk_insert(datasets_base.db, datasets_db.Cal_parms_cal, values)
def read(self, database='project'): """ Read a bmpuser.str text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 8, 'bmpuser.str') d = { 'name': val[0].lower(), 'flag': val[1], 'sed_eff': val[2], 'ptlp_eff': val[3], 'solp_eff': val[4], 'ptln_eff': val[5], 'soln_eff': val[6], 'bact_eff': val[7] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Bmpuser_str, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Bmpuser_str, data)
def put(self, project_db, id): try: SetupProjectDatabase.init(project_db) args = get_mgt_args() m = Management_sch.get(Management_sch.id == id) m.name = args['name'] m.save() new_auto = [] for a in args['auto_ops']: try: dt = D_table_dtl.get((D_table_dtl.file_name == 'lum.dtl') & (D_table_dtl.name == a)) new_auto.append({ 'management_sch_id': m.id, 'd_table_id': dt.id }) except D_table_dtl.DoesNotExist: abort( 404, message='Decision table {name} does not exist'.format( name=a)) new_ops = [] order = 1 for o in args['operations']: new_ops.append({ 'management_sch_id': m.id, 'op_typ': o['op_typ'], 'mon': o['mon'], 'day': o['day'], 'op_data1': o['op_data1'], 'op_data2': o['op_data2'], 'op_data3': o['op_data3'], 'order': o['order'], 'hu_sch': o['hu_sch'] }) order += 1 Management_sch_auto.delete().where( Management_sch_auto.management_sch_id == m.id).execute() lib.bulk_insert(base.db, Management_sch_auto, new_auto) Management_sch_op.delete().where( Management_sch_op.management_sch_id == m.id).execute() lib.bulk_insert(base.db, Management_sch_op, new_ops) return 200 except IntegrityError as e: abort(400, message='Management schedule name must be unique.') except Cons_prac_lum.DoesNotExist: abort(404, message='Management schedule {id} does not exist'.format( id=id)) except Exception as ex: abort(400, message="Unexpected error {ex}".format(ex=ex))
def read(self, database='project'): """ Read a septic.str text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 28, 'septic.str') d = { 'name': val[0].lower(), 'typ': val[1], 'yr': val[2], 'operation': val[3], 'residents': val[4], 'area': val[5], 't_fail': val[6], 'dp_bioz': val[7], 'thk_bioz': val[8], 'cha_dist': val[9], 'sep_dens': val[10], 'bm_dens': val[11], 'bod_decay': val[12], 'bod_conv': val[13], 'fc_lin': val[14], 'fc_exp': val[15], 'fecal_decay': val[16], 'tds_conv': val[17], 'mort': val[18], 'resp': val[19], 'slough1': val[20], 'slough2': val[21], 'nit': val[22], 'denit': val[23], 'p_sorp': val[24], 'p_sorp_max': val[25], 'solp_slp': val[26], 'solp_int': val[27] } data.append(d) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Septic_str, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Septic_str, data)
def read_data(self, recall_rec_id, delete_existing): with open(self.file_name, mode='r') as csv_file: dialect = csv.Sniffer().sniff(csv_file.readline()) csv_file.seek(0) replace_commas = dialect is not None and dialect.delimiter != ',' csv_reader = csv.DictReader(csv_file) rows = [] for row in csv_reader: if replace_commas: for key in row: row[key] = row[key].replace(',', '.', 1) row['recall_rec_id'] = recall_rec_id rows.append(row) if delete_existing: db.Recall_dat.delete().where(db.Recall_dat.recall_rec_id == recall_rec_id).execute() db_lib.bulk_insert(project_base.db, db.Recall_dat, rows)
def put(self, project_db, id): table = change.Water_balance_sft item_table = change.Water_balance_sft_item description = 'Calibration' parser = reqparse.RequestParser() parser.add_argument('id', type=int, required=False, location='json') parser.add_argument('name', type=str, required=True, location='json') parser.add_argument('items', type=list, required=False, location='json') args = parser.parse_args(strict=False) try: SetupProjectDatabase.init(project_db) result = self.save_args(table, args, id=id) if args['items'] is not None: items = [] for c in args['items']: items.append({ 'water_balance_sft_id': id, 'name': c['name'], 'surq_rto': c['surq_rto'], 'latq_rto': c['latq_rto'], 'perc_rto': c['perc_rto'], 'et_rto': c['et_rto'], 'tileq_rto': c['tileq_rto'], 'pet': c['pet'], 'sed': c['sed'], 'orgn': c['orgn'], 'orgp': c['orgp'], 'no3': c['no3'], 'solp': c['solp'] }) item_table.delete().where(item_table.water_balance_sft_id == id).execute() lib.bulk_insert(project_base.db, item_table, items) return 200 except IntegrityError as e: abort(400, message='{item} save error. '.format(item=description) + str(e)) except table.DoesNotExist: abort(404, message='{item} {id} does not exist'.format(item=description, id=id)) except Exception as ex: abort(400, message="Unexpected error {ex}".format(ex=ex))
def create_weather_stations( self, start_prog, total_prog ): # total_prog is the total progress percentage available for this method if self.__abort: return stations = [] cursor = project_base.db.execute_sql( "select lat, lon from weather_file group by lat, lon") data = cursor.fetchall() records = len(data) i = 1 for row in data: if self.__abort: return lat = row[0] lon = row[1] name = weather_sta_name(lat, lon) prog = round(i * total_prog / records) + start_prog # self.emit_progress(prog, "Creating weather station {name}...".format(name=name)) try: existing = Weather_sta_cli.get(Weather_sta_cli.name == name) except Weather_sta_cli.DoesNotExist: station = { "name": name, "wnd_dir": None, "atmo_dep": None, "lat": lat, "lon": lon, } """ "hmd": closest_lat_lon(project_base.db, "weather_file", lat, lon, "hmd"), "pcp": closest_lat_lon(project_base.db, "weather_file", lat, lon, "pcp"), "slr": closest_lat_lon(project_base.db, "weather_file", lat, lon, "slr"), "tmp": closest_lat_lon(project_base.db, "weather_file", lat, lon, "tmp"), "wnd": closest_lat_lon(project_base.db, "weather_file", lat, lon, "wnd") """ stations.append(station) i += 1 db_lib.bulk_insert(project_base.db, Weather_sta_cli, stations) self.match_files_to_stations(45, 45)
def read(self, database='project'): """ Read a graze.ops text file into the database. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 data = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 7, 'graze.ops') fert_name = val[1].strip().replace( "-", "_") # Replace '-' with '_' due to mismatch in files... try: fert = Fertilizer_frt.get(Fertilizer_frt.name == fert_name) d = { 'name': val[0].lower(), 'fert': fert.id, 'bm_eat': val[2], 'bm_tramp': val[3], 'man_amt': val[4], 'grz_bm_min': val[5], 'description': val[6] } data.append(d) except Fertilizer_frt.DoesNotExist: raise ValueError( "Could not find matching fertilizer {fert_name} in database." .format(fert_name=fert_name)) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, db.Graze_ops, data) else: db_lib.bulk_insert(datasets_base.db, db_datasets.Graze_ops, data)
def create_weather_stations( self, start_prog, total_prog ): # total_prog is the total progress percentage available for this method if self.__abort: return stations = [] query = Weather_wgn_cli.select() records = query.count() i = 1 for row in query: if self.__abort: return lat = row.lat lon = row.lon #name = "w{lat}{lon}".format(lat=abs(round(lat*1000)), lon=abs(round(lon*1000))) name = weather_sta_name(lat, lon) prog = round(i * total_prog / records) + start_prog # self.emit_progress(prog, "Creating weather station {name}...".format(name=name)) try: existing = Weather_sta_cli.get(Weather_sta_cli.name == name) except Weather_sta_cli.DoesNotExist: station = { "name": name, "hmd": None, "pcp": None, "slr": None, "tmp": None, "wnd": None, "wnd_dir": None, "atmo_dep": None, "lat": lat, "lon": lon, "wgn": row.id } stations.append(station) i += 1 db_lib.bulk_insert(project_base.db, Weather_sta_cli, stations)
def post(self, project_db): try: args = get_mgt_args() m = Management_sch() m.name = args['name'] m.save() new_auto = [] for a in args['auto_ops']: try: dt = D_table_dtl.get((D_table_dtl.file_name == 'lum.dtl') & (D_table_dtl.name == a['name'])) new_auto.append({'management_sch_id': m.id, 'd_table_id': dt.id, 'plant1': a['plant1'], 'plant2': a['plant2']}) except D_table_dtl.DoesNotExist: abort(404, message='Decision table {name} does not exist'.format(name=a['name'])) new_ops = [] order = 1 for o in args['operations']: new_ops.append({ 'management_sch_id': m.id, 'op_typ': o['op_typ'], 'mon': o['mon'], 'day': o['day'], 'op_data1': o['op_data1'], 'op_data2': o['op_data2'], 'op_data3': o['op_data3'], 'order': o['order'], 'hu_sch': o['hu_sch'] }) order += 1 lib.bulk_insert(base.db, Management_sch_auto, new_auto) lib.bulk_insert(base.db, Management_sch_op, new_ops) return 201 except IntegrityError as e: abort(400, message='Management schedule name must be unique.') except Exception as ex: abort(400, message="Unexpected error {ex}".format(ex=ex))
def read(self, database='project'): """ Read a septic.sep text file into the database. NOTE: CURRENTLY THERE IS AN EXTRA NUMERIC COLUMN BEFORE THE DESCRIPTION. :param database: project or datasets :return: """ file = open(self.file_name, "r") i = 1 septics = [] for line in file: if i > 2: val = line.split() self.check_cols(val, 13, 'septic') sep = { 'name': val[0].lower(), 'q_rate': val[1], 'bod': val[2], 'tss': val[3], 'nh4_n': val[4], 'no3_n': val[5], 'no2_n': val[6], 'org_n': val[7], 'min_p': val[8], 'org_p': val[9], 'fcoli': val[10], 'description': val[12] if val[12] != 'null' else None # 12 index because extra column } septics.append(sep) i += 1 if database == 'project': db_lib.bulk_insert(project_base.db, project_parmdb.Septic_sep, septics) else: db_lib.bulk_insert(datasets_base.db, datasets_parmdb.Septic_sep, septics)
def put(self, project_db, id): table = change.Ch_sed_budget_sft item_table = change.Ch_sed_budget_sft_item description = 'Calibration' parser = reqparse.RequestParser() parser.add_argument('id', type=int, required=False, location='json') parser.add_argument('name', type=str, required=True, location='json') parser.add_argument('items', type=list, required=False, location='json') args = parser.parse_args(strict=False) try: SetupProjectDatabase.init(project_db) result = self.save_args(table, args, id=id) if args['items'] is not None: items = [] for c in args['items']: items.append({ 'ch_sed_budget_sft_id': id, 'name': c['name'], 'cha_wide': c['cha_wide'], 'cha_dc_accr': c['cha_dc_accr'], 'head_cut': c['head_cut'], 'fp_accr': c['fp_accr'] }) item_table.delete().where(item_table.ch_sed_budget_sft_id == id).execute() lib.bulk_insert(project_base.db, item_table, items) return 200 except IntegrityError as e: abort(400, message='{item} save error. '.format(item=description) + str(e)) except table.DoesNotExist: abort(404, message='{item} {id} does not exist'.format(item=description, id=id)) except Exception as ex: abort(400, message="Unexpected error {ex}".format(ex=ex))
def insert_var_range(): file = open(source_data_path + 'var_range.csv', "r") i = 0 items = [] for line in file: if i > 0: val = line.split(',') items.append({ 'id': i, 'table': val[0], 'variable': val[1], 'type': val[2], 'min_value': val[3], 'max_value': val[4], 'default_value': val[5], 'default_text': val[6], 'units': val[7], 'description': val[8] }) i += 1 db_lib.bulk_insert(base.db, definitions.Var_range, items)
def insert_var_range_option(): file = open(source_data_path + 'var_range_option.csv', "r") i = 0 items = [] for line in file: if i > 0: val = line.split(',') vr = definitions.Var_range.get_or_none((definitions.Var_range.table == val[0]) & (definitions.Var_range.variable == val[1])) if vr is not None: items.append({ 'id': i, 'var_range_id': vr.id, 'value': val[2], 'text': val[3], 'text_only': True if val[4].strip() == '1' else False, 'text_value': None if val[5].strip() == '' else val[5].strip() }) i += 1 db_lib.bulk_insert(base.db, definitions.Var_range_option, items)
def add_wgn_stations_db(self, start_prog, total_prog): if self.__abort: return conn = sqlite3.connect(self.wgn_database) conn.row_factory = sqlite3.Row monthly_table = "{}_mon".format(self.wgn_table) if not db_lib.exists_table(conn, self.wgn_table): raise ValueError("Table {table} does not exist in {file}.".format( table=self.wgn_table, file=self.wgn_database)) if not db_lib.exists_table(conn, monthly_table): raise ValueError("Table {table} does not exist in {file}.".format( table=monthly_table, file=self.wgn_database)) if Rout_unit_con.select().count() > 0: coords = Rout_unit_con.select( fn.Min(Rout_unit_con.lat).alias("min_lat"), fn.Max(Rout_unit_con.lat).alias("max_lat"), fn.Min(Rout_unit_con.lon).alias("min_lon"), fn.Max(Rout_unit_con.lon).alias("max_lon")).get() query = "select * from {table_name} where lat between ? and ? and lon between ? and ? order by name".format( table_name=self.wgn_table) tol = 0.5 cursor = conn.cursor().execute( query, (coords.min_lat - tol, coords.max_lat + tol, coords.min_lon - tol, coords.max_lon + tol)) elif Chandeg_con.select().count() > 0: coords = Chandeg_con.select( fn.Min(Chandeg_con.lat).alias("min_lat"), fn.Max(Chandeg_con.lat).alias("max_lat"), fn.Min(Chandeg_con.lon).alias("min_lon"), fn.Max(Chandeg_con.lon).alias("max_lon")).get() query = "select * from {table_name} where lat between ? and ? and lon between ? and ? order by name".format( table_name=self.wgn_table) tol = 0.5 cursor = conn.cursor().execute( query, (coords.min_lat - tol, coords.max_lat + tol, coords.min_lon - tol, coords.max_lon + tol)) else: query = "select * from {table_name} order by name".format( table_name=self.wgn_table) cursor = conn.cursor().execute(query) wgns = [] ids = [] data = cursor.fetchall() records = len(data) #print(records) i = 1 print('\t - Preparing weather generator') for row in data: if self.__abort: return try: existing = Weather_wgn_cli.get( Weather_wgn_cli.name == row['name']) except Weather_wgn_cli.DoesNotExist: prog = round(i * (total_prog / 2) / records) + start_prog # self.emit_progress(prog, "Preparing weather generator {name}...".format(name=row['name'])) i += 1 ids.append(row['id']) wgn = { "id": row['id'], "name": row['name'], "lat": row['lat'], "lon": row['lon'], "elev": row['elev'], "rain_yrs": row['rain_yrs'] } wgns.append(wgn) prog = start_prog if records < 1 else round(i * (total_prog / 2) / records) + start_prog # self.emit_progress(prog, "Inserting {total} weather generators...".format(total=len(ids))) db_lib.bulk_insert(project_base.db, Weather_wgn_cli, wgns) # Chunk the id array so we don't hit the SQLite parameter limit! max_length = 999 id_chunks = [ ids[i:i + max_length] for i in range(0, len(ids), max_length) ] i = 1 start_prog = start_prog + (total_prog / 2) mon_count_query = "select count(*) from {table_name}".format( table_name=monthly_table) total_mon_rows = conn.cursor().execute(mon_count_query).fetchone()[0] current_total = 0 for chunk in id_chunks: monthly_values = [] mon_query = "select * from {table_name} where wgn_id in ({ids})".format( table_name=monthly_table, ids=",".join('?' * len(chunk))) mon_cursor = conn.cursor().execute(mon_query, chunk) mon_data = mon_cursor.fetchall() mon_records = len(mon_data) i = 1 for row in mon_data: if self.__abort: return if i == 1 or (i % 12 == 0): prog = round(i * (total_prog / 2) / mon_records) + start_prog # self.emit_progress(prog, "Preparing monthly values {i}/{total}...".format(i=i, total=mon_records)) i += 1 mon = { "weather_wgn_cli": row['wgn_id'], "month": row['month'], "tmp_max_ave": row['tmp_max_ave'], "tmp_min_ave": row['tmp_min_ave'], "tmp_max_sd": row['tmp_max_sd'], "tmp_min_sd": row['tmp_min_sd'], "pcp_ave": row['pcp_ave'], "pcp_sd": row['pcp_sd'], "pcp_skew": row['pcp_skew'], "wet_dry": row['wet_dry'], "wet_wet": row['wet_wet'], "pcp_days": row['pcp_days'], "pcp_hhr": row['pcp_hhr'], "slr_ave": row['slr_ave'], "dew_ave": row['dew_ave'], "wnd_ave": row['wnd_ave'] } monthly_values.append(mon) prog = round(i * (total_prog / 2) / mon_records) + start_prog current_total = current_total + mon_records # self.emit_progress(prog, "Inserting monthly values {rec}/{total}...".format(rec=current_total, total=total_mon_rows)) db_lib.bulk_insert(project_base.db, Weather_wgn_cli_mon, monthly_values)
def add_weather_files_type(self, source_file, weather_type, prog): start_date = None end_date = None starts = [] ends = [] if os.path.exists(source_file): # self.emit_progress(prog, "Inserting {type} files and coordinates...".format(type=weather_type)) weather_files = [] dir = os.path.dirname(source_file) with open(source_file, "r") as source_data: i = 0 for line in source_data: if self.__abort: break if i > 1: station_name = line.strip('\n') station_file = os.path.join(dir, station_name) if not os.path.exists(station_file): raise IOError( "File {file} not found. Weather data import aborted." .format(file=station_file)) try: existing = Weather_file.get( (Weather_file.filename == station_name) & (Weather_file.type == weather_type)) except Weather_file.DoesNotExist: with open(station_file, "r") as station_data: j = 0 for sline in station_data: if j == 2: station_info = sline.strip().split() if len(station_info) < 4: raise ValueError( "Invalid value at line {ln} of {file}. Expecting nbyr, tstep, lat, long, elev values separated by a space." .format(ln=str(j + 1), file=station_file)) lat = float(station_info[2]) lon = float(station_info[3]) file = { "filename": station_name, "type": weather_type, "lat": lat, "lon": lon } weather_files.append(file) elif j == 3: begin_data = sline.strip().split() if len(begin_data) < 3: raise ValueError( "Invalid value at line {ln} of {file}. Expecting year, julian day, and weather value separated by a space." .format(ln=str(j + 1), file=station_file)) date = datetime.datetime( int(begin_data[0]), 1, 1) current_start_date = date + datetime.timedelta( days=int(begin_data[1]) - 1) #if start_date is not None and current_start_date != start_date: # raise ValueError("Start dates in weather files do not match. Make sure all weather files have the same starting and ending dates.") #start_date = current_start_date starts.append(current_start_date) elif j > 3: break j += 1 non_empty_lines = [ sline for sline in station_data if sline ] last_line = non_empty_lines[ len(non_empty_lines) - 1].strip().split() date = datetime.datetime( int(last_line[0]), 1, 1) current_end_date = date + datetime.timedelta( days=int(last_line[1]) - 1) #if end_date is not None and current_end_date != end_date: # raise ValueError("Ending dates in weather files do not match. Make sure all weather files have the same starting and ending dates.") #end_date = current_end_date ends.append(current_end_date) i += 1 db_lib.bulk_insert(project_base.db, Weather_file, weather_files) if len(starts) > 0 and len(ends) > 0: start_date = max(starts) end_date = min(ends) return start_date, end_date
def insert_lum(): file = open(source_data_path + 'plants_landuse_rules.csv', "r") i = 1 rules = {} for line in file: if i > 1: val = line.split(',') n = val[0].lower().strip() lc = int(val[6]) rules[n] = { 'mgt': None, 'cn2': val[3], 'cons_prac': val[4], 'ov_mann': val[5], 'lc_status': True if lc is 1 else False, 'lai_init': float(val[7]), 'bm_init': float(val[8]), 'phu_init': float(val[9]), 'plnt_pop': float(val[10]), 'yrs_init': float(val[11]), 'rsd_init': float(val[12]) } i += 1 plants = hru_parm_db.Plants_plt.select() plant_coms = [] plant_com_items = [] plant_com_id = 1 for plt in plants: rule = rules[plt.name] plant_com = { 'id': plant_com_id, 'name': '{name}_comm'.format(name=plt.name), 'rot_yr_ini': 1 } plant_coms.append(plant_com) plant_com_item = { 'plant_ini': plant_com_id, 'plnt_name': plt.id, 'lc_status': rule['lc_status'], 'lai_init': rule['lai_init'], 'bm_init': rule['bm_init'], 'phu_init': rule['phu_init'], 'plnt_pop': rule['plnt_pop'], 'yrs_init': rule['yrs_init'], 'rsd_init': rule['rsd_init'] } plant_com_items.append(plant_com_item) plant_com_id += 1 db_lib.bulk_insert(base.db, init.Plant_ini, plant_coms) db_lib.bulk_insert(base.db, init.Plant_ini_item, plant_com_items) lum_default_cal_group = None lum_default_mgt = None #lum.Management_sch.get(lum.Management_sch.name == 'no_mgt').id lum_default_cn2 = 5 lum_default_cons_prac = 1 lum_default_ov_mann = 2 lums = [] lum_dict = {} lum_id = 1 for pcom in init.Plant_ini.select().order_by(init.Plant_ini.id): plant_name = pcom.name.strip().split('_comm')[0] rule = rules[plant_name] """mgt_id = lum_default_mgt if val_exists(rule['mgt']): mgt = lum.Management_sch.get(lum.Management_sch.name == rule['mgt']) mgt_id = mgt.id""" cn2_id = lum_default_cn2 if val_exists(rule['cn2']): cn2 = lum.Cntable_lum.get(lum.Cntable_lum.name == rule['cn2']) cn2_id = cn2.id cons_prac_id = lum_default_cons_prac if val_exists(rule['cons_prac']): cons_prac = lum.Cons_prac_lum.get(lum.Cons_prac_lum.name == rule['cons_prac']) cons_prac_id = cons_prac.id ov_mann_id = lum_default_ov_mann if val_exists(rule['ov_mann']): ov_mann = lum.Ovn_table_lum.get(lum.Ovn_table_lum.name == rule['ov_mann']) ov_mann_id = ov_mann.id l = { 'id': lum_id, 'name': '{name}_lum'.format(name=plant_name), 'plnt_com': pcom.id, 'mgt': None, #mgt_id, 'cn2': cn2_id, 'cons_prac': cons_prac_id, 'ov_mann': ov_mann_id, 'cal_group': lum_default_cal_group } lums.append(l) lum_dict[plant_name] = lum_id lum_id += 1 db_lib.bulk_insert(base.db, lum.Landuse_lum, lums) urbans = hru_parm_db.Urban_urb.select() urb_lums = [] for urb in urbans: l = { 'id': lum_id, 'name': '{name}_lum'.format(name=urb.name), 'urban': urb.id, 'urb_ro': 'buildup_washoff', 'mgt': lum_default_mgt, 'cn2': 49, 'cons_prac': lum_default_cons_prac, 'ov_mann': 18, 'cal_group': lum_default_cal_group } urb_lums.append(l) lum_dict[urb.name] = lum_id lum_id += 1 db_lib.bulk_insert(base.db, lum.Landuse_lum, urb_lums)
def read_default_table(self, file_name, name, table, db, start_line, ignore_id_col=True, desc_key=''): file = open(file_name, 'r') read_units = False if desc_key in ignore_units else True i = 1 rows = [] fields = table._meta.sorted_fields file_fields = [] for line in file: if read_units and i == start_line - 2: for h in line.strip().split(): file_fields.append(h.strip()) elif read_units and i == start_line - 1: units = line.strip().split() ui = units_start_column_index.get(desc_key, default_units_column_index) reverse_index = True if desc_key in reversed_unit_lines else False col_descs = [] for x in range(0, len(units)): try: column_name_val = file_fields[ x] if reverse_index else file_fields[ui + x] units_val = units[ui + x] if reverse_index else units[x] col_desc = { 'table_name': name, 'column_name': column_name_val, 'units': units_val } col_descs.append(col_desc) except IndexError: pass db_lib.bulk_insert(db, base.Column_description, col_descs) elif i >= start_line: val = line.strip().split() row = {} j = 0 for field in fields: skip = False if ignore_id_col and field.name == 'id': skip = True if not skip: try: row[field.name] = None if j >= len( val) or '*' in str(val[j]) else val[j] except IndexError: pass j += 1 rows.append(row) if len(rows) == 1000: db_lib.bulk_insert(db, table, rows) rows = [] i += 1 db_lib.bulk_insert(db, table, rows)