def get_class_of_ips(name): connection = get_connection() cursor = connection.cursor(cursor_factory=RealDictCursor) query = 'select * from class_of_ips where name = {name}'.format( name=surround_with_quotes(name), ) cursor.execute(query) return cursor.fetchall()
def set_up(): os.environ['IS_TEST'] = 'True' print("setting up database stuff...") connection = get_connection() connection.autocommit = True global db_cursor db_cursor = connection.cursor() cursor = db_cursor cursor.execute("CREATE SCHEMA IF NOT EXISTS test") cursor.execute("SET search_path TO test") cursor.execute("DROP TABLE IF EXISTS ip_traffic;") cursor.execute(""" create table ip_traffic ( event_type varchar(255), ip_src varchar(255), ip_dst varchar(255), port_src varchar(255), port_dst varchar(255), timestamp_start timestamp, timestamp_end timestamp, packets integer, bytes integer, writer_id varchar(255), mac_src varchar(255), mac_dst varchar(255), ip_proto varchar(255), src_hostname varchar(255), dst_hostname varchar(255), incoming_outgoing varchar(255) ); """) print("Finished setup!")
def delete_class_of_ips(name): connection = get_connection() connection.autocommit = True cursor = connection.cursor(cursor_factory=RealDictCursor) query = 'delete from class_of_ips where name = {name}'.format( name=surround_with_quotes(name), ) print(query) cursor.execute(query)
def test_after_performance_test_execution(self): os.environ['IS_TEST'] = 'True' print("setting up database stuff...") self.connection = get_connection() self.cursor = self.connection.cursor() self.cursor.execute("SELECT COUNT(1) FROM ip_traffic") result = self.cursor.fetchall() self.assertEqual(result[0][0], HOW_MANY_RECORDS_FOR_TEST)
def get_all_data(): connection = get_connection() cursor = connection.cursor(cursor_factory=RealDictCursor) query = """SELECT * FROM {0};""".format(TABLE_NAME) cursor.execute(query) return cursor.fetchall()
def get_all_data_paginated(page, limit): connection = get_connection() cursor = connection.cursor(cursor_factory=RealDictCursor) query = """SELECT * FROM {0} OFFSET %s LIMIT %s;""".format(TABLE_NAME) cursor.execute(query, (page * limit, limit)) return cursor.fetchall()
def edit_class_of_ips(name, ips): connection = get_connection() connection.autocommit = True cursor = connection.cursor(cursor_factory=RealDictCursor) query = 'update class_of_ips set ips = {ips} where name = {name}'.format( name=surround_with_quotes(name), ips=surround_with_quotes(list_to_pgarray(ips))) print(query) cursor.execute(query)
def insert_class_of_ips(name, ips): connection = get_connection() connection.autocommit = True cursor = connection.cursor(cursor_factory=RealDictCursor) query = 'insert into class_of_ips(name, ips) values ({name}, {ips})'.format( name=surround_with_quotes(name), ips=surround_with_quotes(list_to_pgarray(ips))) print(query) cursor.execute(query)
def main(): connection = get_connection() connection.autocommit = True EXCHANGE_NAME = 'fanoucik' queue_connection = pika.BlockingConnection( pika.ConnectionParameters(host='localhost')) channel = queue_connection.channel() channel.exchange_declare(exchange=EXCHANGE_NAME, exchange_type='fanout') result = channel.queue_declare(queue='', exclusive=True) queue_name = result.method.queue channel.queue_bind(exchange=EXCHANGE_NAME, queue=queue_name) channel.basic_consume(queue=queue_name, on_message_callback=consume_rabbit_data_callback, auto_ack=True) channel.start_consuming()
from src.rest_api.db.db_configuration import get_connection import re import sh connection = get_connection() def get_local_network_ip_addresses(): ifconfig_result = sh.ifconfig() ifconfig_result_str = ifconfig_result.stdout.decode("utf-8") ips = re.findall(r'[0-9]+(?:\.[0-9]+){3}', ifconfig_result_str) return ips connection.autocommit = True def fill_incoming_outgoing(): cursor = connection.cursor() local_network_ip_addresses = get_local_network_ip_addresses() query = """UPDATE ip_traffic SET incoming_outgoing = 'outgoing' WHERE ip_src IN %s """ cursor.execute(query, (tuple(local_network_ip_addresses), )) # OUTGOING ---
def get_filtered_data(kwargs): connection = get_connection() cursor = connection.cursor(cursor_factory=RealDictCursor) conditions = [] parameters = [] in_parameters = [ 'port_src_in', 'port_dst_in', 'ip_proto_in', 'incoming_outgoing_in' ] contain_parameters = ['ip_src_in', 'ip_dst_in'] for contain_parameter in contain_parameters: if not is_parameter_empty(kwargs[contain_parameter]): conditions.append("({} SIMILAR TO %s)".format( contain_parameter.replace("_in", ""))) parameter = '%({})%'.format('|'.join(kwargs[contain_parameter])) '%(foo|bar|baz)%' parameters.append(parameter) for in_parameter in in_parameters: print(in_parameter) if not is_parameter_empty(kwargs[in_parameter]): conditions.append("({} IN %s)".format( in_parameter.replace("_in", ""))) parameters.append(tuple(kwargs[in_parameter])) between_numeric_parameters = ['packets_between', 'bytes_between'] for between_numeric_parameter in between_numeric_parameters: extracted_parameter = kwargs[between_numeric_parameter] if not is_parameter_empty(extracted_parameter): is_upper_unbound = extracted_parameter[1] is None conditions.append("({0} BETWEEN %s AND %s{1})".format( between_numeric_parameter.replace("_between", ""), '::FLOAT8' if is_upper_unbound else '')) parameters.append(extracted_parameter[0]) parameters.append( "+infinity" if is_upper_unbound else extracted_parameter[1]) stamp_between = kwargs["stamp_between"] if not is_parameter_empty(stamp_between): conditions.append("({} BETWEEN %s AND %s)".format(TIMESTAMP_COLUMN)) parameters.append(stamp_between[0]) parameters.append(stamp_between[1] if stamp_between[1] is not None else "'9999-03-27 21:48:02.000000'") page = kwargs["page"] limit = kwargs["limit"] parameters.append(page * limit if limit != DEFAULTS["limit"] else 0) parameters.append(limit) where_condition = "" if is_array_empty(conditions) else " AND ".join( conditions) query = """SELECT * FROM {table_name} {where} {where_condition} ORDER BY {timestamp_column} OFFSET %s LIMIT %s;""".format( table_name=TABLE_NAME, timestamp_column=TIMESTAMP_COLUMN, where='WHERE' if not is_array_empty(conditions) else '', where_condition=where_condition) query_parameters = tuple(parameters) cursor.execute(query, query_parameters) return cursor.fetchall()
def get_all_classes_of_ips(): connection = get_connection() cursor = connection.cursor(cursor_factory=RealDictCursor) query = 'select * from class_of_ips' cursor.execute(query) return cursor.fetchall()