Inudstrial training project submission

CSE-IV SEM

S.V.POLYTECHNIC COLLEGE, BHOPAL

Data visualiation and machine learning

(computer vision) project for
“MASTERCARD BANKING SYSTEM”
using PYTHON and MYsql

SUBMITTED BY-
:ANMOL MEHRA
 CERTIFICATE
This is to certify that Anmol Mehra of CSE-IV SEM,
S.V. POLYTECHNIC COLLEGE, BHOPAL has completed
his industrial training project as an essential
submission entitled Data visualizationed machine
learning (computer vision ) project for “MASTERCARD
BANKING SYSTEM using PYTHON AND MYsql RDMBS
TECHNOLOGIES
I hereby certify that the project is in the accordance
with the guidelines issue by the POLYTECHNIC and
ITSC.

Project trainer M.D. HOD

ITSC CSE,S.V.Polytechnic

ACKNOWLEDGEMENT
I owe a great many thanks to a many people who helped and
supported me during the training and making this project a
reality.
My deepest thanks to Mr. Dwarika Singh Sir, M.D. I.T.S.C., Mr.
Abhishek Shrivastava Project Manager and Trainer ,I.T.S.C. and
all my college faculties for guiding and correcting various
mistakes with attention and care.
I express my sincere thanks to the respected H.O.D. CSE. Mr Manoj
Sonkusare of S.V.Polytechnic, Bhopal, for extending his all possible
support.
Thanks and appreciation to all the helpful people for their
support. I also extend my heartfelt thanks to my family & well-
wishers for their effective encouragement, guidance, and
patience throughout the project
ANMOL MEHRA

CONTENTS
1.Introduction to python
2.Mysql database system

3.Classification of Sql statements

4.Front end and Back end

5.Minimum hardware and software requirement

6.User working anylysis

7.ER diagram

8.MySql commands

9.Python coding

10.Bibliography

Introduction to Python
What is Python?

Python is a popular programming language. It was created by Guido van Rossum, and
released in 1991.

It is used for:

● web development(server-side),
● software development,

● mathematics,

● scripting

What can python do?

 Python can be used on a server to create web applications.
 Python can be used alongside software to create workflows.
 Python can connect to database systems. It can also readand
modifyfiles.
 Python can be used to handle big data and perform complex
mathematics.

Why Python?

 Python works on different platforms (Windows, Mac,Linux, Raspberry Pi,etc)

 Python has a simple syntax similar to the english language
 Python has syntax that allows developers to write programs with fewer lines
than some other programming languages
 Python runs on an interpreter system, meaning that code can be executed as
soon as it is written. This means that prototyping can be very quick
 Python can be treated in a procedural way, an object-oriented way or a
functional way
Good to know
 The most recent major version of Python is Python 3, which we shall be using
in this tutorial. However, Python 2, although not being updated with
anything other than security updates, is still quite popular.
 In this tutorial Python will be written in a text editor. It is possible to write
Python in an Integrated Development Environment, such a Pycharm,
Netbeans or Eclipse which are particularly useful when managing larger
collections of Python files.
Python Syntax compared to other programming languages

 Python was designed for readability, and has some similarities to the
English language with influence from mathematics
 Python uses new lines to complete a command, as opposed to other
programming languages which often use semicolons or parentheses.
 Python relies on indentation, using whitespace, to define scope, such as
the scope of loops, functions and classes. Other programming languages
often use curly-brackets for this purpose.

MYSQL
MYSql is a freely available open source relational
database management system(RDBMS) that uses
structured query language (SQL). It is downloaded
from www.mysql.org in a MySql database
information is stored here in the table. A single
Mysql databse can contain many tables at once
and stores thousand of individual records.
MySql provides a rich set of features that supports
a secure environment for storing and maintaining
and accessing data in the database. It is fast,
reliable, scable alternative to many of the RDBMS
available.

MYSQL-DATABASE-SYSTEM
My SQL database system refers to the combination

of a MySQL server instance and a MySQL database.

MySQL operates using client/server architecture in

which the server runs on the machine containing

the database and clients connect to the server over

a network. MySQL is a multi-user database system,

meaning several users can access the database

simultaneously.

MYSQL-DATABASE-SYSTEM
My SQL database system refers to the combination of a MySQL server instance and a
MySQL database. MySQL operates using client/server architecture in which the server
runs on the machine containing the database and clients connect to the server over a
network. MySQL is a multi-user database system, meaning several users can access
the database simultaneously.

