To maintain Bank Customer Details Like accno,name, balance, mobno and emailid
To create a bank database
#to create a database
import pymysql
db=pymysql.connect("localhost","root","")
c=db.cursor()
sql="create database bank;"
c.execute(sql)
db.close()
To create a table in the above created database
#to create a table
import pymysql
db=pymysql.connect("localhost","root","","bank")
c=db.cursor()
sql="""
create table banked(
accno char(15),
name char(20),
bal decimal(10),
mobno char(15),
emailid char(20)); """
c.execute(sql)
db.close()
To Insert record in the above created table
#to take input for details and save in database
import pymysql
db=pymysql.connect("localhost","root","","bank")
c=db.cursor()
a=input("enter accno")
n=input("enter name")
b=float(input("enter balance"))
m=input("enter mobno")
e=input("enter emailid")
try:
c.execute("insert into banked(accno,name,bal,mobno,emailid) values(%s,%s,%s,%s,%s)"(a,n,b,m,e))
db.commit()
print("record saved")
except:
db.rollback()
db.close()
Display all the record from the table
#display the records from table
import pymysql
db=pymysql.connect("localhost","root","","bank")
try:
c=db.cursor()
sql='select * from banked;'
c.execute(sql)
print("number of rows: ",countrow)
data=c.fetchall()
for eachrow in data:
print(eachrow)
except:
db.rollback()
db.close()
2nd method to display all the record from the table
#display the records from the table field by field
import pymysql
db=pymysql.connect("localhost","root","","bank")
try:
c=db.cursor()
sql='select * from banked;'
c.execute(sql)
countrow=c.execute(sql)
print("number of rows: ",countrow)
data=c.fetchall()
print("Accno Name Bal Mobno Emailid")
for eachrow in data:
a=eachrow[0]
n=eachrow[1]
b=eachrow[2]
m=eachrow[3]
e=eachrow[4]
print(a," ",n," ",b," ",m," ",e)
except:
db.rollback()
db.close()
To search a record on the basis of accno (account number of customer)
#search a record by accno field by field
import pymysql
db=pymysql.connect("localhost","root","","bank")
try:
z=0
accno=int(input("Enter accno to search"))
c=db.cursor()
sql='select * from banked;'
c.execute(sql)
print("number of rows: ",countrow)
data=c.fetchall()
for eachrow in data:
a=eachrow[0]
n=eachrow[1]
b=eachrow[2]
m=eachrow[3]
e=eachrow[4]
if(a==accno):
z=1
print(a,n,b,m,e)
if(z==0):
print("Record is not present")
except:
db.rollback()
db.close()
Search a record on the basis of name of the customer
#search a record by name field by field
import pymysql
db=pymysql.connect("localhost","root","","bank")
try:
z=0
name=input("Enter name to search")
c=db.cursor()
sql='select * from banked;'
c.execute(sql)
print("number of rows: ",countrow)
data=c.fetchall()
for eachrow in data:
a=eachrow[0]
n=eachrow[1]
b=eachrow[2]
m=eachrow[3]
e=eachrow[4]
if(n==name):
z=1
print(a,n,b,m,e)
if(z==0):
print("Record is not present")
except:
db.rollback()
db.close()
Delete a record on the basis of accno of customer
#searching a record by accno and deleting it
import pymysql
db=pymysql.connect("localhost","root","ct","bank")
try:
z=0
accno=int(input("Enter accno to search and delete"))
c=db.cursor()
sql='select * from banked;'
c.execute(sql)
countrow=c.execute(sql)
print("number of rows: ",countrow)
data=c.fetchall()
for eachrow in data:
a=eachrow[0]
n=eachrow[1]
b=eachrow[2]
m=eachrow[3]
e=eachrow[4]
if(a==accno):
z=1
print(a,n,b,m,e)
if(z==0):
print("Record is not present")
else:
a=accno
sql1="delete from banked where accno= ",accno
print(sql1)
c.execute(sql1)
db.commit()
except:
db.rollback()
db.close()
Delete a record on the basis of name of customer
#searching a record by name and deleting it
import pymysql
db=pymysql.connect("localhost","root","ct","bank")
try:
z=0
name=input("Enter name to search and delete")
c=db.cursor()
sql='select * from banked;'
c.execute(sql)
countrow=c.execute(sql)
print("number of rows: ",countrow)
data=c.fetchall()
for eachrow in data:
a=eachrow[0]
n=eachrow[1]
b=eachrow[2]
m=eachrow[3]
e=eachrow[4]
if(n==name):
z=1
print(a,n,b,m,e)
if(z==0):
print("Record is not present")
else:
n=name
sql1="delete from banked where name= ",name
print(sql1)
c.execute(sql1)
db.commit()
except:
db.rollback()
db.close()
To search and display records on the basis of balance in account
#search a record by balance field b field
import pymysql
db=pymysql.connect("localhost","root","ct","bank")
try:
z=0
bal=float(input("Enter balance to search"))
c=db.cursor()
sql='select * from banked;'
c.execute(sql)
print("number of rows: ",countrow)
data=c.fetchall()
for eachrow in data:
a=eachrow[0]
n=eachrow[1]
b=eachrow[2]
m=eachrow[3]
e=eachrow[4]
if(b==bal):
z=1
print(a,n,b,m,e)
if(z==0):
print("Record is not present")
except:
db.rollback()
db.close()
