Bank Management System Python MySQL project
Bank Management System Python MySQL project for class 12 computer science students with source code and output to download. This is one of the core projects that almost all computer science students need to prepare. Bank Management System Python MySQL project helps them to understand the different techniques of Database but also helps them to understand the business rules, software needs to follow.
We tried our best to keep the Banking System Project as simple as we can. Only two Tables has been used throughout the project.
Database of Banking System is maintained in MySQL tables- Customer and Transaction.
The structure of the Customer Table is as follows
mysql> desc customer; +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | acno | int(11) | NO | PRI | NULL | auto_increment | | name | char(30) | YES | | NULL | | | address | varchar(100) | YES | | NULL | | | phone | varchar(15) | YES | | NULL | | | email | varchar(80) | YES | | NULL | | | aadhar_no | varchar(20) | YES | | NULL | | | acc_type | varchar(20) | YES | | NULL | | | status | char(15) | YES | | NULL | | | balance | float(15,2) | YES | | NULL | | +-----------+--------------+------+-----+---------+----------------+ 9 rows in set (0.02 sec)
Note:
- Account number is defined as a primary key as well as this will auto_increment.
- Account number is also serving as a foreign key in Transaction Table
Structure of the Transaction Table
mysql> desc transaction; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | tid | int(11) | NO | PRI | NULL | auto_increment | | dot | date | YES | | NULL | | | amount | int(10) | YES | | NULL | | | type | char(20) | YES | | NULL | | | acno | int(10) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
Initial Data in these two tables are
mysql> select * from customer; +------+----------------+-------------------+-------------+------------------------+----------------+----------+--------+----------+ | acno | name | address | phone | email | aadhar_no | acc_type | status | balance | +------+----------------+-------------------+-------------+------------------------+----------------+----------+--------+----------+ | 1 | rakesh kumar | cf-4 surya nagar | 987181818 | support@cbsetoday.com | 1234-1243-4545 | saving | active | 12200.00 | | 2 | raju vashistha | A-75-B Brij vihar | 96734344318 | raju@cbsetoday.com | 4545-1243-4545 | current | active | 10000.00 | | 3 | archana | cf04 | 4545456 | archana@bianrynote.com | 1234-5656-4545 | saving | active | 10000.00 | | 4 | ashutosh | d-100 brij vihar | 1122334455 | ashu@gmail.com | 1124-5656-6576 | saving | active | 56000.00 | | 5 | raman singh | e-40 radha bihar | 3344556677 | raman@yahoo.com | 4455-5656-4545 | saving | close | 20000.00 | +------+----------------+-------------------+-------------+------------------------+----------------+----------+--------+----------+ mysql> select * from transaction; +-----+------------+--------+----------+------+ | tid | dot | amount | type | acno | +-----+------------+--------+----------+------+ | 1 | 2020-10-16 | 2000 | deposit | 1 | | 2 | 2020-10-15 | 2000 | deposit | 2 | | 3 | 2020-10-18 | 1200 | withdraw | 1 | | 4 | NULL | 2000 | deposit | 1 | | 5 | 2020-11-30 | 200 | deposit | 1 | | 6 | 2020-11-30 | 2000 | withdraw | 1 | | 7 | 2020-11-30 | 200 | withdraw | 1 | +-----+------------+--------+----------+------+
MySQL.connector is used to make a connection between Python and MySQL. Learn more about how to connect MySQL with Python. MySQL connector is avilable from Python.org site.
The whole code is divided into user-defined Python functions. These functions have been called from the main menu to generate the initial menu system. The source code of the Banking Management System project is as follows
import mysql.connector from datetime import date def clear(): for _ in range(65): print() def account_status(acno): conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() sql ="select status,balance from customer where acno ='"+acno+"'" result = cursor.execute(sql) result = cursor.fetchone() conn.close() return result def deposit_amount(): conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() clear() acno = input('Enter account No :') amount = input('Enter amount :') today = date.today() result = account_status(acno) if result [0]== 'active': sql1 ="update customer set balance = balance+"+amount + ' where acno = '+acno+' and status="active";' sql2 = 'insert into transaction(amount,type,acno,dot) values(' + amount +',"deposit",'+acno+',"'+str(today)+'");' cursor.execute(sql2) cursor.execute(sql1) #print(sql1) #print(sql2) print('\n\namount deposited') else: print('\n\nClosed or Suspended Account....') wait= input('\n\n\n Press any key to continue....') conn.close() def withdraw_amount(): conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() clear() acno = input('Enter account No :') amount = input('Enter amount :') today = date.today() result = account_status(acno) if result[0] == 'active' and int(result[1])>=int(amount): sql1 = "update customer set balance = balance-" + \ amount + ' where acno = '+acno+' and status="active";' sql2 = 'insert into transaction(amount,type,acno,dot) values(' + \ amount + ',"withdraw",'+acno+',"'+str(today)+'");' cursor.execute(sql2) cursor.execute(sql1) #print(sql1) #print(sql2) print('\n\namount Withdrawn') else: print('\n\nClosed or Suspended Account.Or Insufficient amount') wait = input('\n\n\n Press any key to continue....') conn.close() def transaction_menu(): while True: clear() print(' Trasaction Menu') print("\n1. Deposit Amount") print('\n2. WithDraw Amount') print('\n3. Back to Main Menu') print('\n\n') choice = int(input('Enter your choice ...: ')) if choice == 1: deposit_amount() if choice == 2: withdraw_amount() if choice == 3: break def search_menu(): conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() while True: clear() print(' Search Menu') print("\n1. Account No") print('\n2. Aadhar Card') print('\n3. Phone No') print('\n4. Email') print('\n5. Names') print('\n6. Back to Main Menu') choice = int(input('Enter your choice ...: ')) field_name='' if choice == 1: field_name ='acno' if choice == 2: field_name ='aadhar_no' if choice == 3: field_name = 'phone' if choice == 4: field_name = 'email' if choice == 5: field_name = 'name' if choice == 6: break msg ='Enter '+field_name+': ' value = input(msg) if field_name=='acno': sql = 'select * from customer where '+field_name + ' = '+value+';' else: sql = 'select * from customer where '+field_name +' like "%'+value+'%";' #print(sql) cursor.execute(sql) records = cursor.fetchall() n = len(records) clear() print('Search Result for ', field_name, ' ',value) print('-'*80) for record in records: print(record[0], record[1], record[2], record[3], record[4], record[5], record[6], record[7], record[8]) if(n <= 0): print(field_name, ' ', value, ' does not exist') wait = input('\n\n\n Press any key to continue....') conn.close() wait=input('\n\n\n Press any key to continue....') def daily_report(): clear() conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') today = date.today() cursor = conn.cursor() sql = 'select tid,dot,amount,type,acno from transaction t where dot="'+ str(today)+'";' cursor.execute(sql) records = cursor.fetchall() clear() print('Daily Report :',today) print('-'*120) for record in records: print(record[0], record[1], record[2], record[3], record[4]) print('-'*120) conn.close() wait = input('\n\n\n Press any key to continue....') def monthly_report(): clear() conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') today = date.today() cursor = conn.cursor() sql = 'select tid,dot,amount,type,acno from transaction t where month(dot)="' + \ str(today).split('-')[1]+'";' cursor.execute(sql) records = cursor.fetchall() clear() print(sql) print('Monthly Report :', str(today).split( '-')[1], '-,', str(today).split('-')[0]) print('-'*120) for record in records: print(record[0], record[1], record[2], record[3], record[4]) print('-'*120) conn.close() wait = input('\n\n\n Press any key to continue....') def account_details(): clear() acno = input('Enter account no :') conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() sql ='select * from customer where acno ='+acno+';' sql1 = 'select tid,dot,amount,type from transaction t where t.acno='+acno+';' cursor.execute(sql) result = cursor.fetchone() clear() print('Account Details') print('-'*120) print('Account No :',result[0]) print('Customer Name :',result[1]) print('Address :',result[2]) print('Phone NO :',result[3]) print('Email ID :',result[4]) print('Aadhar No :',result[5]) print('Account Type :',result[6]) print('Account Status :',result[7]) print('Current Balance :',result[8]) print('-'*120) cursor.execute(sql1) results = cursor.fetchall() for result in results: print(result[0], result[1], result[2], result[3]) conn.close() wait=input('\n\n\nPress any key to continue.....') def report_menu(): while True: clear() print(' Report Menu') print("\n1. Daily Report") print('\n2. Monthly Report') print('\n3. Account Details') print('\n4. Back to Main Menu') choice = int(input('Enter your choice ...: ')) if choice == 1: daily_report() if choice == 2: monthly_report() if choice == 3: account_details() if choice == 4: break def add_account(): conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() name = input('Enter Name :') addr = input('Enter address ') phone = input('Enter Phone no :') email = input('Enter Email :') aadhar = input('Enter AAdhar no :') actype = input('Account Type (saving/current ) :') balance = input('Enter opening balance :') sql = 'insert into customer(name,address,phone,email,aadhar_no,acc_type,balance,status) values ( ' + name +'","'+ addr+'","'+phone+'","'+email+'","'+aadhar+'","'+actype+'",'+balance+',"active" );' cursor.execute(sql) conn.close() print('New customer added successfully') def modify_account(): conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() clear() acno = input('Enter customer Account No :') print('Modify screen ') print('\n 1. Customer Name') print('\n 2. Customer Address') print('\n 3. Customer Phone No') print('\n 4. Customer Email ID') choice = int(input('What do you want to change ? ')) new_data = input('Enter New value :') field_name='' if choice == 1: field_name ='name' if choice == 2: field_name = 'address' if choice == 3: field_name = 'phone' if choice == 4: field_name = 'email' sql ='update customer set ' + field_name + '="'+ new_data +'" where acno='+acno+';' print(sql) cursor.execute(sql) print('Customer Information modified..') def close_account(): conn = mysql.connector.connect( host='localhost', database='bankproject', user='root', password='') cursor = conn.cursor() clear() acno = input('Enter customer Account No :') sql ='update customer set status="close" where acno ='+acno+';' cursor.execute(sql) print('Account closed') def main_menu(): while True: clear() print(' Main Menu') print("\n1. Add Account") print('\n2. Modify Account') print('\n3. Close Account') print('\n4. Transactio Menu') print('\n5. Search Menu') print('\n6. Report Menu') print('\n7. Close application') print('\n\n') choice = int(input('Enter your choice ...: ')) if choice == 1: add_account() if choice == 2: modify_account() if choice == 3: close_account() if choice ==4 : transaction_menu() if choice ==5 : search_menu() if choice == 6: report_menu() if choice ==7 : break if __name__ == "__main__": main_menu()
The output of the above code is
Main Menu 1. Add Account 2. Modify Account 3. Close Account 4. Transactio Menu 5. Search Menu 6. Report Menu 7. Close application Enter your choice ...:
second screen for adding a new customer to bank
Enter your choice ...: 1 Enter Name :samriddhi Enter address f12-surya nagar Enter Phone no :566778785 Enter Email :samrisshi@gmail.com Enter AAdhar no :2345-5667-6767 Account Type (saving/current ) :saving Enter opening balance :20000
close account screen
Enter customer Account No :4 Account closed Press any key to continue....
Transaction Menu
Trasaction Menu 1. Deposit Amount 2. WithDraw Amount 3. Back to Main Menu Enter your choice ...:
Search Menu
Search Menu 1. Account No 2. Aadhar Card 3. Phone No 4. Email 5. Names 6. Back to Main Menu Enter your choice ...:
Report Menu
Report Menu 1. Daily Report 2. Monthly Report 3. Account Details 4. Customer Records 5. Back to Main Menu Enter your choice ...:
Besides all these menus, there are lots of other features that are explained in this Video.
Download Source code in Zip format. Zip file contains Banking System Python MySQL project file and MySQL backup file. All you need to restore this file in your MySQL server.
Limitations of Bank Management Project
Though this project seems to be complete but there are some limitations of this project. Few of them are as follows
- The system is directly accessible by anyone. A login system can be maintained to reduce misuse.
- Reports can be made more user friendly
- More report options can be there.
- Searching options can be increased.
- GUI version is highly recommended.
- Dealing with only the core functionality of the bank.
Conclusion
Besides all the features and shortcomings, this is one of the best Bank Management System project designed for the student. Here students will learn modular programming, connectivity with Python, and how to implement business rules in any software.