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())
