banksystem.py

from myconnection import connect_to_mysql  # pip install mysql-connector-python, myconnection.py must exist
from icecream import ic  # pip install icecream
from mysql.connector import Error

config = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "",
    "database": "bankdb",
}

user_signIn = False
admin_signIn = False
acc = 0

def execute_query(connection, query, values=None):
    cursor = connection.cursor()
    try:
        cursor.execute(query, values)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
    finally:
        cursor.close()

def fetch_query(connection, query, values=None):
    cursor = connection.cursor()
    try:
        cursor.execute(query, values)
        return cursor.fetchall()
    except Error as e:
        print(f"The error '{e}' occurred")
    finally:
        cursor.close()

def insert_data(connection, accountno, password, name, amount, phone):
    query = "INSERT INTO accounts (account_no, password, name, amount, phone) VALUES (%s, %s, %s, %s, %s)"
    values = (accountno, password, name, amount, phone)
    execute_query(connection, query, values)

def signup(connection):
    accountno = int(input('Enter account no: '))
    while True:
        password = input('Enter Password: ')
        confirm_password = input('Enter Confirm Password: ')
        if len(password) < 8:
            print('Password must be at least 8 characters long')
        elif password != confirm_password:
            print('Passwords did not match, please try again!')
        else:
            break

    name = input('Enter your name: ')
    amount = int(input('Enter Initial Deposit amount: '))
    phone = input('Enter your phone no: ')
    insert_data(connection, accountno, password, name, amount, phone)

def delete_account(connection, accountno):
    query = "DELETE FROM accounts WHERE account_no = %s"
    execute_query(connection, query, (accountno,))

def update_amount(connection, accountno, amount):
    query = "UPDATE accounts SET amount = %s WHERE account_no = %s"
    execute_query(connection, query, (amount, accountno))

def deposit_money(connection, account_no, deposit_amount):
    query = "SELECT amount FROM accounts WHERE account_no = %s"
    data = fetch_query(connection, query, (account_no,))
    if data:
        amount = data[0][0] + deposit_amount
        update_amount(connection, account_no, amount)
        print(f'Your new balance is: {amount}')

def withdraw_money(connection, account_no, withdraw_amount):
    query = "SELECT amount FROM accounts WHERE account_no = %s"
    data = fetch_query(connection, query, (account_no,))
    if data:
        amount = data[0][0]
        if withdraw_amount > amount:
            print('Insufficient funds!')
            return
        amount -= withdraw_amount
        update_amount(connection, account_no, amount)
        print(f'Your new balance is: {amount}')

def show_all_accounts(connection):
    query = "SELECT * FROM accounts"
    data = fetch_query(connection, query)
    print('\n\nUser Accounts\n-------------')
    for d in data:
        print(f'id = {d[0]}, account_no = {d[1]}, name = {d[2]}, amount = {d[3]}, phone = {d[4]}')

def check_account(connection, account_no, password):
    query = "SELECT * FROM accounts WHERE account_no = %s AND password = %s"
    data = fetch_query(connection, query, (account_no, password))
    if data:
        check_admin(connection, account_no)
        return True
    return False

def check_admin(connection, account_no):
    global admin_signIn, user_signIn
    query = "SELECT id FROM accounts WHERE account_no = %s"
    data = fetch_query(connection, query, (account_no,))
    if data:
        account_id = data[0][0]
        query = "SELECT * FROM admin WHERE accounts_id = %s"
        data = fetch_query(connection, query, (account_id,))
        if data:
            admin_signIn = True
            user_signIn = False
            print('Admin Login')
        else:
            admin_signIn = False
            user_signIn = True
            print('User Login')

def show_user_accounts(connection, account_no):
    query = "SELECT * FROM accounts WHERE account_no = %s"
    data = fetch_query(connection, query, (account_no,))
    for d in data:
        print(d)

def sign_in(connection):
    global acc
    acc = int(input('Enter account no: '))
    password = input('Enter Password: ')
    if check_account(connection, acc, password):
        print('Successfully Signed in!')
        return True
    print('Wrong username or password.')
    return False

# Program start
cnx = connect_to_mysql(config, attempts=3)
if not cnx or not cnx.is_connected():
    print('Connection error!, Bye')
    exit(1)

while True:
    if sign_in(cnx):
        break
    if input('Try again? (y/n): ').lower() != 'y':
        print('Bye!')
        exit(0)

while True:
    print('0-Exit')
    print('1-Create Account')
    print('2-Deposit Money')
    print('3-Withdraw Money')
    print('4-Show Amount')
    if admin_signIn:
        print('5-Show All Users (for admin only)')
        print('6-Delete Account (for admin only)')
        choice = int(input('Enter your choice (0,1,2,3,4,5,6): '))
    else:
        choice = int(input('Enter your choice (0,1,2,3,4): '))

    if choice == 0:
        print('Bye')
        cnx.close()
        exit(0)
    elif choice == 1:
        if admin_signIn:
            signup(cnx)
        else:
            print('You don’t have privileges to create an account!')
    elif choice == 2:
        deposit_amount = int(input('Please enter amount to deposit: '))
        deposit_money(cnx, acc, deposit_amount)
    elif choice == 3:
        withdraw_amount = int(input('Please enter amount to withdraw: '))
        withdraw_money(cnx, acc, withdraw_amount)
    elif choice == 4:
        show_user_accounts(cnx, acc)
    elif choice == 5 and admin_signIn:
        show_all_accounts(cnx)
    elif choice == 6 and admin_signIn:
        acc_to_delete = int(input('Enter account no. to delete: '))
        delete_account(cnx, acc_to_delete)
    else:
        print('Invalid choice or insufficient privileges')