def update_groups( query , session ): if query.notasi_query: notasi_query = pandas.read_sql(query.notasi_query, db_session.notasi_engine()).to_dict('records') for group_category in set(d["group_category"] for d in notasi_query): category = session.query(GroupCategory).filter_by(name=group_category).first() if not category: category = GroupCategory() session.add(category) category.name = group_category for group in set([d["group_name"] for d in notasi_query if d['group_category'] == group_category]): stored_group = session.query(Group) \ .join(GroupCategory) \ .filter(Group.name==group) \ .filter(GroupCategory.name==group_category) \ .first() if not stored_group: stored_group = Group() session.add(stored_group) stored_group.name = group stored_group.group_category_id = category.id return []
def ldap_select(query): location = query.location dc = 'dc=' + ',dc='.join(split_strip(location.database, ".")) if location.subtype.name == 'Active Directory': auth = NTLM else: auth = SIMPLE server = Server(location.address, get_info=ALL) conn = Connection(server, 'cn=' + location.username + ',' + dc, location.password, auto_bind=True, authentication=auth) formatted_queries = [] if query.notasi_query: notasi_query = pandas.read_sql( query.notasi_query, db_session.notasi_engine()).to_dict('records') for row in notasi_query: key_list = list(row.keys()) formatted_query = query.body for key in key_list: formatted_query = formatted_query.replace( '{' + str(key) + '}', str(row[key])) formatted_queries.append(formatted_query) if not formatted_queries: formatted_queries.append(query.body) data = [] for formatted_query in formatted_queries: conn.search(dc, formatted_query, attributes=[ALL_ATTRIBUTES]) for entry in conn.entries: dict1 = json.loads(entry.entry_to_json())["attributes"] dict1['dn'] = json.loads(entry.entry_to_json())["dn"] data.append(dict1) data = flatten_json(data) return (data)
def run(category: str, endpoint_location: str): session = db_session.create_session() endpoint = session.query(Endpoint) \ .filter_by(endpoint_location=endpoint_location, category=category)\ .first() session.close() key = flask.request.headers.get('key') if not check_password_hash(endpoint.key, key): return "Unauthorised", 401 data = flask.request.data.decode('utf-8') args = flask.request.args.to_dict() data_dict = dict() if data: data_dict = json.loads(data) else: for key in args: data_dict[key] = args[key] notasi_query_string = "select * from (" \ + endpoint.notasi_query \ +") as tab1" where_list = [] for key in data_dict: where_list.append("lower(" + key + ") = lower(:" + key +")") if where_list: notasi_query_string = notasi_query_string + " where " + " AND ".join(where_list) t = text(notasi_query_string) try: notasi_query = pandas.read_sql(t, db_session.notasi_engine(), params=data_dict).to_dict('records') return (json.dumps(notasi_query, default=default)) except Exception as error: return str(error)
def sql_select(query): location = query.location connection_string = location.subtype.dialect + \ "://" + location.username + \ (":" + location.password if location.password else "") + \ "@" + location.address + \ ":" + location.port + \ ("/" + location.database if location.database else "") + \ ("?driver=ODBC+DRIVER+17+for+SQL+Server" if location.subtype.dialect == 'mssql+pyodbc' else "") print(connection_string) location_engine = sa.create_engine(connection_string) location_connection = location_engine.connect() formatted_queries = [] if query.notasi_query: notasi_query = pandas.read_sql( query.notasi_query, db_session.notasi_engine()).to_dict('records') for row in notasi_query: key_list = list(row.keys()) formatted_query = query.body for key in key_list: formatted_query = formatted_query.replace( '{' + str(key) + '}', str(row[key])) formatted_queries.append(formatted_query) if not formatted_queries: formatted_queries.append(query.body) data = [] for formatted_query in formatted_queries: df = pandas.read_sql_query(formatted_query, location_engine) data.append(df) data = json.loads( json.dumps(pandas.concat(data).to_dict('records'), default=default)) return flatten_json(data)
def update_users( query , session ): if query.notasi_query: notasi_query = pandas.read_sql(query.notasi_query, db_session.notasi_engine()).to_dict('records') for row in notasi_query: user = session.query(User).filter_by(username=row["username"]).first() if user: if user.name !=row["name"]: user.name =row["name"] else: user = User() session.add(user) user.username = row["username"] user.name = row["name"] response = [{"job":"users added"}] return flatten_json(response)
def index(): schedules = pandas.read_sql("select * from apscheduler_jobs", db_session.notasi_engine()).to_dict('records') print(schedules) return flask.render_template('schedule/index.html', schedules=schedules)
def run_chart(id, session): chart = session.query(Chart) \ .filter_by(id=id)\ .first() chart_type = chart.chart_type.chart_type user_groups = session.query(UserGroup) \ .filter_by(user_id=current_user.id)\ .all() ####### COLUMN ACCESS ############ groups = [] for user_group in user_groups: groups.append(user_group.group.group_category.name + '.' + user_group.group.name) sql_cols = [] if chart.access_columns.strip(): cols = split_strip(chart.access_columns, ",") for col in cols: q = col + " in ('" + "','".join(groups) + "')" sql_cols.append(q) sql_cols_concat = " OR ".join(sql_cols) query = chart.notasi_query if sql_cols_concat: query = "select * from (" + query + ") as tab1 where " + sql_cols_concat ####### COLUMN GROUPS ############ if chart.access_groups.strip(): access_groups = split_strip(chart.access_groups, ",") match = set(groups) & set(access_groups) if not match and not sql_cols: abort(401) ###########GET DATA ############## notasi_query = pandas.read_sql( query, db_session.notasi_engine()).to_dict('records') ####### COLORS ############ ###########SPLIT CHART ON COL ############## charts = [] result_list = [] if chart.page_column: result = collections.defaultdict(list) for d in notasi_query: result[d[chart.page_column]].append(d) result_list = list(result.values()) else: result_list.append(notasi_query) for l in result_list: value_sets = [] for col in split_strip(chart.value_columns, ","): value_sets.append([d[col] for d in l]) color_palettes = [] random_palette = generate_color_palette(len(l)) random.shuffle(random_palette) if not chart.color_columns: for value_set in value_sets: color_palettes.append(random_palette) else: for col in split_strip(chart.color_columns, ","): if col.lower() == 'random': color_palettes.append(random_palette) else: raw_palette = [d[col] for d in l] rgb_palette = [] for color in raw_palette: if color.startswith("#"): rgb_palette.append(",".join([ str(int(color.strip("#")[i:i + 2], 16)) for i in (0, 2, 4) ])) else: rgb_palette.append(color) color_palettes.append(rgb_palette) charts.append({"page": l[0].get(chart.page_column), \ "chart_type": chart_type, \ "color_palettes": color_palettes, \ "value_sets": value_sets, \ "x_categories": [d[chart.x_categories] for d in l], \ "dataset_legends": split_strip(chart.dataset_legends, ","), \ "chart": chart, \ "options": chart.options, \ "id": chart.id, \ "name": chart.name}) return (charts)
def http_select(query): url_string = query.location.address + query.endpoint headers_string = query.head if query.head else '' data_string = query.body if query.body else '' request_vars = [] responses = [] if query.notasi_query: notasi_query = pandas.read_sql( query.notasi_query, db_session.notasi_engine()).to_dict('records') for row in notasi_query: key_list = list(row.keys()) url = url_string headers = headers_string data = data_string for key in key_list: if url: url = url.replace('{' + str(key) + '}', str(row[key])) if headers: headers = headers.replace('{' + str(key) + '}', str(row[key])) if data: data = data.replace('{' + str(key) + '}', str(row[key])) if headers: headers = json.loads(headers) if data: data = json.loads(data) request_vars.append({'url': url, 'headers': headers, 'data': data}) else: if headers_string: headers = json.loads(headers_string) else: header = [] if data_string: data = json.loads(data_string) else: data = [] request_vars.append({ 'url': url_string, 'headers': headers, 'data': data }) if query.request_method.name == "GET": for request_var in request_vars: response = requests.get(url=request_var['url'], headers=request_var['headers'], data=request_var['data']).json() if type(response) == dict: responses.append( flatten_json(pandas.DataFrame(response, index=[0]))) else: responses.append(flatten_json(pandas.DataFrame(response))) elif query.request_method.name == "POST": for request_var in request_vars: response = requests.post(url=request_var['url'], headers=request_var['headers'], data=request_var['data']).json() responses.append(pandas.DataFrame(response, index=[0])) result = [item for sublist in responses for item in sublist] return result
def update_user_groups( query , session ): if query.notasi_query: notasi_query = pandas.read_sql(query.notasi_query, db_session.notasi_engine()).to_dict('records') group_categories = [] for row in notasi_query: if row["group_category"] and row["group_category"] not in group_categories: group_categories.append(row["group_category"]) existing_row = session.query(UserGroup) \ .join(Group) \ .join(GroupCategory) \ .join(User) \ .filter(Group.name==row["group_name"]) \ .filter(GroupCategory.name==row["group_category"]) \ .filter(User.username==row["username"]) \ .first() existing_group = session.query(Group).filter(GroupCategory.name==row["group_category"]).filter(Group.name==row["group_name"]).first() existing_user = session.query(User).filter(User.username==row["username"]).first() if not existing_row and existing_group and existing_user: new_row = UserGroup() session.add(new_row) new_row.group_id = existing_group.id new_row.user_id = existing_user.id session.commit() for group_category in group_categories: existing_rows = session.query(UserGroup) \ .join(Group) \ .join(GroupCategory) \ .join(User) \ .filter(GroupCategory.name==row["group_category"]) \ .all() for existing_row in existing_rows: if {"username": existing_row.user.username , "group_category": existing_row.group.group_category.name , "group_name": existing_row.group.name} not in notasi_query: session.delete(existing_row) session.close() return []