def load_account_types(bank):
cursor = db.cursor()
select_query = f'select * from account_types where bank = {bank.bank_id}'
cursor.execute(select_query)
record = cursor.fetchall()
cursor.close()
return record
def update_account_info(account, field_name, value):
update_query = f'update account_types set {field_name} = "{value}" where account_type_id = "{account.account_type_id}"'
cursor = db.cursor()
cursor.execute(update_query)
db.commit()
cursor.close()
print('Account type has been updated')
def delete_user(username):
my_cursor = db.cursor()
delete_query = f'delete from users where username = "******"'
my_cursor.execute(delete_query)
db.commit()
my_cursor.close()
print('User successfully deleted')
def insert_bank(bank):
my_cursor = db.cursor()
insert_query = f'insert into banks (bank_name, location) VALUES ("{bank.name}", "{bank.location}")'
my_cursor.execute(insert_query)
db.commit()
my_cursor.close()
print("Bank successfully inserted into banks table\n")
def update_bank(bank):
update_query = f'update banks set bank_name = "{bank.name}", location = "{bank.location}" ' \
f'where bank_id = "{bank.bank_id}"'
my_cursor = db.cursor()
my_cursor.execute(update_query)
db.commit()
my_cursor.close()
print("Bank has been updated")
def add_account_db(account):
insert_query = f'insert into accounts (bank, account_number, balance, account_holder, account_type,' \
f'date_created) values ("{account.bank}", "{account.account_number}", "{account.balance}", ' \
f'"{account.account_holder}", "{account.account_type_id}", "{account.date_created}");'
cursor = db.cursor()
cursor.execute(insert_query)
db.commit()
cursor.close()
print('Account successfully inserted into accounts table')
def insert_account_type(account_type):
cursor = db.cursor()
insert_query = f'insert into account_types (account_type, bank, interest_rate, monthly_fees, minimum_balance, ' \
f'max_withdrawals) values ("{account_type.account_type}", "{account_type.bank}", ' \
f'"{account_type.interest_rate}", "{account_type.monthly_fees}", "{account_type.minimum_balance}"' \
f', "{account_type.max_withdrawals}") '
cursor.execute(insert_query)
db.commit()
cursor.close()
print('Account type successfully inserted into account_types table')
def load_all_banks():
banks = []
select_query = 'select * from banks'
my_cursor = db.cursor()
my_cursor.execute(select_query)
rows = my_cursor.fetchall()
for row in rows:
banks.append(row)
my_cursor.close()
return banks
def check_table_exists(table):
query = f"show tables like '{table}'"
my_cursor = db.cursor()
my_cursor.execute(query)
result = my_cursor.fetchone()
my_cursor.close()
if result:
return True
else:
return False
def insert_user(user):
my_cursor = db.cursor()
if user.bank is None:
user.bank = "NULL"
insert_query = ("insert into users (first_name, last_name, username, password, dob, ssn, email, bank) VALUES "
f"('{user.first_name}', '{user.last_name}', '{user.username}', '{user.password}', "
f"STR_TO_DATE('{user.dob}','%Y-%m-%d'), '{user.ssn}', '{user.email}', '{user.bank}') ")
my_cursor.execute(insert_query)
db.commit()
my_cursor.close()
print("User successfully inserted into users table\n")
def fetch_type_from_name(account_type_name, bank_id):
cursor = db.cursor()
select_query = f'select * from account_types where bank = "{bank_id}" and account_type = "{account_type_name}"'
cursor.execute(select_query)
record = cursor.fetchall()
row_count = cursor.rowcount
cursor.close()
if row_count > 0:
return True, record[0][6]
else:
return False
def remove_account_type(type_id):
cursor = db.cursor()
delete_query = f'delete from account_types where account_type_id = "{type_id}"'
cursor.execute(delete_query)
db.commit()
update_referencing_accounts_query = f'update accounts set account_type_id = NULL where account_type_id = ' \
f'"{type_id}"'
cursor.execute(update_referencing_accounts_query)
db.commit()
cursor.close()
print('Account type successfully deleted')
def delete_tables():
my_cursor = db.cursor()
query = "drop table users"
my_cursor.execute(query)
query = "drop table accounts"
my_cursor.execute(query)
query = "drop table account_types"
my_cursor.execute(query)
query = f"drop table banks"
my_cursor.execute(query)
my_cursor.close()
return True
def check_bank_exists(bank):
my_cursor = db.cursor()
select_query = f'SELECT * FROM banks WHERE bank_name = "{bank.name}" and location = "{bank.location}"'
my_cursor.execute(select_query)
record = my_cursor.fetchall()
row_count = my_cursor.rowcount
if row_count > 0:
my_cursor.close()
return True, record[0]
else:
my_cursor.close()
return False, 0
def load_account(account_id):
select_query = f'select * from accounts where account_id = "{account_id}"'
cursor = db.cursor()
cursor.execute(select_query)
record = cursor.fetchall()
row_count = cursor.rowcount
cursor.close()
if row_count > 0:
account = [record[0][0], record[0][1], record[0][2], record[0][3], record[0][4],
record[0][5], record[0][6]]
return account
else:
print('There is no account associated with this ID')
def load_bank(bank_id):
select_query = f'select * from banks where bank_id = {bank_id}'
my_cursor = db.cursor()
my_cursor.execute(select_query)
record = my_cursor.fetchall()
row_count = my_cursor.rowcount
my_cursor.close()
if row_count > 0:
bank_name = record[0][0]
bank_location = record[0][1]
bank_id = record[0][2]
bank = [bank_name, bank_location, bank_id]
return bank
def remove_bank(bank):
my_cursor = db.cursor()
delete_query = f'delete from banks where bank_id = "{bank.bank_id}"'
my_cursor.execute(delete_query)
db.commit()
update_bank_in_users_query = f'update users set bank = NULL where bank = "{bank.bank_id}"'
my_cursor.execute(update_bank_in_users_query)
db.commit()
update_account_types_query = f'delete from account_types where bank = "{bank.bank_id}"'
my_cursor.execute(update_account_types_query)
db.commit()
my_cursor.close()
print('Bank successfully deleted')
return None
def fetch_account_type(type_id):
cursor = db.cursor()
select_query = f'select * from account_types where account_type_id = "{type_id}"'
cursor.execute(select_query)
record = cursor.fetchall()
row_count = cursor.rowcount
cursor.close()
if row_count > 0:
account_name = record[0][0]
account_bank = record[0][1]
account_interest = record[0][2]
account_fee = record[0][3]
account_min_bal = record[0][4]
account_max_withdrawal = record[0][5]
account_id = record[0][6]
account_type = [account_name, account_bank, account_interest, account_fee, account_min_bal,
account_max_withdrawal, account_id]
return account_type
def load_user(username):
my_cursor = db.cursor()
select_query = f'select users.first_name, users.last_name, users.username, users.password, users.dob, users.ssn, ' \
f'users.email, banks.bank_name from users inner join banks on users.bank = banks.bank_id where ' \
f'username = "******"'
my_cursor.execute(select_query)
record = my_cursor.fetchall()
row_count = my_cursor.rowcount
my_cursor.close()
if row_count > 0:
first_name = record[0][0]
last_name = record[0][1]
username = record[0][2]
password = record[0][3]
dob = record[0][4]
ssn = record[0][5]
email = record[0][6]
bank = record[0][7]
user = [first_name, last_name, username, password, dob, ssn, email, bank]
return True, user
else:
return False, 0
def create_tables():
my_cursor = db.cursor()
if not check_table_exists('banks'):
my_cursor.execute("CREATE TABLE banks (bank_name VARCHAR(255), location VARCHAR(255), "
"bank_id INT AUTO_INCREMENT, PRIMARY KEY(bank_id));")
print("Banks table created")
if not check_table_exists('users'):
my_cursor.execute("CREATE TABLE users (first_name VARCHAR(255), last_name VARCHAR(255), username VARCHAR("
"255), password VARCHAR(255), dob DATE, ssn VARCHAR(255), email VARCHAR(255), bank INT, "
"PRIMARY KEY(username), FOREIGN KEY(bank) REFERENCES banks(bank_id));")
print("Users table created")
if not check_table_exists('account_types'):
my_cursor.execute('create table account_types (account_type varchar(255), bank INT, interest_rate decimal(7,2),'
' monthly_fees decimal(7,2), minimum_balance decimal(7,2), max_withdrawals int, '
'account_type_id int not null auto_increment, primary key(account_type_id), foreign key(bank)'
' references banks(bank_id));')
if not check_table_exists('accounts'):
my_cursor.execute('create table accounts (bank int, account_number int, balance decimal(14,2), account_holder '
'varchar(255), account_type int, date_created date, account_id int not null '
'auto_increment, primary key(account_id), foreign key(account_type) references '
'account_types(account_type_id), foreign key(account_holder) references users(username), '
'foreign key(bank) references banks(bank_id));')
my_cursor.close()
