def choose():
inputs = common.get_inputs(["Please enter a number: "], "")
option = inputs[0]
if option == "1":
queries.firstname_lastname_mentors(common.get_cursor())
elif option == "2":
queries.nickname_miskolc_mentors(common.get_cursor())
elif option == "3":
queries.carol_and_her_hat(common.get_cursor())
elif option == "4":
queries.another_girl_hat(common.get_cursor())
elif option == "5":
queries.add_new_applicant(common.get_cursor())
elif option == "6":
queries.change_phonenumber(common.get_cursor())
elif option == "7":
queries.del_arsiano_and_his_friend(common.get_cursor())
elif option == "8":
queries.all_data_mentors(common.get_cursor())
elif option == "9":
queries.all_data_applicants(common.get_cursor())
elif option == "0":
exit()
else:
raise KeyError("There is no such option.")
def get_trip_by_id(trip_id):
with get_cursor(SCHEMA_NAME) as cursor:
sql = """
SELECT * FROM testtable
WHERE trip_id=%(trip_id)s
"""
cursor.execute(sql, dict(trip_id=trip_id))
return [dict(row) for row in cursor.fetchall()]
def label_dependent_features(env_id, shortest_paths, feature_types, top=5):
new_features = pd.DataFrame()
conn, cursor = get_cursor()
good_users, bad_users = good_bad_users_from_db(env_id, cursor)
# good_users = set(map(str, good_users))
# bad_users = set(map(str, bad_users))
for df, prefix in shortest_paths:
try:
df = load_paths(df)
except Exception as e:
print(e)
continue
dangerous_users_df = list(set(df.index).intersection(bad_users))
safe_users_df = list(set(df.index).intersection(good_users))
unknown_users_df = list(set(df.index) - set(bad_users) - set(good_users))
for users, users_type in [(dangerous_users_df, 'bad_'),
(safe_users_df, 'good_'),
(unknown_users_df, 'unknown_')]:
if len(users) == 0:
continue
users_str = ', '.join(list(map(str, users)))
users_for_column = list(map(str, users))
df_path_features = df.loc[:, users_for_column] \
.T.agg(['min']) \
.T.add_prefix(users_type + prefix)
new_features = new_features.join(df_path_features, how='outer')
df_path_features = df.loc[users, :] \
.agg(['min'], axis=0) \
.T.add_prefix(users_type + prefix + 'rev_')
new_features = new_features.join(df_path_features, how='outer')
for feature in feature_types:
command = "SELECT user_id " \
"FROM data.user_features " \
"WHERE environment_id = %s AND %s IS NOT NULL " \
"AND user_id IN(%s) " \
"ORDER BY %s DESC " \
"LIMIT %d" % (env_id, feature, users_str, feature, top)
cursor.execute(command)
top_users = cursor.fetchall()
top_users_df = [x for t in top_users for x in t]
if len(set(df.index).intersection(top_users_df)) == 0:
continue
df_path_features_top = df.loc[:, users_for_column] \
.T.agg(['min']) \
.T.add_prefix(users_type + prefix) \
.add_suffix('_top{}_'.format(top) + feature)
new_features = new_features.join(df_path_features_top, how='outer')
if 'weight' not in prefix:
#### count?
new_features = new_features.join(calculate_dangerous_features(df, users_for_column, users_type + prefix))
new_features = new_features.join(calculate_dangerous_features(df, users_for_column, users_type + prefix + 'rev_'))
update_db(env_id, new_features, cursor)
conn.close()
del new_features
return
def get_all_trip_ids():
with get_cursor(SCHEMA_NAME) as cursor:
sql = """
SELECT DISTINCT on(trip_id) trip_id, timestamp FROM testtable
WHERE char_length(trip_id) < 7
"""
cursor.execute(sql)
for result in cursor.fetchall():
yield result['trip_id']
def insert_activity(**kwargs):
from common import get_cursor
with get_cursor(SCHEMA_NAME) as cursor:
kwargs['timestamp'] = datetime.fromtimestamp(
kwargs.get('vehicle_timestamp'))
kwargs['_created'] = datetime.now()
# print('inserting: ' + json.dumps(kwargs))
INSERT_ACTIVITY_SQL = """
INSERT INTO testtable (
vehicle_lon,
vehicle_id,
direction_name,
vehicle_timestamp,
route_name,
vehicle_bearing,
route_id,
trip_name,
trip_headsign,
vehicle_lat,
trip_id,
timestamp,
_created
) VALUES (
%(vehicle_lon)s,
%(vehicle_id)s,
%(direction_name)s,
%(vehicle_timestamp)s,
%(route_name)s,
%(vehicle_bearing)s,
%(route_id)s,
%(trip_name)s,
%(trip_headsign)s,
%(vehicle_lat)s,
%(trip_id)s,
%(timestamp)s,
%(_created)s
);"""
try:
cursor.execute(INSERT_ACTIVITY_SQL, kwargs)
except Exception as e:
print(e.message)
return False
return True
def create_activities_table():
with get_cursor(SCHEMA_NAME) as cursor:
create_table_schema = """
CREATE TABLE testtable (
id serial PRIMARY KEY,
vehicle_lon double precision NOT NULL,
vehicle_id integer NOT NULL,
direction_name varchar(20) NOT NULL,
vehicle_timestamp integer NOT NULL,
route_name text NOT NULL,
vehicle_bearing integer NOT NULL,
route_id varchar(20) NOT NULL,
trip_name text NOT NULL,
trip_headsign text NOT NULL,
vehicle_lat double precision NOT NULL,
trip_id varchar(40),
timestamp timestamp with time zone NOT NULL,
_created timestamp with time zone NOT NULL
)
"""
print(create_table_schema)
cursor.execute(create_table_schema)
def label_independent_features(env_id, link, filename, save_paths=True, G=None):
import os
p1 = None
p2 = None
df_vertices_features_G = None
try:
if filename is not None:
if save_paths:
p1, p2 = find_shortest_paths(filename)
G = get_network(filename)
df_vertices_features_G = find_graph_features(G, link + '_')
del G
if env_id is not None:
conn, cur = get_cursor()
update_db(env_id, df_vertices_features_G, cur)
conn.close()
except Exception as e:
print(e)
exit(0)
#save_obj(df_vertices_features_G, filename + '_vertices_features')
if filename is not None:
filename1 = filename + "_weights_reversed.csv"
filename2 = filename + "_length.csv"
try:
if p1 is not None:
p1.wait()
os.remove(filename1)
except Exception as e:
print(e)
try:
if p2 is not None:
p2.wait()
os.remove(filename2)
except Exception as e:
print(e)
return df_vertices_features_G
def applicants_and_mentors_page():
title = "Applicants and mentors page"
query = common.fetch_data(
queries.applicants_and_mentors_(common.get_cursor()))
return render_template("page.html", title=title, table=query)
def contacts_page():
title = "Contacts page"
query = common.fetch_data(queries.contacts(common.get_cursor()))
return render_template("page.html", title=title, table=query)
def mentors_by_country_page():
title = "Mentors by country page"
query = common.fetch_data(queries.mentors_by_country(common.get_cursor()))
return render_template("page.html", title=title, table=query)
def all_school_page():
title = "All school page"
query = common.fetch_data(queries.all_school(common.get_cursor()))
return render_template("page.html", title=title, table=query)
def mentors_and_schools_page():
title = "Mentors and schools page"
query = common.fetch_data(queries.mentors_and_schools(common.get_cursor()))
return render_template("page.html", title=title, table=query)
import tempfile
import uuid
import os
node2vec = Node2Vec(G, dimensions=dim, walk_length=10, num_walks=50, workers=1)
model = node2vec.fit(window=10, min_count=1, batch_words=4)
tmp_dir = tempfile.gettempdir()
tmp_file = os.path.join(tmp_dir, str(uuid.uuid4()))
model.wv.save_word2vec_format(tmp_file, binary=False)
df = pd.read_csv(tmp_file, sep = ' ', names=['node'] + [str(i) for i in range(dim)], skiprows=1)
os.remove(tmp_file)
return df.set_index('node').add_prefix(prefix)
if __name__ == "__main__":
commands = get_commands(new_columns())
conn, cursor = get_cursor()
for command in commands:
try:
cursor.execute(command)
except Exception as e:
print(e)
# for type in ['bad_', 'good_', 'unknown_']:
# for path_link in ['like_', 'repost_', 'friend_']:
# for weight in ['', 'weighted_']:
# for reverse in ['', 'reversed_']:
# column_names.append(type+'shortest_path_' + path_link + weight +reverse + 'min')
# for feature in [link + "_" + feature for link in ['like', 'friend', 'repost'] for feature in ['degree_prestige', 'proximity_prestige', 'betweenness_centrality','hub_measure', 'authority_measure', 'sociability', 'pagerank_weighted', 'pagerank']]:
# column_names.append(type+'shortest_path_' + path_link + weight + 'min_top_5_' + feature)
# if 'weighted' not in weight:
# for reverse in ['', 'reversed_']:
def create_unique_index():
with get_cursor('testschema') as cursor:
cursor.execute("""
CREATE UNIQUE INDEX trip_id_timestamp ON testtable (trip_id, timestamp);
""")
def drop_activities_table():
with get_cursor(SCHEMA_NAME) as cursor:
cursor.execute("""
DROP TABLE testtable;
""")
def do_nothing():
with get_cursor() as cursor:
cursor.execute('set search_path to testschema')
