def task_view(task_id): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT tasks.id, tasks.user_id, tasks.task_id, tasks.status, results.result, tasks.created_at, tasks.modified_at, tasks.function_id, functions.function_name, tasks.endpoint_id, sites.endpoint_name, username " "FROM tasks, results, sites, functions, users " "WHERE results.task_id = tasks.task_id AND sites.endpoint_uuid = tasks.endpoint_id AND functions.function_uuid = tasks.function_id AND tasks.task_id = %s AND cast(tasks.user_id as integer) = users.id " "AND function_id IS NOT NULL;", (task_id, )) task = cur.fetchone() if task == None: return render_template('error.html', user=session.get('name'), title='404 Not Found') if task['username'] != session.get('username'): return render_template('error.html', user=session.get('name'), title='403 Forbidden') name = task['task_id'] except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.tasks')) return render_template('task_view.html', user=session.get('name'), title='View Task', task=task)
def home(): if 'username' not in session: return redirect(url_for('auth_api.login')) stats = [0 for i in range(3)] try: conn, cur = get_db_connection() cur.execute( "SELECT count(functions.id) FROM functions, users WHERE functions.user_id = users.id AND users.username = %s", (session.get("username"), )) executions = cur.fetchone() stats[0] = num_delimiter(executions['count'], "int") cur.execute( "SELECT tasks.created_at, tasks.modified_at FROM tasks, users WHERE cast(tasks.user_id as integer) = users.id AND users.username = %s", (session.get("username"), )) tasks = cur.fetchall() stats[1] = num_delimiter(len(tasks), "int") if len(tasks) != 0: times = list() for task in tasks: times.append(task['modified_at'] - task['created_at']) count = timedelta(hours=0) for time in times: count += time stats[2] = num_delimiter( round((count.total_seconds() / 3600.0), 2), "decimal") except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.start')) return render_template('home.html', user=session.get('name'), title='Home', stats=stats)
def load_op_return(axioma_ids, table_name): day_counts = [1, 5, 10, 15, 21, 42, 63, 126] columns = ["date", "axioma_id"] for n in day_counts: for ret_type in ["total", "idio", "sector"]: columns.append(f"{ret_type}_{n}d_fwd") errors = [] for id in axioma_ids: try: print(id) df = pd.read_sql( f""" select axioma_id,date,total_return,factor_return,idio_return,sector_return from op_raw_return where axioma_id = '{id}' """, utils.get_db_connection(), ) for n in day_counts: for ret_type in ["total"]: col_name = f"{ret_type}_{n}d_fwd" df[col_name] = ( (1 + df[[f"{ret_type}_return"]]).pct_change(n).shift(-n)) for ret_type in ["idio", "sector"]: col_name = f"{ret_type}_{n}d_fwd" total_col_name = f"total_return" df[col_name] = (df.shift(-n)[f"{ret_type}_return"] - df[f"{ret_type}_return"]) / ( 1 + df[total_col_name]) df = df.rename( columns={ "total_return": "total_return_index", "factor_return": "factor_return_index", "idio_return": "idio_return_index", "sector_return": "sector_return_index", }) df = df.replace([np.inf, -np.inf], np.nan) df.to_sql(table_name, utils.get_db_connection(), if_exists="append", index=False) except Exception as e: errors.append((e, id)) return errors
def endpoint_view(endpoint_uuid): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT sites.id, sites.user_id, sites.created_at, status, endpoint_name, endpoint_uuid, public, sites.deleted, username " "FROM sites, users " "WHERE sites.endpoint_uuid = %s " "AND users.id = sites.user_id " "AND endpoint_name IS NOT NULL AND sites.deleted = 'f';", (endpoint_uuid, )) endpoint = cur.fetchone() if endpoint == None: return render_template('error.html', user=session.get('name'), title='404 Not Found') if endpoint['username'] != session.get( 'username') and endpoint['public'] is False: return render_template('error.html', user=session.get('name'), title='403 Forbidden') name = endpoint['endpoint_name'] except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.endpoints')) delete_form = DeleteForm() if delete_form.validate_on_submit() and delete_form.delete.data: json = {'endpoint': endpoint['endpoint_uuid']} tokens = session.get("tokens") funcx_tokens = tokens['funcx_service'] access_token = "Bearer " + funcx_tokens['access_token'] response = requests.post("http://dev.funcx.org/api/v1/delete_endpoint", headers={"Authorization": access_token}, json=json) result = response.json()['result'] if result == 302: flash(f'Deleted Endpoint "{name}".', 'success') return redirect(url_for('guiapi.endpoints')) elif result == 403: return render_template('error.html', user=session.get('name'), title='403 Forbidden') elif result == 404: return render_template('error.html', user=session.get('name'), title='404 Not Found') else: flash('There was an issue handling your request.', 'danger') # return redirect(url_for('guiapi.endpoints')) return render_template('endpoint_view.html', user=session.get('name'), title=f'View "{name}"', endpoint=endpoint, delete_form=delete_form)
def winsorize_op_return(table_name): columns = [ "total_0d_fwd", "idio_0d_fwd", "sector_0d_fwd", "idio_and_sector_0d_fwd", "total_1d_fwd", "idio_1d_fwd", "sector_1d_fwd", "idio_and_sector_1d_fwd", "total_5d_fwd", "idio_5d_fwd", "sector_5d_fwd", "idio_and_sector_5d_fwd", "total_10d_fwd", "idio_10d_fwd", "sector_10d_fwd", "idio_and_sector_10d_fwd", "total_15d_fwd", "idio_15d_fwd", "sector_15d_fwd", "idio_and_sector_15d_fwd", "total_21d_fwd", "idio_21d_fwd", "sector_21d_fwd", "idio_and_sector_21d_fwd", "total_42d_fwd", "idio_42d_fwd", "sector_42d_fwd", "idio_and_sector_42d_fwd", "total_63d_fwd", "idio_63d_fwd", "sector_63d_fwd", "idio_and_sector_63d_fwd", "total_126d_fwd", "idio_126d_fwd", "sector_126d_fwd", "idio_and_sector_126d_fwd", ] connection = utils.get_db_connection() connection.execute("truncate table winsorize_return") connection.execute( f"insert into winsorize_return (date) select distinct date from {table_name}" ) for col_name in columns: print("building winsorize_return ", col_name) query = get_winsorize_sql(col_name, table_name) connection.execute(query) for col_name in columns: print("winsorizing ", col_name) connection.execute(get_ceiling(col_name, table_name)) connection.execute(get_floor(col_name, table_name))
def authorize_function(user_id, function_uuid, token): """Determine whether or not the user is allowed to access this function. This is done in two steps: first, check if the user owns the function. If not, check if there are any groups associated with the function and determine if the user is a member of any of them. Parameters ---------- user_id : str The primary identity of the user function_uuid : str The uuid of the function token : str The auth token Returns ------- bool Whether or not the user is allowed access to the function """ authorized = False try: conn, cur = get_db_connection() # Check if the user owns the endpoint query = "select * from functions where function_uuid = %s" cur.execute(query, (function_uuid, )) row = cur.fetchone() app.logger.debug(f"Endpoint auth row: {row}") if len(row) > 0: # Check if the user owns it if row['user_id'] == user_id: authorized = True elif row['public']: authorized = True if not authorized: # Check if there are any groups associated with this function query = "select * from function_auth_groups where function_id = %s" cur.execute(query, (function_uuid, )) rows = cur.fetchall() endpoint_groups = [] for row in rows: endpoint_groups.append(row['group_id']) if len(endpoint_groups) > 0: authorized = check_group_membership(token, endpoint_groups) except Exception as e: print(e) app.logger.error(e) return authorized
def update_op_return(table_name): command = f""" drop table if exists #map drop table if exists #op_return --Take the max sedol arbitrarily. select map.axioma_id, max(map.sedol) sedol into #map from sedol_map map group by map.axioma_id create clustered index map_idx on #map (sedol) update {table_name} set idio_and_sector_return_index = (idio_return_index + sector_return_index), idio_and_sector_0d_fwd = (sector_0d_fwd + idio_0d_fwd), idio_and_sector_1d_fwd = (sector_1d_fwd + idio_1d_fwd), idio_and_sector_5d_fwd = (sector_5d_fwd + idio_5d_fwd), idio_and_sector_10d_fwd = (sector_10d_fwd + idio_10d_fwd), idio_and_sector_15d_fwd = (sector_15d_fwd + idio_15d_fwd), idio_and_sector_21d_fwd = (sector_21d_fwd + idio_21d_fwd), idio_and_sector_42d_fwd = (sector_42d_fwd + idio_42d_fwd), idio_and_sector_63d_fwd = (sector_63d_fwd + idio_63d_fwd), idio_and_sector_126d_fwd = (sector_126d_fwd + idio_126d_fwd) from {table_name} select ret.date, ret.axioma_id, map.sedol, LAG(total_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) total_0d_fwd, LAG(idio_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) idio_0d_fwd, LAG(sector_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) sector_0d_fwd, LAG(idio_and_sector_1d_fwd) OVER(partition by ret.axioma_id order by ret.date) idio_and_sector_0d_fwd into #op_return from {table_name} ret join #map map on map.axioma_id = ret.axioma_id update ret set ret.sedol = temp.sedol, ret.total_0d_fwd = temp.total_0d_fwd, ret.idio_0d_fwd = temp.idio_0d_fwd, ret.sector_0d_fwd = temp.sector_0d_fwd, ret.idio_and_sector_0d_fwd = temp.idio_and_sector_0d_fwd from {table_name} ret join #op_return temp on temp.axioma_id = ret.axioma_id and temp.date = ret.date """ connection = utils.get_db_connection() connection.execute(command)
def endpoints(): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT DISTINCT sites.user_id, endpoint_name, endpoint_uuid, status, sites.created_at, public FROM sites, users WHERE ((sites.user_id = users.id AND users.username = %s AND sites.public = 'f') OR (sites.public = 't')) AND sites.deleted = 'f' AND endpoint_uuid is not null order by created_at desc;", (session.get("username"), )) endpoints = cur.fetchall() endpoints_total = len(endpoints) private_endpoints = list() private_endpoints_total = 0 private_endpoints_online_total = 0 public_endpoints = list() public_endpoints_total = 0 public_endpoints_online_total = 0 for endpoint in endpoints: if endpoint['public'] is False: private_endpoints_total += 1 if endpoint['status'] == "ONLINE": private_endpoints_online_total += 1 else: public_endpoints_total += 1 if endpoint['status'] == "ONLINE": public_endpoints_online_total += 1 private_endpoints.append(private_endpoints_total) private_endpoints.append(private_endpoints_online_total) private_endpoints.append(private_endpoints_total - private_endpoints_online_total) public_endpoints.append(public_endpoints_total) public_endpoints.append(public_endpoints_online_total) public_endpoints.append(public_endpoints_total - public_endpoints_online_total) numPages = ceil(endpoints_total / 30) except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.home')) return render_template('endpoints.html', user=session.get('name'), title='Endpoints', endpoints=endpoints, endpoints_total=endpoints_total, private_endpoints=private_endpoints, public_endpoints=public_endpoints, numPages=numPages)
def function_tasks(uuid): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT function_uuid, function_name, username FROM functions, users WHERE function_uuid = %s AND functions.user_id = users.id", (uuid, )) func = cur.fetchone() if func == None: return render_template('error.html', user=session.get('name'), title='404 Not Found') if func['username'] != session.get('username'): return render_template('error.html', user=session.get('name'), title='403 Forbidden') func_name = func['function_name'] cur.execute( "SELECT task_id, cast(tasks.user_id as integer), tasks.function_id, functions.function_name, tasks.status, tasks.created_at, tasks.endpoint_id, sites.endpoint_name " "FROM tasks, sites, users, functions " "WHERE tasks.endpoint_id = sites.endpoint_uuid AND cast(tasks.user_id as integer) = users.id AND tasks.function_id = functions.function_uuid " "AND tasks.function_id = %s" "ORDER by tasks.task_id desc", (uuid, )) func_tasks = cur.fetchall() tasks_total = len(func_tasks) except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.function_view', func=func)) try: numPages = ceil(tasks_total / 30) except: return render_template('function_tasks.html', user=session.get('name'), title=f'Tasks of "{func_name}"') return render_template('function_tasks.html', user=session.get('name'), title=f'Tasks of "{func_name}"', func_tasks=func_tasks, tasks_total=tasks_total, func=func, numPages=numPages)
def load_op_raw_return(axioma_ids, start_date, end_date): errors = [] for id in axioma_ids: try: df_ret = op.get_stock_returns("model_provider_id", [id], start_date, end_date) df_ret = df_ret.rename( columns={ "model_provider_id": "axioma_id", "specific_return": "idio_return", }) df_ret.to_sql( "op_raw_return", utils.get_db_connection(), if_exists="append", index=False, ) except Exception as e: errors.append((e, id)) return errors
def functions(): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT function_name, timestamp, modified_at, function_uuid FROM functions, users WHERE functions.user_id = users.id AND users.username = %s AND functions.deleted = False ORDER BY functions.id desc", (session.get("username"), )) functions = cur.fetchall() functions_total = len(functions) numPages = ceil(functions_total / 30) except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.home')) return render_template('functions.html', user=session.get('name'), title='Your Functions', functions=functions, functions_total=functions_total, numPages=numPages)
def tasks(): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT tasks.id, users.id, cast(tasks.user_id as integer), tasks.task_id, results.result, tasks.status, tasks.function_id, functions.function_name, tasks.endpoint_id, sites.endpoint_name " "FROM results, tasks, users, functions, sites " "WHERE results.task_id = tasks.task_id AND users.id = cast(tasks.user_id as integer) AND sites.endpoint_uuid = tasks.endpoint_id AND functions.function_uuid = tasks.function_id " "AND function_id IS NOT NULL AND users.username = %s " "ORDER by tasks.id desc", (session.get("username"), )) tasks = cur.fetchall() tasks_total = len(tasks) numPages = ceil(tasks_total / 30) except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.home')) return render_template('tasks.html', user=session.get('name'), title='Tasks', tasks=tasks, tasks_total=tasks_total, numPages=numPages)
def authorize_endpoint(user_id, endpoint_uuid, function_uuid, token): """Determine whether or not the user is allowed to access this endpoint. This is done in two steps: first, check if the user owns the endpoint. If not, check if there are any groups associated with the endpoint and determine if the user is a member of any of them. Parameters ---------- user_id : str The primary identity of the user endpoint_uuid : str The uuid of the endpoint function_uuid : str The uuid of the function token : str The auth token Returns ------- bool Whether or not the user is allowed access to the endpoint """ authorized = False try: conn, cur = get_db_connection() # Check if the user owns the endpoint query = "select * from sites where endpoint_uuid = %s" cur.execute(query, (endpoint_uuid, )) row = cur.fetchone() app.logger.debug(f"Endpoint auth row: {row}") # If the endpoint is flagged as protected we need to overwrite if len(row) > 0: # If it is restricted we need to check the function is allowed, otherwise nothing else matters if row['restricted']: app.logger.debug( "Restricted endpoint, checking function is allowed.") query = "select * from restricted_endpoint_functions where endpoint_id = %s and function_id = %s" cur.execute(query, (endpoint_uuid, function_uuid)) funcs = cur.fetchall() app.logger.debug(f"Length of query response: {len(funcs)}") if len(funcs) == 0: # There is no entry of this function, so reject it. raise Exception( f"Function {function_uuid} not permitted on endpoint {endpoint_uuid}" ) # Check if the user owns it if row['user_id'] == user_id: authorized = True # Otherwise if the row is public elif row['public']: authorized = True if not authorized: # Check if there are any groups associated with this endpoint query = "select * from auth_groups where endpoint_id = %s" cur.execute(query, (endpoint_uuid, )) rows = cur.fetchall() endpoint_groups = [] for row in rows: endpoint_groups.append(row['group_id']) if len(endpoint_groups) > 0: authorized = check_group_membership(token, endpoint_groups) except Exception as e: print(e) app.logger.error(e) return authorized
connection.execute(get_ceiling(col_name, table_name)) connection.execute(get_floor(col_name, table_name)) if __name__ == "main": # Note to get winsorization to work properly, first run: # truncate table op_return # Change this to op_return_test to test. table_name = "op_return" missing_ids = pd.read_sql( """ select distinct axioma_id from sedol_map map where not exists (select * from op_raw_return ret where ret.axioma_id = map.axioma_id) """, utils.get_db_connection(), ).axioma_id.values errors_raw = load_op_raw_return(missing_ids, date(2012, 1, 1), date(2019, 12, 31)) # errors_raw = load_op_raw_return(['11D7GQAK9'], date(2012,1,1), date(2019,12,31)) print(errors_raw) missing_op_return_ids = pd.read_sql( """ select distinct axioma_id from op_raw_return raw where not exists (select * from op_return ret where ret.axioma_id = raw.axioma_id) """, utils.get_db_connection(), ).axioma_id.values errors_op_return = load_op_return(missing_op_return_ids, table_name)
def function_view(uuid): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT function_name, description, entry_point, users.id, username, timestamp, modified_at, function_uuid, status, function_code FROM functions, users WHERE function_uuid = %s AND functions.user_id = users.id AND functions.deleted = False", (uuid, )) func = cur.fetchone() if func == None: return render_template('error.html', user=session.get('name'), title='404 Not Found') if func['username'] != session.get('username'): return render_template('error.html', user=session.get('name'), title='403 Forbidden') name = func['function_name'] user_id = func['id'] except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.functions')) execute_form = ExecuteForm() cur.execute( "SELECT DISTINCT endpoint_name, endpoint_uuid FROM sites WHERE endpoint_uuid IS NOT NULL AND ((user_id = %s AND sites.public = false) OR (sites.public = true));", (user_id, )) endpoints = cur.fetchall() endpoint_uuids = list() for endpoint in endpoints: endpoint_uuids.append( (endpoint['endpoint_uuid'], endpoint['endpoint_name'])) execute_form.endpoint.choices = endpoint_uuids if execute_form.validate_on_submit() and execute_form.submit.data: json = { 'func': func['function_uuid'], 'endpoint': execute_form.endpoint.data, 'data': execute_form.data.data } tokens = session.get("tokens") funcx_tokens = tokens['funcx_service'] access_token = "Bearer " + funcx_tokens['access_token'] print("Sending Request") response = requests.post("http://funcx.org/api/v1/execute", headers={"Authorization": access_token}, json=json) task_id = response.json()['task_id'] time.sleep(1) return redirect(url_for('guiapi.task_view', task_id=task_id)) else: print(execute_form.validate_on_submit()) print(execute_form.submit.data) print("nope") delete_form = DeleteForm() if delete_form.validate_on_submit() and delete_form.delete.data: print("Delete:" + str(delete_form.validate_on_submit())) print("Delete:" + str(delete_form.delete.data)) json = {'func': func['function_uuid']} tokens = session.get("tokens") funcx_tokens = tokens['funcx_service'] access_token = "Bearer " + funcx_tokens['access_token'] response = requests.post("http://dev.funcx.org/api/v1/delete_function", headers={"Authorization": access_token}, json=json) result = response.json()['result'] if result == 302: flash(f'Deleted Function "{name}".', 'success') return redirect(url_for('guiapi.functions')) elif result == 403: return render_template('error.html', user=session.get('name'), title='403 Forbidden') elif result == 404: return render_template('error.html', user=session.get('name'), title='404 Not Found') else: flash('There was an issue handling your request.', 'danger') # return redirect(url_for('guiapi.functions')) return render_template('function_view.html', user=session.get('name'), title=f'View "{name}"', func=func, execute_form=execute_form, delete_form=delete_form)
def function_edit(uuid): if 'username' not in session: return redirect(url_for('auth_api.login')) try: conn, cur = get_db_connection() cur.execute( "SELECT function_name, description, entry_point, username, timestamp, modified_at, function_uuid, status, function_code FROM functions, users WHERE function_uuid = %s AND functions.user_id = users.id", (uuid, )) func = cur.fetchone() if func == None: return render_template('error.html', user=session.get('name'), title='404 Not Found') if func['username'] != session.get('username'): return render_template('error.html', user=session.get('name'), title='403 Forbidden') except: flash('There was an issue handling your request.', 'danger') return redirect(url_for('guiapi.function_view', uuid=uuid)) name = func['function_name'] form = EditForm() if form.validate_on_submit(): json = { 'func': func['function_uuid'], 'name': form.name.data, 'desc': form.desc.data, 'entry_point': form.entry_point.data, 'code': form.code.data } tokens = session.get("tokens") funcx_tokens = tokens['funcx_service'] access_token = "Bearer " + funcx_tokens['access_token'] response = requests.post("http://dev.funcx.org/api/v1/upd_function", headers={"Authorization": access_token}, json=json) result = response.json()['result'] if result == 302: flash(f'Saved Function "{form.name.data}"!', 'success') return redirect(url_for('guiapi.function_view', uuid=uuid)) elif result == 403: return render_template('error.html', user=session.get('name'), title='403 Forbidden') elif result == 404: return render_template('error.html', user=session.get('name'), title='404 Not Found') else: flash('There was an issue handling your request.', 'danger') # return redirect(url_for('guiapi.function_view', uuid=uuid)) form.name.data = func['function_name'] form.desc.data = func['description'] form.entry_point.data = func['entry_point'] form.code.data = func['function_code'] return render_template('function_edit.html', user=session.get('name'), title=f'Edit "{name}"', func=func, form=form, cancel_route="view")