Bank Management- Python Project for class 12 MySQL Connectivity

Bank Management- Python Project Class 12

Source Code:

#https://cbsepython.in
print("****BANK TRANSACTION****")
#creating database
import mysql.connector
mydb=mysql.connector.connect (host="localhost",user="root", passwd="admin")
mycursor=mydb.cursor()
mycursor.execute("create database if not exists bank")
mycursor.execute("use bank")
#creating required tables 
mycursor.execute("create table if not exists bank_master(acno char(4) primary key,name varchar(30),city char(20),mobileno char(10),balance int(6))")
mycursor.execute("create table if not exists banktrans(acno char (4),amount int(6),dot date,ttype char(1),foreign key (acno) references bank_master(acno))")
mydb.commit()
while(True):
    
    print("1=Create account")
    print("2=Deposit money")
    print("3=Withdraw money")
    print("4=Display account")
    print("5=Exit")
    ch=int(input("Enter your choice:"))
    
#PROCEDURE FOR CREATING A NEW ACCOUNT OF THE APPLICANT
    if(ch==1):
        print("All information prompted are mandatory to be filled")
        acno=str(input("Enter account number:"))
        name=input("Enter name(limit 35 characters):")
        city=str(input("Enter city name:"))
        mn=str(input("Enter mobile no.:"))
        balance=0
        mycursor.execute("insert into bank_master values('"+acno+"','"+name+"','"+city+"','"+mn+"','"+str(balance)+"')")
        mydb.commit()
        print("Account is successfully created!!!")
        
#PROCEDURE FOR UPDATIONG DETAILS AFTER THE DEPOSITION OF MONEY BY THE APPLICANT
    elif(ch==2):
        acno=str(input("Enter account number:"))
        dp=int(input("Enter amount to be deposited:"))
        dot=str(input("Enter date of Transaction: YYYY-MM-DD "))
        ttype="d"
        mycursor.execute("insert into banktrans values('"+acno+"','"+str(dp)+"','"+dot+"','"+ttype+"')")
        mycursor.execute("update bank_master set balance=balance+'"+str(dp)+"' where acno='"+acno+"'")
        mydb.commit()
        print("money has been deposited successully!!!")
#PROCEDURE FOR UPDATING THE DETAILS OF ACCOUNT AFTER THE WITHDRAWL OF MONEY BY THE APPLICANT

    elif(ch==3):
        acno=str(input("Enter account number:"))
        wd=int(input("Enter amount to be withdrawn:"))
        dot=str(input("enter date of transaction: YYYY-MM-DD "))
        ttype="w"
        mycursor.execute("insert into banktrans values('"+acno+"','"+str(wd)+"','"+dot+"','"+ttype+"')")
        mycursor.execute("update bank_master set balance=balance-'"+str(wd)+"' where acno='"+acno+"'")
        mydb.commit()

#PROCEDURE FOR DISPLAYING THE ACCOUNT OF THE ACCOUNT HOLDER AFTER HE/SHE ENTERS HIS/HER ACCOUNT NUMBER
    elif(ch==4):
        acno=str(input("Enter account number:"))
        mycursor.execute("select * from bank_master where acno='"+acno+"'")
        for i in mycursor:
            print(i)
    else:
        break
        

Output:

****BANK TRANSACTION****
1=Create account
2=Deposit money
3=Withdraw money
4=Display account
5=Exit
Enter your choice:1
All information prompted are mandatory to be filled
Enter account number:1001
Enter name(limit 35 characters):"Jitendra Singh"
Enter city name:"New Delhi"
Enter mobile no.:1234567890
Account is successfully created!!!

Enter your choice:2
Enter account number:1001
Enter amount to be deposited:5000
Enter date of Transaction: YYYY-MM-DD 2021-10-10
money has been deposited successully!!!

Enter your choice:3
Enter account number:1001
Enter amount to be withdrawn:1000
enter date of transaction: YYYY-MM-DD 2021-10-11

Enter your choice:4
Enter account number:1001
('1001', '"Jitendra Singh"', '"New Delhi"', '1234567890', 4000)
1=Create account
2=Deposit money
3=Withdraw money
4=Display account
5=Exit

Explanation:

This is a Python code for a basic bank transaction system. It uses a MySQL database to store account and transaction details.

The code creates a “bank” database if it does not already exist and then creates two tables in the database – “bank_master” and “banktrans”. The “bank_master” table stores details of each account holder, such as account number, name, city, mobile number, and account balance. The “banktrans” table stores transaction details, such as account number, amount, date of transaction, and transaction type (deposit or withdrawal).

The code then presents a menu to the user with the following options:

1. Create account
2. Deposit money
3. Withdraw money
4. Display account
5. Exit

If the user chooses to create an account, the code prompts for details such as account number, name, city, and mobile number. It then inserts the details into the “bank_master” table and sets the account balance to 0.

If the user chooses to deposit money, the code prompts for the account number and amount to be deposited. It then inserts a transaction record into the “banktrans” table with the account number, amount, current date, and transaction type “d”. It also updates the account balance in the “bank_master” table.

If the user chooses to withdraw money, the code prompts for the account number and amount to be withdrawn. It then inserts a transaction record into the “banktrans” table with the account number, amount, current date, and transaction type “w”. It also updates the account balance in the “bank_master” table.

If the user chooses to display an account, the code prompts for the account number and then retrieves and displays the account details from the “bank_master” table.

If the user chooses to exit, the code breaks out of the while loop and ends.

Functions used in this Python code

1. mysql.connector.connect(): This function is used to create a connection to a MySQL database. It takes in several parameters, such as the host, user, and password, and returns a MySQLConnection object.
2. cursor(): This method is called on a MySQLConnection object and returns a Cursor object. A Cursor object is used to interact with a MySQL database.
3. execute(): This method is called on a Cursor object and is used to execute a MySQL query. It takes a single parameter, which is the MySQL query to be executed.
4. commit(): This method is called on a MySQLConnection object and is used to commit any changes made to the database. This is necessary to ensure that changes made to the database are permanent.
5. input(): This function is used to take user input. It prompts the user to enter a value and returns the input as a string.
6. str(): This function is used to convert a value to a string.
7. int(): This function is used to convert a value to an integer.
8. break: This keyword is used to break out of a loop.
9. if...elif...else: This is a conditional statement used to execute different blocks of code depending on the condition. The if block is executed if the condition is true, the elif block is executed if the previous condition is false and the current condition is true, and the else block is executed if all the previous conditions are false.
10. for loop: This is a loop that is used to iterate over a sequence of values. In this code, it is used to iterate over the result of a MySQL query and print the values.
11. primary key: This is a column or group of columns in a database table that uniquely identifies each row. In this code, the “acno” column in the “bank_master” table is set as the primary key, which means that each account number can only appear once in the table.
12. foreign key: This is a column or group of columns in a database table that references the primary key of another table. In this code, the “acno” column in the “banktrans” table is set as a foreign key that references the “acno” column in the “bank_master” table. This ensures that each transaction in the “banktrans” table corresponds to a valid account in the “bank_master” table.

You should also check:

• Important questions for Practical Viva

• Class 12 Practical File for Term 2 Practical Examination

• More Python Projects