def create_link_counts_table(): sql = """CREATE TABLE link_counts ( begin_node_id BIGINT, end_node_id BIGINT, avg_num_trips FLOAT);""" try: db_main.execute(sql) except: print "Error creating link counts table." db_main.commit()
def save_link_counts(count_dict): sqls = [] for (begin_node_id, end_node_id) in count_dict: count = count_dict[(begin_node_id, end_node_id)] sql = "INSERT INTO link_counts VALUES(%d,%d,%f);" % ( begin_node_id,end_node_id,count) sqls.append(sql) db_main.execute("DELETE FROM link_counts;") db_main.execute("\n".join(sqls)) db_main.commit()
def save_link_counts(count_dict, perc_dict): sqls = [] for (begin_node_id, end_node_id) in count_dict: count = count_dict[(begin_node_id, end_node_id)] perc_obs = perc_dict[(begin_node_id, end_node_id)] sql = "INSERT INTO link_counts VALUES(%d,%d,%f,%f);" % ( begin_node_id,end_node_id,count, perc_obs) sqls.append(sql) db_main.execute("DELETE FROM link_counts;") db_main.execute("\n".join(sqls)) db_main.commit()
def create_link_counts_table(): drop_link_counts_table() sql = """CREATE TABLE link_counts ( begin_node_id BIGINT, end_node_id BIGINT, avg_num_trips FLOAT, perc_obs FLOAT);""" try: db_main.execute(sql) except: print "Error creating link counts table." db_main.commit()
def create_travel_time_table(): sql = """CREATE TABLE travel_times ( begin_node_id BIGINT, end_node_id BIGINT, datetime TIMESTAMP, travel_time REAL, num_trips INTEGER);""" try: db_main.execute(sql) sql = "CREATE INDEX idx_tt_datetime ON travel_times using BTREE (datetime);" db_main.execute(sql) except: pass db_main.commit()
def save_travel_times(road_map, datetime): date_str = "'" + str(datetime) + "'" # First remove any existing travel times for the given datetime delete_travel_times(datetime) # Next, add one row with the default speed. This will have nodes 0, 0 # The default speed will be saved in the travel time field default_speed = road_map.get_default_speed() if(default_speed!=None): sql = "INSERT INTO travel_times VALUES(0, 0, %s, %f, 0);" % (date_str, default_speed) db_main.execute(sql) BULK_SIZE=5000 # Create a prepared statement db_main.execute("PREPARE tt_plan (BIGINT, BIGINT, TIMESTAMP, REAL, INTEGER) AS " "INSERT INTO travel_times VALUES($1, $2, $3, $4, $5);") sqls = [] # Loop through the Links and create a bunch of EXECUTE statements for link in road_map.links: if(link.num_trips > 0): sql = "EXECUTE tt_plan(%d, %d, %s, %f, %d);" % ( link.origin_node_id, link.connecting_node_id, date_str, link.time, link.num_trips) sqls.append(sql) if(len(sqls) >= BULK_SIZE): # Concatenate EXECUTE statements and run them final_sql = "\n".join(sqls) sqls = [] db_main.execute(final_sql) db_main.commit() # Run the last few EXECUTE statements if necessary if(len(sqls) > 1): final_sql = "\n".join(sqls) db_main.execute(final_sql) db_main.commit() # Clean up the prepared statement db_main.execute("DEALLOCATE tt_plan;") db_main.commit()
def delete_travel_times(datetime): sql = "DELETE FROM travel_times where datetime=%s;" db_main.execute(sql, (datetime,)) db_main.commit()
def drop_travel_time_table(): try: db_main.execute("DROP TABLE travel_times;") except: pass db_main.commit()