def search_by_tags():
data = json.loads(request.get_data())
page = int(request.args.get('page', 1))
per_page = int(request.args.get('per_page', 10))
tags_list = data.get('tags')
if not isinstance(tags_list, list):
tags_list = tags_list.split(",")
post_id_select = select([distinct(tags.c.post_id)])\
.where(tags.c.tag.in_(tags_list))\
.limit(per_page).offset((page-1) * per_page)
post_total = select([distinct(tags.c.post_id)])\
.where(tags.c.tag.in_(tags_list))
conn = engine.connect()
result_post_id = conn.execute(post_id_select)
result_post_total = conn.execute(post_total)
conn.close()
if result_post_id.rowcount:
post_id_list = [dict(r).get('post_id') for r in result_post_id.fetchall()]
all_matched_posts = posts.select().where(posts.c.id.in_(post_id_list))
conn = engine.connect()
res_all_matched_posts = conn.execute(all_matched_posts)
conn.close()
res = {
'total': result_post_total.rowcount,
'data': [dict(r) for r in res_all_matched_posts.fetchall()]
}
return jsonify(res)
else:
res = {
'total': result_post_total.rowcount,
'data': []
}
return jsonify(res)
def rep_players_count_dynamics():
if not current_user.rights.can_access_to_admin_page:
abort(403)
form = PlayersCountDynamicsForm(request.form)
with engine.connect() as conn:
result = conn.execute("call usp_rep_players_count_dynamics_filt_teams")
teams = [row for row in result]
form.team.choices = teams
if request.method == "GET":
form.begin_date.data = datetime.now().replace(
hour=0, minute=0, second=0) + timedelta(days=-7)
form.end_date.data = datetime.now().replace(hour=0, minute=0, second=0)
return render_template('players_count_dynamics.html', form=form)
if request.method == 'POST':
team_id = form.team.data
begin_date = form.begin_date.data.date()
end_date = form.end_date.data.date()
with engine.connect() as conn:
result = conn.execute(
"call usp_rep_players_count_dynamics(%s, %s, %s)",
(team_id, begin_date, end_date))
result_table = [row for row in result]
dates = [
date['fulldate'].strftime("%d/%m/%Y") for date in result_table
]
values = [value['players_cnt'] for value in result_table]
return render_template('players_count_dynamics.html',
form=form,
dates=dates,
values=values)
def add_tags(post_id):
data = json.loads(request.get_data())
tags_list = data.get('tags')
if not isinstance(tags_list, list):
tags_list = tags_list.split(",")
post_exists = select([posts.c.id]).where(posts.c.id == int(post_id))
conn = engine.connect()
post_exists_res = conn.execute(post_exists)
conn.close()
if not post_exists_res.rowcount:
error = {
'error': u'No post with id {} found for User {}'.format(post_id, request.user.get('username'))
}
resp = jsonify(error)
resp.status_code = 404
return resp
stmt = tags.select().where(tags.c.post_id == int(post_id))
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
list_of_existing_tags = [dict(r).get('tag') for r in result.fetchall()]
tags_to_persist = [tag for tag in tags_list if tag not in list_of_existing_tags]
if tags_to_persist:
conn = engine.connect()
for one_tag in tags_to_persist:
tag_insert = tags.insert().values(post_id=int(post_id), tag=one_tag)
conn.execute(tag_insert)
conn.close()
res = {
'success': True
}
return jsonify(res)
def ucc2020_blitz():
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc2020_blitz_best_players")
best_players_result = [row for row in result]
best_players_columns = result.keys()
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc2020_blitz_rounds")
rounds_result = [row for row in result]
rounds_result_columns = result.keys()
best_players = pd.DataFrame(best_players_result, columns=best_players_columns)
rounds = pd.DataFrame(rounds_result, columns=rounds_result_columns)
first_teams = rounds[['round_id', 'match_id', 'team1_name', 'team1_result', 'team2_name']]
first_teams = first_teams.rename(columns={"team1_name": "team_name", "team1_result": "team_result", "team2_name": "opponent_name"})
second_teams = rounds[['round_id', 'match_id', 'team2_name', 'team2_result', 'team1_name']]
second_teams = second_teams.rename(columns={"team2_name": "team_name", "team2_result": "team_result", "team1_name": "opponent_name"})
tournament_table = pd.concat([first_teams, second_teams]).reset_index()
tournament_table = pd.pivot_table(tournament_table, columns=['opponent_name'], index=['team_name'], values='team_result').reset_index()
tournament_table['Загалом'] = tournament_table.sum(axis=1)
tournament_table.reset_index(inplace=True)
tournament_table.sort_values(by=['Загалом'], ascending=False, inplace=True)
return render_template("ucc2019.html", best_players=best_players, rounds=rounds, tournament_table=tournament_table)
def get_users(user_id=None):
if not user_id:
page = int(request.args.get('page', 1))
per_page = int(request.args.get('per_page', 10))
stmt = select([users.c.id, users.c.username, users.c.created_at, users.c.updated_at])\
.limit(per_page).offset((page-1)* per_page)
total_stmt = select([users.c.id])
conn = engine.connect()
result = conn.execute(stmt)
total_result = conn.execute(total_stmt)
conn.close()
rows = result.fetchall()
total = total_result.rowcount
else:
stmt = select([users.c.id, users.c.username, users.c.created_at, users.c.updated_at])\
.where(users.c.id == int(user_id))
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
rows = result.fetchall()
total = result.rowcount
l = [dict(r) for r in rows]
result_resp = {
'total': total,
'data': l
}
resp = jsonify(result_resp)
resp.status_code = 200
return resp
def ucc_tournament(tournament_id):
if int(tournament_id) in (11, 15):
with engine.connect() as conn:
tmp = conn.execute("call usp_stat_lcwl_bestplayers ({0})".format(tournament_id))
result = [row for row in tmp]
columns = tmp.keys()
points = pd.DataFrame(result, columns=columns)
max_points = pd.DataFrame(result, columns=columns)
points = pd.pivot_table(points, columns=['club_2', 'round_id'], index=['player_1', 'chess_blitz_rating'],
values='team1_player_score') \
.reset_index()
cols = [('player_1', ''), ('chess_blitz_rating', '')] + sorted(list(points.columns)[2:], key=lambda x: x[1])
points = points.reindex(columns=cols).reset_index(drop=True)
points['Total'] = points[list(points.columns[2:])].sum(axis=1)
rivals = points.columns[2:]
points = points.sort_values(by=['Total'], ascending=False)
max_points = pd.pivot_table(max_points, columns=['club_2', 'round_id'],
index=['player_1', 'chess_blitz_rating'],
values='team1_player_max_possible_score').reset_index()
max_points = max_points.reindex(columns=cols).reset_index(drop=True)
max_points['Total_max'] = max_points[list(max_points.columns[2:])].sum(axis=1)
points = pd.merge(points, max_points[['Total_max', 'player_1']], on='player_1', how='left')
points['points_percentage'] = 100 * points['Total'] / points['Total_max']
points = points.sort_values(by=['Total', 'points_percentage'], ascending=False)
points['Place'] = np.arange(1, len(points) + 1)
return render_template("lcwl_best_players.html", points=points, rivals=rivals)
else:
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc_best_players ({0})".format(tournament_id))
best_players_result = [row for row in result]
best_players_columns = result.keys()
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc_rounds ({0})".format(tournament_id))
rounds_result = [row for row in result]
rounds_result_columns = result.keys()
best_players = pd.DataFrame(best_players_result, columns=best_players_columns)
rounds = pd.DataFrame(rounds_result, columns=rounds_result_columns)
first_teams = rounds[['round_id', 'match_id', 'team1_name', 'team1_result', 'team2_name']]
first_teams = first_teams.rename(columns={"team1_name": "team_name", "team1_result": "team_result", "team2_name": "opponent_name"})
second_teams = rounds[['round_id', 'match_id', 'team2_name', 'team2_result', 'team1_name']]
second_teams = second_teams.rename(columns={"team2_name": "team_name", "team2_result": "team_result", "team1_name": "opponent_name"})
tournament_table = pd.concat([first_teams, second_teams]).reset_index()
tournament_table = pd.pivot_table(tournament_table, columns=['opponent_name'], index=['team_name'], values='team_result').reset_index()
tournament_table['Загалом'] = tournament_table.sum(axis=1)
tournament_table.reset_index(inplace=True)
tournament_table.sort_values(by=['Загалом'], ascending=False, inplace=True)
return render_template("ucc2019.html", best_players=best_players, rounds=rounds, tournament_table=tournament_table)
def add_tags(post_id):
data = json.loads(request.get_data())
tags_list = data.get('tags')
if not isinstance(tags_list, list):
tags_list = tags_list.split(",")
post_exists = select([posts.c.id]).where(posts.c.id == int(post_id))
conn = engine.connect()
post_exists_res = conn.execute(post_exists)
conn.close()
if not post_exists_res.rowcount:
error = {
'error':
u'No post with id {} found for User {}'.format(
post_id, request.user.get('username'))
}
resp = jsonify(error)
resp.status_code = 404
return resp
stmt = tags.select().where(tags.c.post_id == int(post_id))
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
list_of_existing_tags = [dict(r).get('tag') for r in result.fetchall()]
tags_to_persist = [
tag for tag in tags_list if tag not in list_of_existing_tags
]
if tags_to_persist:
conn = engine.connect()
for one_tag in tags_to_persist:
tag_insert = tags.insert().values(post_id=int(post_id),
tag=one_tag)
conn.execute(tag_insert)
conn.close()
res = {'success': True}
return jsonify(res)
def search_by_tags():
data = json.loads(request.get_data())
page = int(request.args.get('page', 1))
per_page = int(request.args.get('per_page', 10))
tags_list = data.get('tags')
if not isinstance(tags_list, list):
tags_list = tags_list.split(",")
post_id_select = select([distinct(tags.c.post_id)])\
.where(tags.c.tag.in_(tags_list))\
.limit(per_page).offset((page-1) * per_page)
post_total = select([distinct(tags.c.post_id)])\
.where(tags.c.tag.in_(tags_list))
conn = engine.connect()
result_post_id = conn.execute(post_id_select)
result_post_total = conn.execute(post_total)
conn.close()
if result_post_id.rowcount:
post_id_list = [
dict(r).get('post_id') for r in result_post_id.fetchall()
]
all_matched_posts = posts.select().where(posts.c.id.in_(post_id_list))
conn = engine.connect()
res_all_matched_posts = conn.execute(all_matched_posts)
conn.close()
res = {
'total': result_post_total.rowcount,
'data': [dict(r) for r in res_all_matched_posts.fetchall()]
}
return jsonify(res)
else:
res = {'total': result_post_total.rowcount, 'data': []}
return jsonify(res)
def is_logged_in(authorization):
authenticated = False
now = datetime.datetime.now()
fifteen_minutes_back = now - datetime.timedelta(minutes=15)
stmt = token.select()\
.where(token.c.token == authorization)\
.where(token.c.last_accessed_at > fifteen_minutes_back)
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
if result.rowcount:
token_obj = dict(result.fetchone())
stmt = token.update()\
.where(token.c.token == token_obj.get('token'))\
.values(last_accessed_at=now)
user_get = select([users.c.id, users.c.username])\
.where(users.c.id == int(token_obj.get('user_id')))
conn = engine.connect()
conn.execute(stmt)
user_res = conn.execute(user_get)
conn.close()
user = dict(user_res.fetchone())
setattr(request, 'user', user)
authenticated = True
return authenticated
def get_post(post_id=None):
if post_id:
stmt = posts.select().where(posts.c.id == int(post_id))
conn = engine.connect()
data = conn.execute(stmt)
conn.close()
rows = data.fetchall()
total = data.rowcount
else:
page = int(request.args.get('page', 1))
per_page = int(request.args.get('per_page', 10))
post_select = posts.select()\
.order_by(desc("created_by"))\
.limit(per_page).offset((page-1) * per_page)
all_posts = posts.select()
conn = engine.connect()
result_posts = conn.execute(post_select)
all_posts_res = conn.execute(all_posts)
conn.close()
rows = result_posts.fetchall()
total = all_posts_res.rowcount
l = [dict(r) for r in rows]
result_resp = {
'total': total,
'data': l
}
resp = jsonify(result_resp)
resp.status_code = 200
return resp
def get_post(post_id=None):
if post_id:
stmt = posts.select().where(posts.c.id == int(post_id))
conn = engine.connect()
data = conn.execute(stmt)
conn.close()
rows = data.fetchall()
total = data.rowcount
else:
page = int(request.args.get('page', 1))
per_page = int(request.args.get('per_page', 10))
post_select = posts.select()\
.order_by(desc("created_by"))\
.limit(per_page).offset((page-1) * per_page)
all_posts = posts.select()
conn = engine.connect()
result_posts = conn.execute(post_select)
all_posts_res = conn.execute(all_posts)
conn.close()
rows = result_posts.fetchall()
total = all_posts_res.rowcount
l = [dict(r) for r in rows]
result_resp = {'total': total, 'data': l}
resp = jsonify(result_resp)
resp.status_code = 200
return resp
def ucc2019_bullet_rapid():
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc2019_rapid_bullet_best_player")
best_players_result = [row for row in result]
best_players_columns = result.keys()
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc2019_rapid_bullet_rounds")
rounds_result = [row for row in result]
rounds_result_columns = result.keys()
best_players = pd.DataFrame(best_players_result, columns=best_players_columns)
rounds = pd.DataFrame(rounds_result, columns=rounds_result_columns)
groupA_rounds = rounds.loc[rounds['team1_id'].isin([84300, 42800, 67918, 57502, 71728, 52452])
& rounds['round_id'].isin([1, 2, 3, 4, 5])]
groupB_rounds = rounds.loc[rounds['team1_id'].isin([71066, 42164, 84302, 27634, 61104, 72704])
& rounds['round_id'].isin([1, 2, 3, 4, 5])]
groupA_first_teams = groupA_rounds[['round_id', 'match_id', 'team1_name', 'team1_result', 'team2_name']]
groupA_first_teams = groupA_first_teams.rename(columns={"team1_name": "team_name", "team1_result": "team_result", "team2_name": "opponent_name"})
groupA_second_teams = groupA_rounds[['round_id', 'match_id', 'team2_name', 'team2_result', 'team1_name']]
groupA_second_teams = groupA_second_teams.rename(columns={"team2_name": "team_name", "team2_result": "team_result", "team1_name": "opponent_name"})
groupA_tournament_table = pd.concat([groupA_first_teams, groupA_second_teams]).reset_index()
groupA_tournament_table = pd.pivot_table(groupA_tournament_table, columns=['opponent_name'], index=['team_name'], values='team_result', aggfunc='sum').reset_index()
groupA_tournament_table['Загалом'] = groupA_tournament_table.sum(axis=1)
groupA_tournament_table.reset_index(inplace=True)
groupA_tournament_table.sort_values(by=['Загалом'], ascending=False, inplace=True)
groupB_first_teams = groupB_rounds[['round_id', 'match_id', 'team1_name', 'team1_result', 'team2_name']]
groupB_first_teams = groupB_first_teams.rename(
columns={"team1_name": "team_name", "team1_result": "team_result", "team2_name": "opponent_name"})
groupB_second_teams = groupB_rounds[['round_id', 'match_id', 'team2_name', 'team2_result', 'team1_name']]
groupB_second_teams = groupB_second_teams.rename(
columns={"team2_name": "team_name", "team2_result": "team_result", "team1_name": "opponent_name"})
groupB_tournament_table = pd.concat([groupB_first_teams, groupB_second_teams]).reset_index()
groupB_tournament_table = pd.pivot_table(groupB_tournament_table, columns=['opponent_name'], index=['team_name'],
values='team_result', aggfunc='sum').reset_index()
groupB_tournament_table['Загалом'] = groupB_tournament_table.sum(axis=1)
groupB_tournament_table.reset_index(inplace=True)
groupB_tournament_table.sort_values(by=['Загалом'], ascending=False, inplace=True)
return render_template("ucc2019_bullet_rapid.html", best_players=best_players, rounds=rounds,
groupA_tournament_table=groupA_tournament_table,
groupB_tournament_table=groupB_tournament_table)
def ucc2020_daily():
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc2020daily_live_standings")
live_standings_result = [row for row in result]
live_standings_columns = result.keys()
live_standings = pd.DataFrame(live_standings_result, columns=live_standings_columns)
with engine.connect() as conn:
result = conn.execute("call usp_stat_ucc2020daily_rounds")
rounds_result = [row for row in result]
rounds_columns = result.keys()
rounds = pd.DataFrame(rounds_result, columns=rounds_columns)
rounds_list = rounds['round_id'].unique().tolist()
return render_template("ucc2020_daily.html", live_standings=live_standings, rounds=rounds, rounds_list=rounds_list)
def kyiv_reg_dashboard():
with engine.connect() as conn:
result = conn.execute("call kyiv_reg_matches_dashboard")
reg_matches_result = [row for row in result]
reg_matches_columns = result.keys()
with engine.connect() as conn:
result = conn.execute("call kyiv_reg_matches_dashboard_timeouts")
timeouts_result = [row for row in result]
timeouts_columns = result.keys()
matches = pd.DataFrame(reg_matches_result, columns=reg_matches_columns)
timeouts = pd.DataFrame(timeouts_result, columns=timeouts_columns)
return render_template('kyiv_reg_dashboard.html',
matches=matches,
timeouts=timeouts)
def fetch_by_self(lat_: float, lng_: float):
rad = 5 # search radi
Radi_earth = 6371
maxLat = lat_ + math.degrees(rad / Radi_earth)
minLat = lat_ - math.degrees(rad / Radi_earth)
maxLon = lng_ - math.degrees(
math.asin(rad / Radi_earth) / math.cos(math.degrees(lat_)))
minLon = lng_ + math.degrees(
math.asin(rad / Radi_earth) / math.cos(math.degrees(lat_)))
print("latitude : " + str(maxLat) + " , " + str(minLat))
print("longitude : " + str(maxLon) + " , " + str(minLon))
query = "Select * From pin Where latitude Between " + str(
minLat) + " And " + str(maxLat) + " And longitude Between " + str(
minLon) + " And " + str(maxLon) + ";"
conn = engine.connect()
result = conn.execute(query)
conn.close()
final = ""
for row in result:
print(row)
final += final + " " + str(row)
if final == "":
return "No results found."
return final
def create_user():
data = json.loads(request.get_data())
error = validate_for_user(data)
if error:
resp = jsonify(error)
resp.status_code = 400
return resp
user_insert = users.insert()\
.values(username=data.get('username'), password=data.get('password'))
conn = engine.connect()
try:
result = conn.execute(user_insert)
except sqlalchemy.exc.IntegrityError:
error = {
'error': u'username {} already exists'.format(data.get('username'))
}
resp = jsonify(error)
resp.status_code = 400
conn.close()
return resp
conn.close()
res = {
'id': result.inserted_primary_key
}
resp = jsonify(res)
resp.status_code = 201
return resp
def home():
#goals by session user
con=engine.connect()
goals = con.execute("""select id, email, category, description, target, round(amount/((julianday(target)-julianday('now'))/30),2) as 'monthly', round((((julianday(target)-julianday(datetime('now')))/30)),1) as 'months',
round((julianday(target)-julianday(datetime('now'))),0) as 'days', round(goals.amount,0) as 'amount'
from goals
where email=:param
order by category""", {"param":session['email']} )
progress = con.execute("""select goals.category, goals.description, ifnull(round(sum(transactions.amount),2), 0) as 'sum', round(((julianday(target)-julianday(datetime('now')))/30),1) as 'months', round((julianday(target)-julianday(datetime('now'))),0) as 'days',
round(goals.amount,2) as 'goal', ifnull((sum(transactions.amount)/goals.amount*100),0) as 'progress'
from goals LEFT JOIN transactions on goals.category=transactions.category and goals.description=transactions.goal
and goals.email=transactions.email
where goals.email=:param
and goals.amount is not null
group by goals.category, goals.description
order by goals.category""", {"param":session['email']} )
#transactions by session user
transactions = con.execute("select * from transactions where email=:param order by trans_date desc", {"param":session['email']} )
#accounts by session user
accounts = con.execute("select accounts.id, accounts.name, ifnull(round(sum(transactions.amount),2),0) as 'sum' from accounts left join transactions on accounts.name=transactions.account and accounts.email=transactions.email where accounts.email=:param group by accounts.name order by accounts.name", {"param":session['email']} )
#categories by session user
categories = con.execute("select categories.id, categories.name, ifnull(round(sum(transactions.amount),2),0) as 'sum' from categories left join transactions on categories.name=transactions.category and categories.email=transactions.email where categories.email=:param group by categories.name order by categories.name", {"param":session['email']} )
print (str(session['email']),'is on Home')
return render_template('home.html', accounts=accounts, transactions=transactions, categories=categories, goals=goals, progress=progress, today=today)
def setup():
init_db()
con = engine.connect()
con.execute(users.delete())
con.execute(users.insert(), name='admin', email='admin@localhost', password='******')
con.execute(users.insert(), name='anna', email='anna@localhost', password='******')
return "ok"
def update_posts(post_id):
data = json.loads(request.get_data())
error = validate_data_for_create_post(data)
if error:
resp = jsonify(error)
resp.status_code = 400
return resp
stmt = posts.update()\
.values(title=data.get('title'), description=data.get('description'))\
.where(posts.c.user_id == request.user.get('id'))\
.where(posts.c.id == int(post_id))
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
if result.rowcount:
res = {
'success': True
}
return jsonify(res)
error = {
'error': u'No post with id {} found for User {}'.format(post_id, request.user.get('username'))
}
resp = jsonify(error)
resp.status_code = 404
return resp
def update_posts(post_id):
data = json.loads(request.get_data())
error = validate_data_for_create_post(data)
if error:
resp = jsonify(error)
resp.status_code = 400
return resp
stmt = posts.update()\
.values(title=data.get('title'), description=data.get('description'))\
.where(posts.c.user_id == request.user.get('id'))\
.where(posts.c.id == int(post_id))
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
if result.rowcount:
res = {'success': True}
return jsonify(res)
error = {
'error':
u'No post with id {} found for User {}'.format(
post_id, request.user.get('username'))
}
resp = jsonify(error)
resp.status_code = 404
return resp
def lcwl_s5_main():
with engine.connect() as conn:
tmp = conn.execute("call usp_stat_lcwl_s5_main")
result = [row for row in tmp]
columns = tmp.keys()
points = pd.DataFrame(result, columns=columns)
max_points = pd.DataFrame(result, columns=columns)
points = pd.pivot_table(points, columns=['club_2', 'round_id'], index=['player_1', 'chess_blitz_rating'],
values='team1_player_score') \
.reset_index()
cols = [('player_1', ''), ('chess_blitz_rating', '')] + sorted(list(points.columns)[2:], key=lambda x: x[1])
points = points.reindex(columns=cols).reset_index(drop=True)
points['Total'] = points[list(points.columns[2:])].sum(axis=1)
rivals = points.columns[2:]
points = points.sort_values(by=['Total'], ascending=False)
max_points = pd.pivot_table(max_points, columns=['club_2', 'round_id'], index=['player_1', 'chess_blitz_rating'],
values='team1_player_max_possible_score').reset_index()
max_points = max_points.reindex(columns=cols).reset_index(drop=True)
max_points['Total_max'] = max_points[list(max_points.columns[2:])].sum(axis=1)
points = pd.merge(points, max_points[['Total_max', 'player_1']], on='player_1', how='left')
points['points_percentage'] = 100*points['Total']/points['Total_max']
points = points.sort_values(by=['Total', 'points_percentage'], ascending=False)
points['Place'] = np.arange(1, len(points) + 1)
return render_template("lcwl_best_players.html", points=points, rivals=rivals)
def insert_review(r_json):
conn = engine.connect()
locationID = r_json['locationID']
traceID = r_json['traceID']
content = r_json['content']
imageURL = r_json['imageURL']
thumbnailURL = r_json['thumbnailURL']
likeNum = r_json['likeNum']
lat = r_json['lat']
lon = r_json['lon']
placeName = r_json['placeName']
writeDate = r_json['writeDate']
userImageUrl = r_json['userImageUrl']
userName = r_json['userName']
print userName
print userImageUrl
# q = ("""INSERT INTO review VALUES("%s","%s","%s","%s","%s","%d","%f","%f","%s","%f","%s","%s")""",(locationID, traceID, content, imageURL, thumbnailURL, likeNum, lat, lon, placeName, writeDate, userName, userImageUrl))
# q = "INSERT INTO review VALUES (?,?,?,?,?,?,?,?,?,?,?,?);",(locationID, traceID, content, imageURL, thumbnailURL, likeNum, lat, lon, placeName, writeDate, userName, userImageUrl)
q = "INSERT INTO review VALUES('{}','{}','{}','{}','{}',{},{},{},'{}',{},'{}','{}');".format(
locationID, traceID, content, imageURL, thumbnailURL, likeNum, lat,
lon, placeName, writeDate, userName, userImageUrl)
# q = "select * from review"
# print q
conn.execute(q)
conn.close()
return 'insert ok'
def get_audio(words):
with engine.connect() as cur:
query = select([glados.c.text, glados.c.id])
for word in words:
query = query.where(glados.c.text.contains(word))
data = cur.execute(query).fetchall()
return [(d.text, d.id) for d in data]
def create(db: Session = Depends(get_db)):
if os.name == 'nt':
ospath = "\\"
else:
ospath = "/"
with open (os.getcwd()+ospath+'IN.csv','r') as f:
reader = csv.reader(f)
columns = next(reader)
value = 2000000000
#cursor = connection.cursor()
cursor = engine.connect()
#cursor = connection.cursor()
cursor.execute("CREATE EXTENSION cube")
cursor.execute("CREATE EXTENSION earthdistance")
cursor.execute("CREATE TABLE pincode (id serial primary key, loc character varying(20) NOT NULL,address character varying(50),city character varying(50),lat double precision,lon double precision,accuracy character varying(10))")
for row in reader: # Iterate through csv
cursor.execute("INSERT INTO pincode(loc,address,city,lat,lon,accuracy) VALUES ('{}', '{}', '{}', '{}', '{}','{}')" .format(row[0], row[1], row[2], row[3] if row[3] else value, row[4] if row[4] else value,row[5] if row[5] else value))
f.close()
cursor.execute("CREATE TABLE poly(name character varying(50) primary key, parent character varying(50), cord text)")
with open (os.getcwd()+ospath+'map.json','r') as f:
ff = json.load(f)
for i in ff['features']:
#print(i['properties']['name'])
cursor.execute("INSERT INTO poly VALUES ('{}', '{}', '{}')" .format(i['properties']['name'], i['properties']['parent'], i['geometry']['coordinates']))
'''
def update_user(user_id):
if user_id != request.user.get('id'):
error = {
'error': 'Unauthorized'
}
resp = jsonify(error)
resp.status_code = 401
return resp
data = json.loads(request.get_data())
error = validate_for_user(data)
if error:
resp = jsonify(error)
resp.status_code = 400
return resp
stmt = users.update()\
.where(users.c.id == int(user_id))\
.values(username=data.get('username'), password=data.get('password'))
conn = engine.connect()
result = conn.execute(stmt)
status_code = 200
if not result.rowcount:
user_insert = users.insert()\
.values(username=data.get('username'), password=data.get('password'), id=int(user_id))
conn.execute(user_insert)
status_code = 201
conn.close()
res = {
'id': user_id
}
resp = jsonify(res)
resp.status_code = status_code
return resp
def play(id):
with engine.connect() as cur:
bytes = cur.execute(
select([glados.c.record]).where(glados.c.id == id)).scalar()
song = AudioSegment.from_file(BytesIO(bytes), format="wav")
print('playing...')
_play(song)
print('done!')
def get_review_l(lat, lon):
conn = engine.connect()
q = "SELECT * FROM review WHERE lat='{}';".format(lat)
data = conn.execute(q)
json_data = []
for data_list in data:
json_data.append(otoj(data_list))
conn.close()
json_data = {"data": json_data}
return json_data
def tu_hq_players_count_dashboard():
with engine.connect() as conn:
result = conn.execute(
"call usp_dashboards_Ukraine_vs_Russia_playersCount")
players_result = [row for row in result]
players_columns = result.keys()
players = pd.DataFrame(players_result, columns=players_columns)
return render_template('tu_hq_players_count_dashboard.html',
players=players)
def tu_best_daily_players_dashboard():
with engine.connect() as conn:
result = conn.execute("call tu_best_daily_players_dashboard")
best_players_result = [row for row in result]
best_players_columns = result.keys()
best_players = pd.DataFrame(best_players_result,
columns=best_players_columns)
return render_template('tu_best_daily_players_dashboard.html',
best_players=best_players)
async def run(bot, ctx, cmd, arg) -> None:
try:
with engine.connect() as conn:
logger.info(f'executing raw sql: "{arg}" by {ctx["user_id"]}')
res = conn.execute(arg).fetchall()
message = json.dumps([dict(r) for r in res], ensure_ascii=False)
await bot.send(ctx, message=message)
except Exception as error:
logger.error(error)
def bulkload_csv_data_to_database(engine, tablename, columns, data, sep=","):
print("Start ingesting data into postgres ...")
print("Table name: {table}".format(table=tablename))
print("CSV schema: {schema}".format(schema=columns))
conn = engine.connect().connection
cursor = conn.cursor()
cursor.copy_from(data, tablename, columns=columns, sep=sep, null='null')
conn.commit()
conn.close()
print("Finish ingesting")
def rep_clubs_membership_changes():
if not current_user.rights.can_access_to_admin_page:
abort(403)
form = ClubsMembershipChangesForm(request.form)
with engine.connect() as conn:
result = conn.execute(
"call usp_rep_clubs_membership_changes_filt_teams")
teams = [row for row in result]
form.team.choices = teams
if request.method == "GET":
form.begin_date.data = datetime.now().replace(
hour=0, minute=0, second=0) + timedelta(days=-1)
form.end_date.data = datetime.now().replace(hour=0, minute=0, second=0)
return render_template('clubs_membership_changes.html', form=form)
if request.method == 'POST':
team_id = form.team.data
begin_date = form.begin_date.data.date()
end_date = form.end_date.data.date()
if begin_date <= date(2019, 10, 2):
flash('Дані за зміною складу збираються з 3 жовтня 2019',
category='danger')
with engine.connect() as conn:
result = conn.execute(
"call usp_rep_clubs_membership_changes(%s, %s, %s)",
(team_id, begin_date, end_date))
result_table = [row for row in result]
left = [
player for player in result_table if player['player_action'] == -1
]
added = [
player for player in result_table if player['player_action'] == 1
]
return render_template('clubs_membership_changes.html',
form=form,
left=left,
added=added)
def api_token():
data = json.loads(request.get_data())
error = validate_for_user(data)
if error:
resp = jsonify(error)
resp.status_code = 400
return resp
stmt = users.select()\
.where(users.c.username == data.get('username'))\
.where(users.c.password == data.get('password'))
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
if result.rowcount:
# authenticated now, generate or fetch token
user_obj = dict(result.fetchone())
now = datetime.datetime.now()
fifteen_minutes_back = now - datetime.timedelta(minutes=15)
token_ext = token.select()\
.where(token.c.user_id == user_obj.get('id'))\
.where(token.c.last_accessed_at > fifteen_minutes_back)
conn = engine.connect()
fetch_token_result = conn.execute(token_ext)
conn.close()
if fetch_token_result.rowcount:
# token exists, just return
return jsonify({
'token': dict(fetch_token_result.fetchone()).get('token')
})
else:
# create, persist and return the token
token_string = uuid.uuid4()
token_stmt = token.insert().values(user_id=user_obj.get('id'), token=unicode(token_string))
conn = engine.connect()
conn.execute(token_stmt)
conn.close()
return jsonify({
'token': token_string
})
else:
# unauthenticated
return unauthenticated()
def get_database_size(database_name):
connection = engine.connect()
prepare_statement = text(stmt_get_size)
result = connection.execute(prepare_statement, schema=database_name)
result = result.first()
if result[0] is None:
return 0.00
return result[0]
def drop_database(name):
connection = engine.connect()
prepare_statement = stmt_drop_db % name
prepare_statement = text(prepare_statement)
drop_result = connection.execute(prepare_statement)
connection.close()
database = db_session.query(Database).get(name)
db_session.delete(database)
db_session.commit()
def get_tournaments():
try:
conn = engine.connect()
except exc.DisconnectionError:
logging.error("Error connecting to database")
return None
result = conn.execute("SELECT * FROM tournament")
tournament = []
for row in result:
tournament.append(json.dumps(row))
conn.close()
return json.dumps(tournament)
def grant_privileges(username, database):
connection = engine.connect()
prepare_statement = stmt_grant_privileges % database
prepare_statement = text(prepare_statement)
result = connection.execute(prepare_statement, username=username, database=database)
connection.close()
user = db_session.query(User).get(username)
database = db_session.query(Database).get(database)
database.privileges.append(user)
db_session.commit()
def create_user(username, password, realuser):
connection = engine.connect()
prepare_statement = text(stmt_create_user)
result = connection.execute(prepare_statement, username=username, password=password)
connection.close()
user = User()
user.name = username
user.realuser = realuser
db_session.add(user)
db_session.commit()
def create_tournament(data):
try:
conn = engine.connect()
conn.execute(
"INSERT INTO tournament ("
"tournament_id, title, date, game_title, tourney_type, rule_set) VALUES ('"
+ data['tournament_id'] + "', '" + data['title'] + "', '" +
data['date'] + "', '" + data['game_title'] + "', '" +
data['tourney_type'] + "', '" + data['rule_set'] + "');")
except exc.DisconnectionError:
logging.error("Error connecting to database")
return False
return True
def tu_hq_reg_dashboard():
with engine.connect() as conn:
result = conn.execute("call team_ukraine_reg_matches_dashboard")
reg_matches_result = [row for row in result]
reg_matches_columns = result.keys()
with engine.connect() as conn:
result = conn.execute(
"call team_ukraine_reg_matches_dashboard_timeouts")
timeouts_result = [row for row in result]
timeouts_columns = result.keys()
with engine.connect() as conn:
result = conn.execute(
"call team_ukraine_reg_matches_dashboard_mobilization")
mobilization_result = [row for row in result]
mobilization_columns = result.keys()
matches = pd.DataFrame(reg_matches_result, columns=reg_matches_columns)
timeouts = pd.DataFrame(timeouts_result, columns=timeouts_columns)
mobilization = pd.DataFrame(mobilization_result,
columns=mobilization_columns)
mobilization_ukraine = mobilization[mobilization['team_name'] ==
'Team Ukraine']
mobilization_opponent = mobilization[
mobilization['team_name'] != 'Team Ukraine']
mobilization_ukraine['avg_rating'] = mobilization_ukraine[
'avg_rating'].astype(int)
mobilization_opponent['avg_rating'] = mobilization_opponent[
'avg_rating'].astype(int)
# {{ mobilization['fulldate'].tolist() }}
return render_template('tu_hq_reg_dashboard.html',
matches=matches,
timeouts=timeouts,
mobilization_ukraine=mobilization_ukraine,
mobilization_opponent=mobilization_opponent)
def drop_user(username, realuser):
connection = engine.connect()
user = db_session.query(User).get(username)
if user.realuser != realuser:
return
prepare_statement = text(stmt_drop_user)
result = connection.execute(prepare_statement, username=username)
connection.close()
db_session.delete(user)
db_session.commit()
def create_database(name, realuser):
# let's create database in the real world
connection = engine.connect()
prepare_statement = text((stmt_create_db % name))
create_result = connection.execute(prepare_statement)
connection.close()
database = Database()
database.name = name
database.realuser = realuser
db_session.add(database)
db_session.commit()
def delete_users(user_id):
if user_id != request.user.get('id'):
error = {
'error': 'Unauthorized'
}
resp = jsonify(error)
resp.status_code = 401
return resp
stmt = users.delete().where(users.c.id == int(user_id))
conn = engine.connect()
conn.execute(stmt)
result_resp = {
'success': True,
}
resp = jsonify(result_resp)
resp.status_code = 200
return resp
def delete_post(post_id):
stmt = posts.delete()\
.where(posts.c.user_id == request.user.get('id'))\
.where(posts.c.id == int(post_id))
conn = engine.connect()
result = conn.execute(stmt)
conn.close()
if result.rowcount:
res = {
'success': True
}
return jsonify(res)
error = {
'error': u'No post with id {} found for User {}'.format(post_id, request.user.get('username'))
}
resp = jsonify(error)
resp.status_code = 404
return resp
def create_post():
data = json.loads(request.get_data())
error = validate_data_for_create_post(data)
if error:
resp = jsonify(error)
resp.status_code = 400
return resp
post_insert = posts.insert()\
.values(title=data.get('title'), description=data.get('description'), user_id=request.user.get('id'))
conn = engine.connect()
result = conn.execute(post_insert)
conn.close()
res = {
'id': result.inserted_primary_key
}
resp = jsonify(res)
resp.status_code = 201
return resp
def profile():
if 'email' in session:
print (str(session['email']),'is on Profile')
else:
print('Guest is on Profile ... and this should never happen')
usr = str(session['email'])
#transactions by session user
transactions = db_session.query(Transactions).filter(Transactions.email==usr).all()
#accounts by session user
accounts = db_session.query(Accounts).filter(Accounts.email==usr).all()
#categories by session user
categories = db_session.query(Categories).filter(Categories.email==usr).all()
cat_cnt = db_session.query(func.count(Categories.id)).filter(Categories.email==usr)
#progress by session user
con=engine.connect()
progress = con.execute("select transactions.category, sum(transactions.amount) as sum, goals.goal, ((sum(transactions.amount)/goals.goal )*100) as progress from transactions, goals where transactions.category=goals.category and transactions.email=:param group by transactions.category", {"param":session['email']} )
return render_template('profile.html', progress=progress)
def parent_and_run_child_urls(url, depth, max_depth):
try:
url_visited[url] = True
opened_url = urlopen(url)
url_insert = urls.insert()\
.values(url=url, code=u'200')
conn = engine.connect()
conn.execute(url_insert)
conn.close()
try:
page = opened_url.read()
except httplib.IncompleteRead, e:
page = e.partial
soup = BeautifulSoup(page, 'html.parser')
links = soup.find_all('a')
threads = list()
child_depth = int(depth) + int(1)
print child_depth
for link in links:
if depth <= max_depth and len(url_visited.keys()) < 10000:
end_point = link.get('href')
effective_end_point = get_effective_end_point(end_point)
if effective_end_point and effective_end_point.startswith('/'):
if not url.endswith('/'):
child_url = url + effective_end_point
else:
child_url = url[:-1] + effective_end_point
else:
child_url = effective_end_point
if child_url and not url_visited.get(child_url, False):
print "parent: ", url, " child: ", child_url
threads.append(gevent.spawn(parent_and_run_child_urls, child_url, child_depth, max_depth))
else:
pass
if threads:
gevent.joinall(threads)
def delete_trans(id):
con=engine.connect()
con.execute('delete from transactions where id =:id', {"id":id} )
flash('The transaction was deleted.')
print (str(session['email']), 'deleted transaction ID: ', id)
return redirect(url_for('home'))
child_url = url + effective_end_point
else:
child_url = url[:-1] + effective_end_point
else:
child_url = effective_end_point
if child_url and not url_visited.get(child_url, False):
print "parent: ", url, " child: ", child_url
threads.append(gevent.spawn(parent_and_run_child_urls, child_url, child_depth, max_depth))
else:
pass
if threads:
gevent.joinall(threads)
except HTTPError as e:
url_insert = urls.insert()\
.values(url=url, code=unicode(e.reason))
conn = engine.connect()
conn.execute(url_insert)
conn.close()
except URLError as e:
url_insert = urls.insert()\
.values(url=url, code=unicode(e.reason))
conn = engine.connect()
conn.execute(url_insert)
conn.close()
except Exception as e:
print e
@app.route("/")
def new_dfs_based_crawling():
url = 'http://askme.com'
def delete_acct(id):
con=engine.connect()
con.execute('delete from accounts where id =:id', {"id":id} )
flash('The account was deleted.')
print (str(session['email']), 'deleted account ID: ', id)
return redirect(url_for('home'))
def delete_cat(id):
con=engine.connect()
con.execute('delete from categories where id =:id', {"id":id} )
flash('The category was deleted.')
print (str(session['email']), 'deleted category ID: ',id)
return redirect(url_for('home'))
def delete_goal(id):
con=engine.connect()
con.execute('delete from goals where id =:id', {"id":id} )
flash('The goal was deleted.')
print (str(session['email']), 'deleted goal ID: ',id)
return redirect(url_for('home'))
