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()
