def insert_data(data: list, dbset: dict, live: bool): ''' Upload data to the database :param data: List of dictionaries, gets converted to list of tuples :param dbset: DB settings passed to Pygresql to create a connection ''' num_rows = len(data) if num_rows > 0: LOGGER.info('Uploading %s rows to PostgreSQL', len(data)) LOGGER.debug(data[0]) else: LOGGER.warning('No data to upload') return to_insert = [] for dic in data: # convert each observation dictionary into a tuple row for inserting row = (dic["userId"], dic["analysisId"], dic["measuredTime"], dic["measuredTimeNoFilter"], dic["startPointNumber"], dic["startPointName"], dic["endPointNumber"], dic["endPointName"], dic["measuredTimeTimestamp"], dic["outlierLevel"], dic["cod"], dic["deviceClass"]) to_insert.append(row) db = DB(**dbset) if live: db.inserttable('king_pilot.daily_raw_bt', to_insert) else: db.inserttable('bluetooth.raw_data', to_insert) db.close()
class vol_utils(object): def __init__(self): self.logger = logging.getLogger('volume_project.sql_utilities') self.db_connect() def db_connect(self): CONFIG = configparser.ConfigParser() CONFIG.read('db.cfg') dbset = CONFIG['DBSETTINGS'] self.db = DB(dbname=dbset['database'], host=dbset['host'], user=dbset['user'], passwd=dbset['password']) self.logger.info('Database connected.') def exec_file(self, filename): try: f = open(filename) exec(filename) except: for root_f, folders, files in os.walk('.'): if filename in files: f = root_f + '/' + filename break self.logger.info('Running ', f) exec(f) if f is None: self.logger.error('File %s not found!', filename) raise Exception('File %s not found!', filename) def execute_sql(self, filename): f = None try: f = open(filename) except: for root_f, folders, files in os.walk('.'): if filename in files: f = open(root_f + '/' + filename) if f is None: self.logger.error('File %s not found!', filename) raise Exception('File not found!') sql = f.read() reconnect = 0 while True: try: self.db.query(sql) self.db.commit() return except ProgrammingError as pe: self.logger.error('Error in SQL', exc_info=True) self.db_connect() reconnect += 1 if reconnect > 5: raise Exception('Check DB connection. Cannot connect') def get_sql_results(self, filename, columns, replace_columns=None, parameters=None): ''' Input: filename columns: a list of column names replace_columns: a dictionary of {placeholders:real strings} parameters: list of parameter values Output: dataframe of results ''' f = None try: f = open(filename) except: for root_f, folders, files in os.walk('.'): if filename in files: f = open(root_f + '/' + filename) if f is None: if filename[: 6] == 'SELECT': # Also accepts sql queries directly in string form sql = filename else: self.logger.error('File %s not found!', filename) raise Exception('File not found!') else: sql = f.read() if replace_columns is not None: for key, value in replace_columns.items(): sql = sql.replace(key, str(value)) reconnect = 0 while True: try: if parameters is not None: return pd.DataFrame(self.db.query(sql, parameters).getresult(), columns=columns) else: return pd.DataFrame(self.db.query(sql).getresult(), columns=columns) except ProgrammingError as pe: self.logger.error('Error in SQL', exc_info=True) self.db_connect() reconnect += 1 if reconnect > 5: raise Exception('Check Error Message') def load_pkl(self, filename): f = None try: f = open(filename, "rb") except: for root_f, folders, files in os.walk('.'): if filename in files: f = open(root_f + '/' + filename) if f is None: self.logger.error('File %s not found!', filename) raise Exception('File not found!') return pickle.load(f) def truncatetable(self, tablename): reconnect = 0 while True: try: self.db.truncate(tablename) self.db.commit() self.logger.info('%s truncated', tablename) return except ProgrammingError as pe: print(pe) self.db_connect() reconnect += 1 if reconnect > 5: self.logger.error('Error in SQL', exc_info=True) raise Exception('Check Error Message') def inserttable(self, tablename, content): reconnect = 0 while True: try: self.db.inserttable(tablename, content) self.db.commit() self.logger.info('Inserted table: %s', tablename) break except ProgrammingError: self.db_connect() reconnect += 1 if reconnect > 5: self.logger.error('Error in SQL', exc_info=True) raise Exception('Check Error Message') def __exit__(self): self.db.close()
src = cfg[args.src] print("") print("------------------------------------------------------------------------------------") print("Source: " + str(args.src)) print("------------------------------------------------------------------------------------") srcdb = DB(dbname=src["db"], host=src["host"], port=int(src["port"]), user=src["user"], passwd=src["password"]) for srv in args.dst: item = cfg[srv] print("") print("------------------------------------------------------------------------------------") print("Destination: " + str(srv)) print("------------------------------------------------------------------------------------") dstdb = DB(dbname=item["db"], host=item["host"], port=int(item["port"]), user=item["user"], passwd=item["password"]) for table in tables: dstdb.start() rows = srcdb.query('SELECT * FROM %s' % table).getresult() dstdb.query('CREATE TEMPORARY TABLE newvals ON COMMIT DROP AS TABLE %s WITH NO DATA' % table) dstdb.inserttable('newvals', rows) dstdb.query('LOCK TABLE %s IN EXCLUSIVE MODE' % table) print(upd.get(table)) dstdb.query(upd.get(table)) print(insert.get(table)) dstdb.query(insert.get(table)) dstdb.commit()
year ) + m + ' (detector, start_time, end_time, flow_mean, occ_mean, vehicle_occ_mean, lpu_factor_mean)\ VALUES (new.detector, new.start_time, new.end_time, new.flow_mean, new.occ_mean, new.vehicle_occ_mean, new.lpu_factor_mean)' q = 'CREATE TABLE scoot.agg_15_' + str(year) + m + '(detector text, \ start_time timestamp without time zone, end_time timestamp without time zone, \ flow_mean int, occ_mean double precision, vehicle_occ_mean int, \ LPU_factor_mean double precision, \ CONSTRAINT c' + str( year ) + m + ' CHECK (date_part(\'month\'::text, start_time) = ' + m + '::double precision AND date_part(\'year\'::text, start_time) = ' + str( year) + '::double precision))' db.query(q) db.query(r) print('Table Created') db.inserttable('scoot.agg_15_' + str(year) + m, sdata) print('Table Inserted') # Insert outflowing data from a month earlier than start_month if not sdata_prev.empty: if month == 1: year = year - 1 m = '12' else: if (month - 1) < 10: m = '0' + str(month - 1) else: m = str(month - 1) sdata_prev = sdata_prev[[ 'detector', 'start_time', 'end_time', 'flow_mean', 'occ_mean', 'vehicle_occ_mean', 'lpu_factor_mean'
for srv in args.dst: item = cfg[srv] print("") print( "------------------------------------------------------------------------------------" ) print("Destination: " + str(srv)) print( "------------------------------------------------------------------------------------" ) dstdb = DB(dbname=item["db"], host=item["host"], port=int(item["port"]), user=item["user"], passwd=item["password"]) for table in tables: dstdb.start() rows = srcdb.query('SELECT * FROM %s' % table).getresult() dstdb.query( 'CREATE TEMPORARY TABLE newvals ON COMMIT DROP AS TABLE %s WITH NO DATA' % table) dstdb.inserttable('newvals', rows) dstdb.query('LOCK TABLE %s IN EXCLUSIVE MODE' % table) print(upd.get(table)) dstdb.query(upd.get(table)) print(insert.get(table)) dstdb.query(insert.get(table)) dstdb.commit()
scoot['Time'] = pd.to_datetime(scoot['Time']).dt.time # Shift all count time by a cycle time to represent the start of the signal(count) cycle scoot['seconds'] = scoot['seconds'] - scoot['CycleTime'] scoot = scoot[scoot['seconds'] >= 0] scoot['Time'] = scoot.apply(subtract_ct, axis=1) # Sort based on Time (Data is sorted most of the time, occasional mess) scoot = scoot.sort_values(['seconds']) del scoot['M29'] scoot['Date'] = scoot['Date'].apply(lambda x: x.strftime("%Y-%m-%d")) scoot['Time'] = scoot['Time'].apply(lambda x: x.strftime("%H:%M:%S")) scoot = scoot.values.tolist() # Create partition rules if not inplace (Will replace if this month was loaded before) if (load_date.year, load_date.month) not in ruled: ruled.append((load_date.year, load_date.month)) r = 'CREATE OR REPLACE RULE cycle_level_' + load_date.strftime("%Y%m") + '_insert AS \ ON INSERT TO scoot.cyclelevel_' + load_date.strftime("%Y%m") + \ ' WHERE date_part(\'month\'::text, new.count_time) = ' + str(load_date.month) + '::double precision \ AND date_part(\'year\'::text, new.count_time) = ' + str(load_date.year) + '::double precision \ DO INSTEAD INSERT INTO scoot.cyclelevel_' + load_date.strftime("%Y%m") + '(count_date, count_time, detector, cycle_time, flow, occupancy, seconds) \ VALUES (new.count_date, new.count_time, new.detector, new.cycle_time, new.flow, new.occupancy, new.seconds)' db.query(r) db.inserttable('scoot.cyclelevel_' + load_date.strftime("%Y%m"), scoot) print('Finished ' + load_date.strftime("%Y-%m-%d"))
chain.append(current) root.remove(current) to_visit.extend(list(pairs.groupby('c1').get_group(current)['c2'])) visited.append(current) chains.append(chain) groups = {} count = 1 table = [] for group in chains: for tcl in group: table.append([tcl, count]) count = count + 1 db.truncate('prj_volume.centreline_groups_l2') db.inserttable('prj_volume.centreline_groups_l2', table) group_no_merge = [ x for t in db.query( 'SELECT DISTINCT group_number FROM prj_volume.centreline_groups LEFT JOIN prj_volume.centreline_groups_l2 ON (group_number=l1_group_number) WHERE l2_group_number IS NULL' ).getresult() for x in t ] for tcl in group_no_merge: table.append([tcl, count]) count = count + 1 db.truncate('prj_volume.centreline_groups_l2') db.inserttable('prj_volume.centreline_groups_l2', table) db.close()
"REFERENCES Feature (featureId)" ) print ("Relation: cityfeaturescity "); db.query("ALTER TABLE cityfeatures ADD CONSTRAINT cityfeaturescity " "FOREIGN KEY (cityid) " "REFERENCES city (cityid)" ) print ("Inserting data into the database") print ("Table City - multiple rows at once") cities = 'Seattle Spokane Tacoma Vancouver'.split() data = list(enumerate(cities, start=1)) db.inserttable('city', data) print ("Table Feature - multiple rows at once") features = 'Library Park Museum Theatre'.split() data = list(enumerate(features, start=1)) db.inserttable('feature', data) print ("Table cityfeature - single row at once") db.insert('cityfeatures',featureid=1, cityid=1) db.insert('cityfeatures',featureid=2, cityid=1) db.insert('cityfeatures',featureid=3, cityid=1) db.insert('cityfeatures',featureid=4, cityid=1)
tree.findall(".//EndTime"), tree.findall(".//WorkPeriod"), tree.findall(".//Expired"), tree.findall(".//Signing"), tree.findall(".//Notification"), tree.findall(".//WorkEventType"), tree.findall(".//Contractor"), tree.findall(".//PermitType"), tree.findall(".//Description")): rowsql = [] for x in a: if x.tag in ('LastUpdated', 'StartTime', 'EndTime') and x.text is not None: y = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(float(x.text) / 1000)) else: y = x.text if x.tag == 'Name': rowsql.append(None) rowsql.append(None) rowsql.append(None) rowsql.append(y) rowsql.append(None) data.append(rowsql) db.truncate('city.restrictions_import') db.inserttable('city.restrictions_import', data) sql = db.query( "DELETE FROM city.restrictions USING city.restrictions_import WHERE city.restrictions.id = city.restrictions_import.id" ) sql = db.query( "INSERT INTO city.restrictions SELECT * FROM city.restrictions_import") db.close()
events['group_id'] = group_id grouped = events.groupby(['group_id']) event_table = [] for (id), group in grouped: row = [] row.append(id) row.append(group['event_name'].iloc[0]) row.append(group['venue_id'].iloc[0]) row.append(group['classification'].iloc[0]) row.append(len(group)) event_table.append(row) del events['venue_id'] del events['classification'] del events['event_name'] events = events[[ 'group_id', 'event_id', 'start_date', 'start_time', 'end_date', 'end_time', 'source' ]] events['start_date'] = events.apply(caststartdate, axis=1) events['end_date'] = events.apply(castenddate, axis=1) events['start_time'] = events.apply(caststarttime, axis=1) events['end_time'] = events.apply(castendtime, axis=1) events = events.values.tolist() db.truncate('city.event_groups') db.truncate('city.event_details') db.inserttable('city.event_groups', event_table) db.inserttable('city.event_details', events) db.close()