mysqlbankplain.py

# error code 1 - connection error
# error code 2 - user error, username or password


from myconnection import connect_to_mysql #pip install mysql-connector-python, myconnection.py must exists
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

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

# Function to fetch results from a select query
def fetch_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")


# INSERT
def insertData(connection,accountno,password,name,amount,phone):
    q = f"INSERT INTO accounts(account_no,password,name,amount,phone) values ({accountno},'{password}','{name}',{amount},'{phone}')"
    # string must be quoted (အပြင်မှာ '' ဆို အတွင်းမှာ "")
    execute_query(connection,q)

# Signup
def signup(connection):
    accountno = input('Enter account no: ')
    accountno = int(accountno)

    while True:
        password = input('Enter Password: ')
        confirm_password = input('Enter Confirm Password: ')
        if len(password) < 8:
            print('Passowrd must at least 8 digits')
        elif password != confirm_password:
            print('Passwords did not much, Please try again!')
        else:
            break

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

# DELETE
def deleteAccount(connection,accountno):
    q = f"DELETE from accounts where account_no = {accountno}"
    execute_query(connection,q)

# UPDATE
def updateAmount(connection,accountno,amount):
    q = f"UPDATE accounts SET amount = {amount} where accountno = {accountno}"
    execute_query(connection,q)

def depositMoney(connection,account_no,depositAmount):
    q = f'SELECT amount from accounts where account_no = {account_no}'
    data = fetch_query(connection,q)
    amount = data[0][0]
    amount = amount + depositAmount
    updateAmount(connection,account_no,amount)
    print(f'Now your amount is updated: {amount}')

def withdrawMoney(connection,account_no,withdrawAmount):
    q = f'SELECT amount from accounts where account_no = {account_no}'
    data = fetch_query(connection,q)
    amount = data[0][0]
    if withdrawAmount > amount:
        print('Insufficient amount!')
        return(3)
    amount = amount - depositAmount
    updateAmount(connection,account_no,amount)
    print(f'Now your amount is updated: {amount}')


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

def checkAccount(connection,account_no,password):
    q = f'SELECT * from accounts where account_no = {account_no} and password = "{password}"'
    data = fetch_query(connection,q)
    #ic(data)
    if data != []:
        checkAdmin(connection,account_no)
        return True

    else:
        return False

def checkAdmin(connection,account_no):
    global admin_signIn
    global user_signIn
    q = f'SELECT id from accounts where account_no = {account_no}'
    data = fetch_query(connection,q)
    id = data[0][0]
    #ic(id)
    q = f'SELECT * from admin where accounts_id = {id}'
    data = fetch_query(connection,q)
    #ic(data)
    if data != []:
        admin_signIn = True
        user_signIn = False
        print('Admin Login')

    else:
        print('User Login')
        admin_signIn = False
        user_signIn = True


# showThatAcc
def showUserAccounts(connection,accountno):
    q = f'SELECT * from accounts where accountno = {accountno}'
    data = fetch_query(connection,q)
    for d in data:
        print(d)


def signIn(connection):
    global acc
    acc = input('Enter account no: ')
    acc = int(acc)
    password = input('Enter Password: ')
    status = checkAccount(connection,acc,password)
    if status:
        print('Successfully Signin!')
        return True
    else:
        print('Wrong username or password.')
        return False

#program start here
cnx = connect_to_mysql(config, attempts=3)

if not cnx or not cnx.is_connected():
    print('Connection error!, Bye')
    exit(1)

# Database connection OK...
while True:
    signIn_status = signIn(cnx)
    if signIn_status:
        break
    ans = input('Try again? (y/n): ')
    if ans.lower() != 'y':
        print('Bye!')
        exit(0)

#Menu
while True:
    print('0-Exit')
    print('1-Create Account') #must member of Admin to 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)') #must member of Admin to create account
        print('6-Delete Account (for admin only)') #must member of Admin to create account
        ans = input('Enter your choice(0,1,2,3,4,5,6): ')
    else:
        ans = input('Enter your choice(0,1,2,3,4): ')

    ans = int(ans)
    if ans == 0:
        print('Bye')
        cnx.close()
        exit(0)
    elif ans == 1:
        if admin_signIn:
            print('Welcome Admin:')
            signup(cnx)
        else:
            print('You dont have priveleges to create an account!')
    elif ans == 2:
        depositAmount = input('Pls enter amount to deposit: ')
        depositAmount = int(depositAmount)
        depositMoney(cnx,acc,depositAmount)
    elif ans == 3:
        wdAmount = input('Pls enter amount to withdraw: ')
        wdAmount = int(wdAmount)
        withdrawMoney(cnx,acc,wdAmount)
    elif ans == 4:
        showUserAccounts(cnx,acc)
    elif ans == 5:
        if not admin_signIn:
            print('You are not Admin!, Sorry!')
        showAllAccounts(cnx)
    elif ans == 6:
        acc2Del = input('Enter account no. to delete: ')
        acc2Del = int(acc2Del)
        deleteAccount(cnx,acc2Del)