The sever (MySQL server) listens for client requests coming in over the

network and accesses database contents according to those requests and

provides that to the clients.

Clients are programs that connect to the database server and issue

queries in a pre-specified format. MySQL is compatible with the standards

based SQL (Structured Query Language). The client program may contact

the server programmatically (meaning a program call the server during

execution) or manually.

CLASSIFICATION-OF-SQL-STATEMENTS
 SQL provides many different types of commands used for different purposes. SQL
commands can be divided into following categories:

Commands:- Commands that allows you to perform tasks related to data definition example:-

 Creating, altering and dropping

 Granting and revoking privileges and roles
 Maintaining commands

Data Manipulation language(DML) Commands:- Commands that allows you to perform tasks
related to data manipulation example:-

 Retrieval, Insertion, deletion and modification of data stored in database.

Transaction Control language(TCL) Commands:- Commands that allows you to manage and
control the transaction(a transaction is one complete unit of work involving many steps).
example-
 Making change to database permanently
 Undoing changes to database
 Creating savepoints
 Setting properties for transaction

MySQL was created and is supported by MySQL LAB,a company based in Sweden .This company is
now a subsidiary of Sun Microsystems, which holds the copy right to most of the codebase. On
April 20th, 2009 Oracle database, announce a deal to acquire Sun.

Microsystem

The chief inventor of MySQL was Michael Widenius (a.k.a. Monty). MySQL has been named after
Monty’s daughter My. The logo of MySQL, the dolphine, is named as “Sakila”.

FRONT END:-
The project uses python as its front end that is the part which interacts. The front end basically includes
input facilities through which the user interact. Apart from the user interaction, the front end also contains
database objects that form a layer between the user and BACK END. The project uses python as its front end
that is the part which interacts

BACK END:-
MySQL is used as a BACK END. It handles all database accesses through one or more servers. A Server is a

special computer that is responsible for processing requests send to it. It processes all the data and query

passed to it and sends result back to the front end where it is displayed to theuser.
 HARDWARE AND SOFTWARE
SPECIFICATION

Minimum Hardware Specification of the machine to be used:-

 P1-processor
 32 MB RAM
 14 inch color monitor
 2.1 GB HDD
 233 MHZ CPU clock speed

Minimum Software Specification of the machine to be used (tools &

platforms used):-

 Python 3.6 and above

Project category:-

This project uses GUI provided by Python and MySQL which is a

RDBMS.
 USER WORKING
ANALYSIS

This ER model is for MASTERCARD BANKING

SYSTEM platform which will allow the customers to

register feeding their details once. Using mobile number

to identify account uniquely. Each customer can have his

or her choice for different types of banking purpose,

along with the customer can avail diff kind of banking

services.
ER DIAGRAM
 MY SQL COMMANDS
Coding for 1st table
create table bank_list (

bank_id varchar,

bank_name varchar(30),

branch_number varchar (30),

city varchar (30)

PRIMARY KEY (bank_id)

);

Insertion of data:-

insert into bank_list values ('001', 'sbi', '111',

'bhopal');

insert into bank_list values ('002', 'pnb', '112',

'indore');

insert into bank_list values ('003', 'canara_banK',

'13', 'jabalpur');

insert into bank_list values ('004', 'indian_bank',

'114', 'gwalior');

insert into bank_list values ('005', 'bob', '115',

'raisen');
insert into bank_list values ('006', 'iob', '116',
'bhopal');

insert into bank_list values ('007', 'icici', '117',

'raisen');

insert into bank_list values ('008', 'hdfc', '118',

'jaipur');

insert into bank_list values ('009', 'hsbc', '119',

'delhi');

