def __init__(self): QMainWindow.__init__(self) self.ui = Ui_ClientInfo() self.ui.setupUi(self) self.ui.start_button.clicked.connect(self.get_user_info) self.ui.search_button.clicked.connect(self.search) self.ui.searchResult.doubleClicked.connect(self.select_client) create_connection()
def get_details(id): conn = db.create_connection('bank.db') sql = 'SELECT * FROM accounts WHERE acc_no=?' with conn: cur = conn.execute(sql, (id, )) rows = cur.fetchall() return rows
def test_user_exists(self): self.create_test_user() with db_connection.create_connection(self.database_path) as conn: self.assertTrue( self.login_session.user_exists( self.login_session.user.username, self.database_path), "User does not exist")
def save_subdomain(self): conn = create_connection() with conn: subdomain = (self.url, self.google_dorks, self.webserver, self.links, self.asset_id) self.subdomain_id = create_subdomain(conn, subdomain) return self.subdomain_id
def main(argc, argv): # init db connection db_config = db_con.get_db_config(path=argv[2]) con, cur = db_con.create_connection(db_config) if argc < 2: print('Configuration file for index creation required') return vec_config = json.load(open(argv[1], 'r')) init_tables(con, cur, vec_config['table_name']) insert_vectors(vec_config['vec_file_path'], con, cur, vec_config['table_name'], db_config['batch_size'], vec_config['normalized']) # commit changes con.commit() # create index create_index(vec_config['table_name'], vec_config['index_name'], 'word', con, cur) # close connection con.close()
def create_new_user(self, credentials: tuple, database: str = None): if database is None: database = self.database_path with db_connection.create_connection(database) as conn: user_id = db_connection.create_user(conn, credentials) self.user = User(user_id, credentials[0], credentials[1]) self.logged_in = True print(f"User {self.user.user_id} with username {self.user.username} has been created and logged in.")
def deactivate_account(self, database: str = None): if database is None: database = self.database_path user_id = self.user.user_id with db_connection.create_connection(database) as conn: db_connection.delete_user(conn, user_id) self.user = None self.logged_in = False
def update_user_password(self, new_password: str, database: str = None): if database is None: database = self.database_path new_hashed_password = bcrypt.hashpw(new_password.encode('utf8'), bcrypt.gensalt()) with db_connection.create_connection(database) as conn: db_connection.update_user(conn, (self.user.username, new_hashed_password, self.user.user_id)) self.user.password = new_hashed_password print("Password updated.")
def user_exists(self, username: str, database: str = None) -> bool: if database is None: database = self.database_path with db_connection.create_connection(database) as conn: user_rows = db_connection.get_user_by_username(conn, username) if len(user_rows) == 0: return False else: return True
def save_link(self): conn = create_connection() with conn: link = (self.response_code, self.page_url, self.tech_stack, self.header, self.forms, self.http_parameters, self.hardcoded_strings, self.screenshots, self.backup_files, self.outdated_js, self.subdomain_id) self.link_id = create_link(conn, link) return self.subdomain_id
def d_or_w(obj, id, amount, type): sql = f'UPDATE accounts SET balance=? WHERE acc_no=?' conn = db.create_connection('bank.db') with conn: if type == 'D': conn.execute(sql, (obj.deposit(amount), id)) elif type == 'W': conn.execute(sql, (obj.withdrawal(amount), id)) print('Deposit/Withdrawal Done')
def validate_login(id): conn = db.create_connection('bank.db') sql = 'SELECT acc_no FROM accounts WHERE acc_no=?' with conn: cur = conn.execute(sql, (int(id), )) rows = cur.fetchall() if rows[0][0] == int(id): return True else: return False
def tearDown(self): sql_drop_users_table = "DROP TABLE IF EXISTS users;" # create a database connection self.conn = db_connection.create_connection(self.database_path) if self.conn is not None: # drop users table db_connection.drop_table(self.conn, sql_drop_users_table) else: print("Error! cannot create the database connection.")
def register(): obj = Account.create_account() conn = db.create_connection('bank.db') sql = f'INSERT INTO accounts (account_name,account_type,city,national_id,balance,date_created,flag)' \ f' VALUES(?,?,?,?,?,?,1)' acc_sql = (obj.account_name, obj.account_type, obj.city, obj.national_id, obj.get_balance(), obj.date_created) with conn: cur = conn.cursor() cur.execute(sql, acc_sql) return cur.lastrowid
def create_test_user(self): username = "******" password = "******" hashed_password = bcrypt.hashpw(password.encode('utf8'), bcrypt.gensalt()) with db_connection.create_connection(self.database_path) as conn: user_id = db_connection.create_user(conn, (username, hashed_password)) self.login_session.user = login.User(user_id, username, hashed_password) self.login_session.logged_in = True
def test_login(self): username = "******" password = "******" hashed_password = bcrypt.hashpw(password.encode('utf8'), bcrypt.gensalt()) with db_connection.create_connection(self.database_path) as conn: db_connection.create_user(conn, (username, hashed_password)) self.login_session.login_user(username, password, self.database_path) self.assertTrue(self.login_session.logged_in, 'Not logged in')
def create_user_table(self): sql_create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id integer PRIMARY KEY, username text NOT NULL, password text ); """ with db_connection.create_connection(self.database_path) as conn: if conn is not None: db_connection.create_table(conn, sql_create_users_table) else: print("Error! Cannot create the database connection.")
def login_user(self, username: str, password: str, database: str = None) -> bool: if database is None: database = self.database_path with db_connection.create_connection(database) as conn: user_rows = db_connection.get_user_by_username(conn, username) user_id, user_username, user_password = user_rows[0] if user_rows and bcrypt.checkpw(password.encode('utf8'), user_password): self.user = User(user_id, user_username, user_password) self.logged_in = True print("Successful login") return True return False
def test_register_new_user(self): username = "******" password = "******" self.login_session.create_new_user((username, password), self.database_path) self.assertTrue(self.login_session.logged_in, "User was not logged in") self.assertIsInstance(self.login_session.user, login.User, "User was not instantiated") with db_connection.create_connection(self.database_path) as conn: self.assertEqual( 1, len(db_connection.get_user_by_username(conn, username)), "User was not created in DB")
def main(argc, argv): # get retrofitting config conf = config.get_config(argv) # get group information groups_info = utils.parse_groups(conf['GROUPS_FILE_NAME']) data_columns = utils.get_data_columns_from_group_data(groups_info) db_config = db_con.get_db_config(path=argv[2]) con, cur = db_con.create_connection(db_config) # get tokens of data columns all_terms = utils.get_terms(data_columns, con, cur) print('Retrieved terms from database') present_vectors, dim = utils.get_vectors_for_present_terms_from_group_file( data_columns, groups_info) print('Got vectors of terms from group file') # create M0 and presence vector term_list, M0, v_P = create_M0(all_terms, present_vectors, dim, conf) print('Constructed initial matrix M0 with size', M0.shape) print('len', len(v_P.nonzero()[0])) # create adjacency matrices, weight matrices, count vectors and vector for R A_cat, S, c, rel_key_pairs = create_adjacency_matrices( term_list, groups_info, con, cur, v_P, conf) print('Created matrix representations') # get category vectors v_c v_c = get_v_c(A_cat, M0, v_P) for key in v_c: print(key, np.linalg.norm(v_c[key])) print('Created category vectors') v_Q = np.ones(len(v_P)) # run iterative algorithm Mk = run_retrofitting(M0, S, v_c, c, v_Q, A_cat, rel_key_pairs, term_list, conf) print('Retrofitting done, start to generate vectors file ...') # output result to file output_vectors(term_list, Mk, conf['RETRO_VECS_FILE_NAME']) print('Exported vectors') return
def create_edgelist(retro_config_filename, db_config_filename, graph_name): conf = config.get_config_by_file_name(retro_config_filename) groups_info = utils.parse_groups(conf['GROUPS_FILE_NAME']) db_config = db_con.get_db_config(path=db_config_filename) con, cur = db_con.create_connection(db_config) data_columns = get_data_columns(groups_info) all_terms = utils.get_terms(data_columns, con, cur) term_list = get_term_list(all_terms) term_list += data_columns edges = get_edges(term_list, groups_info, con, cur) export_edgelist(term_list, edges, graph_name + '.edges', graph_name + '.terms') return
def insert_data(imei, longitud, latitud, vitesse, time_tracking): try: conn, cursor = db_connection.create_connection() except: return if cursor != None: try: cursor.execute( "INSERT INTO track (imie,logitude ,latitude ,vitesse,DATE) VALUES (%s,%s,%s,%s,%s);", (imei, longitud, latitud, vitesse, time_tracking)) conn.commit() except: print("can't add data to database'") return 0 finally: cursor.close() conn.close()
def setUp(self): with open('db.yaml', 'r') as db_config: self.db_config = yaml.safe_load(db_config) self.database_path = self.db_config['TEST_PATH'] sql_create_users_table = """ CREATE TABLE IF NOT EXISTS users ( id integer PRIMARY KEY, username text NOT NULL, password text ); """ # create a database connection self.conn = db_connection.create_connection(self.database_path) if self.conn is not None: # create users table db_connection.create_table(self.conn, sql_create_users_table) else: print("Error! cannot create the database connection.") self.login_session = login.Login()
def main(argc, argv): db_config_file = argv[1] config = read_config_file(argv[2]) train_data_size = int(config['train-size']) test_data_size = int(config['test-size']) output_folder = config['output_folder'] db_config = db_con.get_db_config(db_config_file) con, cur = db_con.create_connection(db_config) # get query that returns tuple of relations which are present query = get_vector_query( config['query'], config['table_names'], VEC_TABLE_TEMPL, VEC_TABLE1_TEMPL, VEC_TABLE2_TEMPL) id_lookup, value_lookup, value_count, id_keylist = get_raw_data( query, con, cur) scores = [] for i in range(ITERATIONS): data, value_lookup, output_size = get_data( train_data_size + test_data_size, id_lookup, value_lookup, value_count, id_keylist) input_data = list(data.values()) random.shuffle(input_data) train_data = input_data[:train_data_size] test_data = input_data[train_data_size:] score = classify(train_data, test_data, output_size) scores.append(score) print('Scores:', scores) print('mu %f %% std %f %%' % ( np.mean(scores), np.std(scores))) mu = np.mean(scores) std = np.std(scores) print('mu', mu, 'std', std) # output label = '+'.join(config['table_names']) # save output output_result(dict({label: {'mu': mu, 'std': std, 'scores': scores}}), output_folder + OUTPUT_NAME + label + '.json') # plot output plot_graph(scores, label, 'App Category Classification', output_folder + OUTPUT_NAME_PLOT + label + '.png') return mu, std, scores
def main(): account_df, person_df, transaction_df = prepare_date() account_types = get_type_categories(account_df,'account_type') print("The different account types are:") print(account_types) transaction_types = get_type_categories(transaction_df,'transaction_type') print("The different transaction types are:") print(transaction_types) # Establish connection print('---Trying to connect to Database---') connection = create_connection(postgresql_db_config) # Populate Enumerated type valid_account_type in database with different account types values for val in account_types: valid_account_type_insert = "ALTER TYPE valid_account_type ADD VALUE " + "'" + str(val) + "';" execute_query(connection, valid_account_type_insert) # Populate Enumerated type valid_transaction_type in database with different transaction types values for val in transaction_types: valid_transaction_type_insert = "ALTER TYPE valid_transaction_type ADD VALUE " + "'" + str(val) + "';" execute_query(connection, valid_transaction_type_insert) # Insert customers information into persons table of database for index, row in person_df.iterrows(): customers_records_insert="INSERT INTO persons (id,name,surname,zip,city,country,email,phone_number,birth_date) VALUES " + str(tuple(row.values)) +";" execute_query(connection, customers_records_insert) # Insert accounts information into accounts table of database for index, row in account_df.iterrows(): accounts_records_insert="INSERT INTO accounts (id,id_person,account_type) VALUES " + str(tuple(row.values)) +";" execute_query(connection, accounts_records_insert) # Insert transactions information into transactions table of database for index, row in transaction_df.iterrows(): transactions_records_insert="INSERT INTO transactions (id,id_account,transaction_type, transaction_date, transaction_amount) VALUES " + str(tuple(row.values)) +";" execute_query(connection, transactions_records_insert) connection.commit() connection.close()
def main(argc, argv): # get database connection db_config = db_con.get_db_config(path=argv[2]) con, cur = db_con.create_connection(db_config) # get retrofitting config conf = config.get_config(argv) # read out data from database db_columns = get_all_db_columns( (conf['TABLE_BLACKLIST'], conf['COLUMN_BLACKLIST']), con, cur) # construct graph from relational data schema = get_schema(con, cur) # export schema relation_graph = construct_relation_graph( schema, db_columns, conf['RELATION_BLACKLIST']) nx.write_gml(relation_graph, conf['SCHEMA_GRAPH_PATH']) return
def get_project_id(project_name): conn = create_connection() with conn: project_id = select_project_id(conn, project_name) return project_id
def save_scan(self): conn = create_connection() scan = (self.project_id, self.date, self.assets) with conn: self.scan_id = create_scan_result(conn, scan)
def main(argc, argv): if argc < 8: print('Not enough arguments') return retro_config_filename = argv[1] db_config_file = argv[2] query = argv[3] # query that returns tuple of relations which are present train_data_size = int(argv[4]) test_data_size = int(argv[5]) iterations = int(argv[6]) table_names = [] table_names.append(argv[7]) if argc == 9: table_names.append(argv[8]) db_config = db_con.get_db_config(db_config_file) con, cur = db_con.create_connection(db_config) # create edgelist graph_name = GRAPH_FILE_PREFIX + str(time.time()) utils.create_edgelist(retro_config_filename, db_config_file, graph_name) query = utils.get_vector_query(query, table_names, VEC_TABLE_TEMPL, VEC_TABLE1_TEMPL, VEC_TABLE2_TEMPL) id_lookup, text_lookup = pr.get_raw_data(query, con, cur) vector_lookup = defaultdict(list) print('table_names', table_names) vectors = list() for table_name in table_names: vectors.append(utils.get_db_vectors(table_name, con, cur)) for key in vectors[0]: combined_vector = [] complete = True for vector_dataset in vectors: if key not in vector_dataset: complete = False print('WARNING: Ignore', key, 'since it is not present in all vector datasets') break if np.linalg.norm(vector_dataset[key]) > 0: combined_vector.append(vector_dataset[key] / np.linalg.norm(vector_dataset[key])) else: combined_vector.append(vector_dataset[key]) if complete: combined_vector = np.concatenate(combined_vector) vector_lookup[key].append(combined_vector / np.linalg.norm(combined_vector)) g, nodes = pr.construct_graph(graph_name) features = pr.construct_features_from_vec_dict(nodes, text_lookup, vector_lookup) train_size = int(0.9 * train_data_size) test_size = train_data_size - train_size validation_size = test_data_size print(train_size, test_size, validation_size) scores = classify_fast_method(nodes=nodes, features=features, data_dict=id_lookup, text_lookup=text_lookup, g=g, train_size=train_size, test_size=test_size, validation_size=validation_size, epochs=500, iterations=iterations, lr=0.001, activation=mx.nd.relu, normalization=True, hidden_layers=[300], msg_fn='mean', stagnation=50) mu = np.mean(scores) std = np.std(scores) print('mu', mu, 'std', std, 'scores', scores) os.remove(graph_name + '.edges') os.remove(graph_name + '.terms') return mu, std, scores
import sqlite3 from sqlite3 import Error from db_connection import create_connection DATABASE_PATH = r"" TABLE_NAME = "stocks" sql_create_table_command = """ CREATE TABLE IF NOT EXISTS {} ( stock VARCHAR(10) PRIMARY KEY ); """.format(TABLE_NAME) def create_table(conn, create_table_sql): """ create a table from the create_table_sql statement :param conn: Connection object :param create_table_sql: a CREATE TABLE statement :return: """ try: c = conn.cursor() c.execute(create_table_sql) except Error as e: print(e) if __name__ == '__main__': conn = create_connection(DATABASE_PATH) create_table(conn, sql_create_table_command) conn.close()
import sqlite3 import codecs from db_connection import create_connection database = "word_database.db" conn = create_connection(database) if conn is not None: try: c = conn.cursor() c.execute('SELECT COUNT(*) FROM WORDS') except Error as e: print(e) print(c.fetchone())