My 12th grade CS project on python - mysql connectivity
import mysql.connector
mydb = mysql.connector.connect(user = 'root',
passwd = '1234',
auth_plugin = 'mysql_native_password',
database = 'BankDB2'
)
mycursor = mydb.cursor(buffered = True)
def Menu():
print("*" * 157)
print("|==========================================================|".center(150))
print("| <<< WELCOME TO A PROJECT >>> |".center(150))
print("| <<< ON PYTHON - MYSQL CONNECTIVITY >>> |".center(150))
print("| <===> |".center(150))
print("| <<< BY RUDRADEV ARYA OF CLASS XII - A >>> |".center(150))
print("|==========================================================|".center(150))
print("| MAIN MENU |".center(150))
print("|==========================================================|".center(150))
print("| 1. Insert Record/s |".center(150))
print("|==========================================================|".center(150))
print("| 2. Display Record/s as per Account Number |".center(150))
print("| a. Sorted as per Account Number |".center(150))
print("| b. Sorted as per Customer Number |".center(150))
print("| c. Sorted as per Customer Balance |".center(150))
print("|==========================================================|".center(150))
print("| 3. Search Record Details as per the account number |".center(150))
print("|==========================================================|".center(150))
print("| 4. Update Record |".center(150))
print("|==========================================================|".center(150))
print("| 5. Delete Record |".center(150))
print("|==========================================================|".center(150))
print("| 6. Transactions Debit/Withdraw from the account |".center(150))
print("| a. Debit/Withdraw from the account |".center(150))
print("| b. Credit into the Account |".center(150))
print("|==========================================================|".center(150))
print("| 7. EXIT |".center(150))
print("|==========================================================|".center(150))
print("*" * 157)
def MenuSort():
print(" a. Sorted as per Account Number".center(150))
print(" b. Sorted as per Customer Number".center(150))
print(" c. Sorted as per Customer Balance".center(150))
print(" d. BACK".center(150))
def MenuTransaction():
print(" a. Debit/Withdraw from the account".center(150))
print(" b. Credit into the account".center(150))
print(" c. BACK".center(150))
def Create():
try:
mycursor.execute('create table bank(ACCNO varchar(10), NAME varchar(20), MOBILE varchar(10), EMAIL varchar(20), ADDRESS varchar(20), CITY varchar(10), COUNTRY varchar(20), BALANCE integer(15))')
print(" [ Table Created ] ".center(52))
Insert()
except:
print(" [ Table Created ] ".center(52))
Insert()
def Insert():
while True:
Acc = input(" [ Enter Account Number ] :> ".center(52))
Name = input(" [ Enter Name ] :> ".center(52))
Mob = input(" [ Enter Mobile ] :> ".center(52))
Email = input(" [ Enter Email ] :> ".center(52))
Add = input(" [ Enter Address ] :> ".center(52))
City = input(" [ Enter City ] :> ".center(52))
Country = input(" [ Enter Country ] :> ".center(52))
Bal = float(input(" [ Enter Balance ] :> ".center(52)))
Rec = [Acc, Name.upper(), Mob, Email.upper(), Add.upper(), City.upper(), Country.upper(), Bal]
Cmd = "insert into BANK values(%s,%s,%s,%s,%s,%s,%s,%s)"
mycursor.execute(Cmd,Rec)
mydb.commit()
ch = input(" [ Do you want to enter more records ] :> ")
if ch=='N' or ch=='n':
break
def DispSortAcc():
try:
Cmd = "Select * from BANK order by ACCNO"
mycursor.execute(Cmd)
S = mycursor.fetchall()
F = "%15s %15s %15s %15s %15s %15s %15s %15s"
print(F % ("ACCNO", "NAME", "MOBILE", "EMAIL ADDRESS", "ADDRESS", "CITY", "COUNTRY", "BALANCE"))
print("="*157)
for i in S:
for j in i:
print("%14s" % j, end = ' ')
print()
print("="*157)
except:
print(" [ Table dosent EXIST ] :> ".center(52))
def DispSortName():
try:
Cmd = "Select * from BANK order by NAME"
mycursor.execute(Cmd)
S = mycursor.fetchall()
F = "%15s %15s %15s %15s %15s %15s %15s %15s"
print(F % ("ACCNO", "NAME", "MOBILE", "EMAIL ADDRESS", "ADDRESS", "CITY", "COUNTRY", "BALANCE"))
print("="*157)
for i in S:
for j in i:
print("%14s" % j, end = ' ')
print()
print("="*157)
except:
print(" [ Table dosent EXIST ] :> ")
def DispSortBal():
try:
Cmd = "Select * from BANK order by Balance"
mycursor.execute(Cmd)
S = mycursor.fetchall()
F = "%15s %15s %15s %15s %15s %15s %15s %15s"
print(F % ("ACCNO", "NAME", "MOBILE", "EMAIL ADDRESS", "ADDRESS", "CITY", "COUNTRY", "BALANCE"))
print("="*157)
for i in S:
for j in i:
print("%14s" % j, end = ' ')
print()
print("="*157)
except:
print(" [ Table dosent EXIST ] :> ".center(52))
def DispSearchAcc():
try:
Cmd = "Select * from BANK"
mycursor.execute(Cmd)
S = mycursor.fetchall()
ch = input(" [ Enter the Account Number To Be Searched ] :> ".center(52))
for i in S:
if i[0] == ch:
print("="*157)
F = "%15s %15s %15s %15s %15s %15s %15s %15s"
print(F % ("ACCNO", "NAME", "MOBILE", "EMAIL ADDRESS", "ADDRESS", "CITY", "COUNTRY", "BALANCE"))
print("="*157)
for j in i:
print("%14s" % j, end = ' ')
print()
break
else:
print(" [ Record Not Found ] :> ".center(52))
except:
print(" [ Table dosent EXIST ] :> ".center(52))
def Update():
try:
Cmd = "SELECT * FROM BANK"
mycursor.execute(Cmd)
S = mycursor.fetchall()
A = input(" [ Enter the account number whose details are to be changed ] :> ")
for i in S:
i = list(i)
if i[0] == A:
ch = input(" [ Change Name ( Y / N ) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[1] = input(" [ Enter Name ] :> ".center(52))
i[1] = i[1].upper()
ch = input(" [ Change Mobile ( Y / N ) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[2] = input(" [ Enter Mobile ] :> ".center(52))
ch = input(" [ Change Email ( Y / N ) :> ".center(52))
if ch == 'y' or ch == 'Y':
i[3] = input(" [ Enter Email ] :> ".center(52))
i[3] = i[3].upper()
ch = input(" [ Change Addess ( Y / N ) :> ".center(52))
if ch == 'y' or ch == 'Y':
i[4] = input(" [ Enter Addess ] :> ".center(52))
i[4] = i[4].upper()
ch = input(" [ Change City ( Y / N ) :> ".center(52))
if ch == 'y' or ch == 'Y':
i[5] = input(" [ Enter City ] :> ".center(52))
i[5] = i[5].upper()
ch = input(" [ Change Country ( Y / N ) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[6] = input(" [ Enter Country ] :> ".center(52))
i[6] = i[6].upper()
ch = input(" [ Change Balance ( Y / N ) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[7] = float(input(" [ Enter Balance ] :> ".center(52)))
Cmd = "UPDATE BANK SET NAME = %s, MOBILE = %s, EMAIL = %s, ADDRESS = %s, CITY = %s, COUNTRY = %s, BALANCE = %s WHERE ACCNO = %s "
val = (i[1], i[2], i[3], i[4], i[5], i[6], i[7], i[0])
mycursor.execute(Cmd,val)
mydb.commit()
print(" [ Account Updated ]".center(52))
break
else:
print(" [ Record Not Found ] ".center(52))
except:
print(" [ No Such Table ] ".center(52))
def Delete():
try:
Cmd = "Select * from BANK"
mycursor.execute(Cmd)
S = mycursor.fetchall()
A = input(" [ Enter the account number whose details are to be changed ] :> ".center(52))
for i in S:
i = list(i)
if i[0] == A:
Cmd = "Delete from bank where accno = %s"
val = (i[0],)
mycursor.execute(Cmd, val)
mydb.commit()
print(" [ Account Deleted ] ".center(52))
break
else:
print(" [ Record Not Found ] ".center(52))
except:
print(" [ No Such Table EXISTS ] ".center(52))
def Debit():
try:
Cmd = "select * from BANK"
mycursor.execute(Cmd)
S = mycursor.fetchall()
print(" [ Please Note that the money can only be debited if Min balance of Rs 5000 exists ] ".center(52))
acc = input(" [ Enter Note that the account number from which the money is to be debited ] ".center(52))
for i in S:
i = list(i)
if i[0] == acc:
Amt = float(input(" [ Enter the amount to be withdrawn ] :> ".center(52)))
if i[7] - Amt >= 5000:
i[7] -= Amt
Cmd = "UPDATE BANK SET BALANCE = %s WHERE ACCNO = %s"
val = (i[7], i[0])
mycursor.execute(Cmd, val)
mydb.commit()
print(" [ The Entered Amount Debited ] ".center(52))
break
else:
print(" [ Sorry Try Again There must be min balance of Rs 5000 ] ".center(52))
break
else:
print(" [ Record not found ] ".center(52))
except:
print(" [ Table Dosent EXIST ] ".center(52))
def Credit():
try:
Cmd = "select * from BANK"
mycursor.execute(Cmd)
S = mycursor.fetchall()
acc = input(" [ Enter the Account number in which money is to be credited ] :> ".center(52))
for i in S:
i = list(i)
if i[0] == acc:
Amt = float(input(" [ Enter the amount to be credited ] :> ".center(52)))
i[7] += Amt
Cmd = "UPDATE BANK SET BALANCE = %s WHERE ACCNO = %s"
val = (i[7], i[0])
mycursor.execute(Cmd, val)
mydb.commit()
print(" [Amount Credited] ".center(52))
break
else:
print(" [Record Not FOUND] ".center(52))
except:
print(" [Table Dosent EXIST] ".center(52))
while True:
Menu()
ch = input(" [ Please Enter your Choice] :> ".center(52))
if ch == "1":
Create()
elif ch == "2":
while True:
MenuSort()
ch1 = input(" [ Please Enter choice a / b / c / d] :> ".center(52))
if ch1 in ['a','A']:
DispSortAcc()
elif ch1 in ['b','B']:
DispSortName()
elif ch1 in ['c','C']:
DispSortBal()
elif ch1 in ['d','D']:
print(" [Back to the MAIN MENU] ".center(52))
break
else:
print(" [Invalid choice] ".center(52))
elif ch == "3":
DispSearchAcc()
elif ch == "4":
Update()
elif ch == "5":
Delete()
elif ch == "6":
while True:
MenuTransaction()
ch1 = input(" [Enter choice a / b / c] :> ".center(52))
if ch1 in ['a','A']:
Debit()
elif ch1 in ['b','B']:
Credit()
elif ch1 in ['c','C']:
print(" [Back to the MAIN MENU] ".center(52))
break
else:
print(" [Invalid choice] ".center(52))
elif ch == "7":
print(" [EXITING...... :) ] ".center(52))
break
else:
print(" [Wrong Choice Entered] ".center(52))
def Update():
try:
Cmd = "select * from BANK"
mycursor.execute(Cmd)
S = mycursor.fetchall()
A = input(" [ Please Enter the account number whose details to be changed ] :> ".center(52))
for i in S:
i = list(i)
if i[0] == A:
ch = input(" [ Change Name (Y/N) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[1] = input(" [ Enter Name ] :> ".center(52))
i[1] = i[1].upper()
ch = input(" [ Change Mobile(Y/N) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[2] = input(" [ Enter Mobile ] :> ".center(52))
ch = input(" [ Change Email (Y/N) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[3] = input(" [ Enter Email ] :> ".center(52))
i[3] = i[3].upper()
ch = input(" [ Change Address (Y/N) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[4] = input(" [ Enter Address ] :> ".center(52))
i[4] = i[4].upper()
ch = input(" [ Change City (Y/N) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[5] = input(" [ Enter City ] :> ".center(52))
i[5] = i[5].upper()
ch = input(" [ Change Country (Y/N) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[6] = input(" [ Enter Country ] :>".center(52))
i[6] = i[6].upper()
ch = input(" [ Change Balance (Y/N) ] :> ".center(52))
if ch == 'y' or ch == 'Y':
i[7] = float(input(" [ Enter Balance ] :>"))
Cmd = "UPDATE BANK SET NAME = %s, MOBILE = %s, ADDRESS = %s, CITY = %s, COUNTRY = %s, BALANCE = %s, WHERE ACCNO = %s "
val = (i[1], i[2], i[3], i[4], i[5], i[6], i[7], i[0])
mycursor.execute(Cmd, val)
mydb.commit()
print(" [ Account Updated ] ".center(52))
break
else:
print(" [ Record not Found ] ".center(52))
except:
print(" [ No such Table ] ".center(52))
SQL commands:
1) show databases;
2) use bankdb2;
3) select * from bank;
4) show tables;
5) create table banktrials (
-> ACCNO varchar(10),
-> NAME varchar(20),
-> MOBILE varchar(10),
-> EMAIL varchar(20),
-> ADDRESS varchar(20),
-> CITY varchar(10),
-> COUNTRY varchar(20),
-> BALANCE integer(15))
-> ;
6) create table banktrials (ACCNO varchar(10), NAME varchar(20), MOBILE varchar(10), EMAIL varchar(20), ADDRESS varchar(20), CITY varchar(10), COUNTRY varchar(20), BALANCE integer(15));