insert into bank_list values ('010', 'axis', '120',

'mumbai');
Coding for 2nd table:-
create table bank_services (
acc_type varchar (30) primary key,
rate_of_intrest varchar (30),
documents_required varchar (30),
remarks varchar (30));
Insertion into table:-
insert into bank_services values ('current', '20_%_PA',
'aadhar_card_pan_card_
mobile_no',
'best_for_buisness');
insert into bank_services values ('savings', '4_%_PA',
'aadhar_card_mobile_no',
'best_for_personal_use');
insert into bank_services values ('pm_fund_acc_', '0_
%_PA',
'any_identity_proof',
'for_ppl_who_are_bpl');
insert into bank_services values ('DBT_acc', '15_%_PA',
'stock_card_mobile_no',
'for_stock_mark');
insert into bank_services values ('FD_acc', '8_%_PA',
'family_card_aadhar_car
d', 'for_ur_savings');
Coding for 3rd table
create table customer_list (
dt_acc_open varchar (30),
fkbank_id varchar(30) references bank_list(bank_id),
customer_id varchar (30) primary key,
customer_name varchar (30),
phone varchar (30),
city varchar (30));

Insertion into the table:-

insert into customer_list values ('1_jan_2001', '001', '121',

'anmol', '8989898989', 'bhopal');
insert into customer_list values ('22_sep_2007', '002', '122',
'ruru', '7418574125', 'bhopal');
insert into customer_list values ('6_july_2009', '003', '123',
'shaakt', '9632587412', 'bhopal');
insert into customer_list values ('5_aug_2009', '004', '124',
'satwik', '8956237415', 'bhopal');
insert into customer_list values ('7_may_1999', '005', '125',
'shashank', '8529631476', 'bhopal');
insert into customer_list values ('5_april_2016', '006', '126',
'akshat', '9632587415', 'bhopal');
insert into customer_list values ('25_feb_2006', '007', '127',
'shalab', '9876543219', 'bhopal');
insert into customer_list values ('9_march_2009', '008', '128',
'yath', '7894561326', 'bhopal');
insert into customer_list values ('5_april_2007', '009', '129',
'ansh', '6969696969', 'rewa');
insert into customer_list values ('9_feb_2023', '010', '130',
'shoriya', '6352419876', 'jhatkhedi');
Coding for 4th table:-

create table acc_handling (

fkcustomer_id varchar (30) references
customer_list(customer_id),
fkacc_type varchar (30) references bank_services(acc_type),
dt_stop varchar (30),
remarks varchar (30));

Insertion into the table:-

insert into acc_handling values ('121', 'current', '14_jan_2023',

'good_customer');
insert into acc_handling values ('122', 'savings',
'16_may_2015', 'dues_left');
insert into acc_handling values ('123', 'pm_fund_acc',
'16_july_2016', 'loyal_customer');
insert into acc_handling values ('124', 'DBT_acc',
'14_jan_2016', 'average_customer');
insert into acc_handling values ('125', 'Fd_acc',
'still_working_acc', 'good_customer');
insert into acc_handling values ('126', 'Fd_acc', '15_sep_2019',
'dues_left');
insert into acc_handling values ('127', 'savings',
'24_march_2015', 'good_customer');
insert into acc_handling values ('128', 'Fd_acc',
'15_march_2020', 'late_in_deposition');
insert into acc_handling values ('129', 'savings', '14_oct_2007',
'FRAUD');
insert into acc_handling values ('130', 'current',
'still_working_acc', 'average_customer');
Coding for 5th table:-
create table transactions (
fkcustomer_id varchar (30) references
customer_list(customer_id),
transaction_date varchar (30),
transacted_amount varchar (30),
amount_left varchar (300));

Insertion of data:-

insert into transactions values ('121', '11_july_2008, '10000',

'50000');
insert into transactions values ('122', '16_may_2015, '5000',
'156983');
insert into transactions values ('123', '5_june_2011, '145899',
'2000');
insert into transactions values ('124', '17_nov_2016, '2000',
'12365');
insert into transactions values ('125', '19_aug_2005, '1000',
'10000');
insert into transactions values ('126', '20_june_2019, '5000',
'6000');
insert into transactions values ('127', '24_june_2008, '100000',
'5000');
insert into transactions values ('128', '19_jan_2019, '15000',
'9632');
insert into transactions values ('129', '17_sept_2015, '1200',
'1600');
insert into transactions values ('130', '14_feb_2002, '18', '10');
PYTHON COMMANDS
Table 1.

import mysql.connector as connector

import matplotlib.pyplot as plt
con=connector.connect(host='localhost', user='root', password='password', database='mastercard')
if con.is_connected(): print("connection established...") print("\n*******WELCOME mastercard
PROJECT*******")
mycursor = con.cursor() mycursor.execute("SELECT * FROM transactions")
myresult = mycursor.fetchall()
for x in myresult:
print(x) mycursor.execute("select customer_id,transacted_amount FROM transactions")
result = mycursor.fetchall
customer_id=[] transacted_amount=[]
for i in mycursor:
customer_id.append(i[0])
transacted_amount.append(i[1]) print("customer_id = ",customer_id) print(" transacted_amount =
",transacted_amount)
import matplotlib.pyplot as plt
plt.title("Transaction of customers")
plt.plot(customer_id,rating,linestyle='dashed',color='blue')
plt.show()

table 2 :-
import mysql.connector as connector
import matplotlib.pyplot as plt
con=connector.connect(host='localhost', user='root', password='password', database='mastercard')
if con.is_connected():
print("connection established...") print("\n*******WELCOME mastercard PROJECT*******")
mycursor = con.cursor() mycursor.execute
("SELECT * FROM bank_services")
myresult = mycursor.fetchall()
for x in myresult:
print(x) mycursor.execute
("select acc_type ,rate_of_intrest from bank_services”)
result = mycursor.fetchall
acc_type=[] rate_of_intrest=[]
for i in mycursor:
acc_type.append(i[0])
rate_of_intrest.append(i[1])
print("acc_type= ", acc_type) print("rate_of_intrest = ", rate_of_intrest)
max_y_lim=max(rate_o_intrest) min_y_lim=min(rate_of_intrest)-0
plt.title("Rate of intresy or diff account")
plt.barh(acc_type,rate_of_intrest,color=blue)
plt.show()
Table 3:-

import mysql.connector as connector

import matplotlib.pyplot as plt
con=connector.connect(host='localhost', user='root', password='password', database='mastercard')
if con.is_connected():
print("connection established...") print("\n*******WELCOME mastercard PROJECT*******")
mycursor = con.cursor()
mycursor.execute("SELECT * FROM bank_list")
myresult = mycursor.fetchall()
for x in myresult: print(x) mycursor.execute("select bank_id ")
result = mycursor.fetchall bank_id=[]
for i in mycursor:
bank_id.append(i[0])
print("bank_id= ", bank_id)
c=['blue','brown','green','purple','blue','orange',’cyan’, ‘pink’]
plt.title("bank in the cities")
plt.bar(bank_list,color=c)
plt.show()
INSERT UPDATE DELETE ON TABLE
Table 1

INSERT
import mysql.connector
import matplotlib.pyplot as plt
mydb= mysql.connector.connect(host='localhost', user='root', password='password',
database='mastercard')
mycursor=mydb.cursor()
sql="insert into customer_list(dt_acc_open, bank_id,customer_id,customer_name,phone,city)
values(%s,%s,%s,%s,%s,%s)" val=("15_oct_2009","001","121","utkrash", "7418529636","Bhopal”)
mycursor.execute(sql,val) mydb.commit()
print(mycursor.rowcount,"record inserted")

UPDATE
import mysql.connector
import matplotlib.pyplot as plt
mydb= mysql.connector.connect(host='localhost', user='root', password='password',
database='mastercard')
mycursor=mydb.cursor() sql="update bank_id=012 where bank_id=002" mycursor.execute(sql)
mydb.commit() print(mycursor.rowcount,"1 record affected")

DELETE:-
import mysql.connector
import matplotlib.pyplot as plt
mydb= mysql.connector.connect(host='localhost', user='root', password='password',
database='mastercard') mycursor=mydb.cursor() sql="delete from bank_id where bank_id=007"
mycursor.execute(sql) mydb.commit() print(mycursor.rowcount,"records deleted")

:-
 Tab
le 2:-
INSERT
import mysql.connector
import matplotlib.pyplot as plt
mydb= mysql.connector.connect(host='localhost', user='root', password='password',
database='mastercard')
mycursor=mydb.cursor()
sql="insert into bank_services(acc_type, rate_of_intrest,documents_required,remarks) values(%s,
%s,%s,%s)" val=("serving","10%","no documents","good_acc")
mycursor.execute(sql,val) mydb.commit()
print(mycursor.rowcount,"record inserted")

UPDATE
import mysql.connector
import matplotlib.pyplot as plt
mydb= mysql.connector.connect(host='localhost', user='root', password='password',
database='mastercard')
mycursor=mydb.cursor() sql="update rate_of_intrest=12% where rate_of_intrest=8"
mycursor.execute(sql) mydb.commit() print(mycursor.rowcount,"1 record affected")

DELETE:-
import mysql.connector
import matplotlib.pyplot as plt
mydb= mysql.connector.connect(host='localhost', user='root', password='password',
database='mastercard') mycursor=mydb.cursor() sql="delete from bank_id where
acc_type=DBT_acc" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount,"records
deleted")
 BIBLIOGRAPHY
 www.google.com

 www.youtube.com

 www.geeksforgeeks.com

 www.pythonorg.com