Python | CCIT

1
 Python | CCIT

Table of Contents
Database ............................................................................... 3
Database ............................................................................. 3
MySQL Driver/Connector ................................................... 3
PIP ........................................................................................ 3
Class MySQLConnection ..................................................... 4
Class MySQLCursor .............................................................. 6
Database Table .................................................................. 10
Examples .......................................................................11-20

2
 Python | CCIT

Database
 To connect with any database we require a Connector /Driver.
 Python provides us different connectors for different databases.
 Such as
1. MySQL
2. Microsoft SQL Server
3. Oracle
4. SqlLite
5. Sybase

MySQL Driver/Connector
 Python needs a MySQL driver to communicate with MySQL server.
 We have to Download MySQL Driver/Connector.
 To Install My-SQL Connector use PIP tool.
pip install mysql-connector
 This Connector /Driver is defined in package mysql

PIP
 pip is the standard package manager for Python.
 It allows you to install and manage additional packages that are not part of
the Python standard library.
 This tool is by default installed with python.
 This pip tool can be executed from command prompt.
For ex: To install a package
pip install mysql-connector
For ex: To uninstall a package
pip uninstall numpy
For ex: To show list of installed packages
pip list

3
 Python | CCIT

Syntax to use pip tool:

pip <command> [options]
Command can be:
install Install packages.
uninstall Uninstall packages.
list List installed packages.
help Show help for commands.

Class MySQLConnection
 This class is used to manage session with database.
 It is defined in module mysql.connector
Constructor:
 MySQLConnection(**kwargs)
o Will connect with database and return a Connection Object if
successful.
o Arguments can be
o user : The user name used to authenticate
o password : password to authenticate the user
o database : database name
o host : The host name or IP address (default is localhost )
o port : The TCP/IP port of the MySQL server(default is 3306)

NOTE: Same be achiveied by using connect( **kargs) function.

4
 Python | CCIT

Methods:-
 close()
o will close connection with database.
 rollback()
o commits the current transaction.
 close()
o Cancels the current transaction.
 Is connected()
o It returns True when the connection is available, False otherwise.
 cursor()
o This method returns a MySQLCursor() object.
o This Object can be used to send SQL statements to database.
Create a database ccitdb. In that data base create a table accmaster with
columns accno,name and balance. Add 4-5 records in that table accmaster.
Start mysql client command line tool.
It will ask for password:
 Provide password given while installation.
 If password not given then just press enter key.

On SQL prompt type commands:

sql> create database ccitdb;
sql> use ccitdb;
sql> create table accmaster(accno integer,name varchar(20)
,balance integer);
sql> insert into accmaster values(1001,'Amit Jain',50000);
sql> insert into accmaster values(1002,'Gopal Pandey',
250000);
sql> insert into accmaster values(1003,'Mona Mantri',
5
10000);
 Python | CCIT

WAP to connect with a database ccitdb present on your local system.(

userid=root password =admin )

import mysql.connector as sql

try:
conn=sql.MySQLConnection(user="root",password="admin",datab
ase="ccitdb")
print("Connected...")
conn.close()
except Exception as e:
print("Failed to Connect",e)

values are 5 and 10

values are 5 and 10
values are 5 and 10

Class MySQLCursor
 It is used to send a sql statement to database.
 MySQL cursor is read-only and non-scrollable.
 Read-only
o You cannot update data in the underlying table through the cursor.
 Non-scrollable
o You can only fetch rows in the order determined by the SELECT
statement.
o You cannot fetch rows in the reversed order.
o You cannot skip rows or jump to a specific row in the result set.

6
 Python | CCIT

Constructor:
 MySQLCursor(MySQLConnection )
o Creates a cursor from a connection object.
Methods:-
 execute(query, params=None)
o This method executes the given query or command.
o For insert / update / delete operation we can check how many rows
affected by using cursor property rowcount.
o The params argument is used to pass parameter values of the query.
 fetchone( )
o This method return one single record as list.
 fetchall()
o This method return all records as multidimensional list.
 fetchmany(SIZE)
o This method return specific number of records.
Properties:
 rowcount
o Indicates no of rows affected
 with_rows
o returns True or False to indicate whether the most recently executed
operation produced rows.
WAP to insert a record into table accmaster. Database=ccitdb user=root
password=admin.

import mysql.connector as sql

try:
mydb=sql.MySQLConnection(user="root",password="admin",
database="ccitdb")
smt="insert into accmaster values(1010,'Raj Joshi'
7
,25000)"
 Python | CCIT

cur=mydb.cursor()
cur.execute(smt)
if cur.rowcount==1:
print("Record Inserted...")
mydb.commit()
mydb.close()
except Error as e:
print("Failed to Connect",e)

WAP to delete a record from table accmaster.

database=ccitdb user=root password=admin
import mysql.connector as sql
try:
mydb=sql.MySQLConnection(user="root",password="admin"
,database=" ccitdb")
smt="delete from accmaster where accno=1010"
cur=mydb.cursor()
cur.execute(smt)
if cur.rowcount==1:
print("Record deleted...")
8
 Python | CCIT

mydb.commit()
mydb.close()
except Exception as e:
print("Failed to Connect",e)

WAP to read accno, name and balance and add a record into accmaster table.

import mysql.connector as sql

try:
an=input("Enter accno:")
nm=input("Enter name:")
amt=input("Enter amount:")
cmd="insert into accmaster values("+an+",'"+nm+"',"+amt
+")"
conn=sql.connect(database="ccitdb",user="root",password="ad
min")
cur=conn.cursor()
cur.execute(cmd)
if cur.rowcount==1:
print("Record Inserted..")
conn.commit() 9
 Python | CCIT

conn.close()
except Exception as e:
print("Error:",e)

Enter accno: 1004

Enter name: Mona Mantri
Enter amount: 10000
Record Inserted..

Database Table
 create table employee(empno int,name varchar(100),job varchar(100),salary
int,joindate date);
 insert into employee values(1001, 'Amit Jain', 'clerk', 25000, '2012-2-12');
 insert into employee values(1002, 'Sumit Kumar', 'Manager', 50000, '2012-4-
2');
 insert into employee values(1003,'Raj Rathi', 'clerk', 25000, '2014-8-22');
WAP to read accno and amt and perform deposit operation on accmaster table.
database=ccitdb user=root password=admin.
import mysql.connector as sql
try:
an=input("Enter accno:")
amt=input("Enter amount:")
conn=sql.connect(database="ccitdb",user="root",password=
"admin")
cmd="update accmaster set balance=balance+"+amt+" where
accno="+an
10
cur=conn.cursor()
 Python | CCIT

cur.execute(cmd)
if cur.rowcount==1:
print("amount deposited..")
conn.commit()
else:
print("AccNo not found..")
conn.close()
except Exception as e:
print("Error:",e)

Enter accno: 1010

Enter amount: 7000
amount deposited

WAP To read data and insert it into accmaster table.

import mysql.connector as sql
an=input("Enter AccNo: ")
nm=input("Enter Name: ")
bl=input("Enter Balance: ")
try:
query="insert into accmaster values (%s,%s,%s)"
conn=sql.connect(user="root",password="admin",database=
"ccitdb")
cur=conn.cursor()
lst=[an,nm,bl]
cur.execute(query , lst) 11

print("record inserted..")
 Python | CCIT

print("record inserted..")
conn.commit()
conn.close()
except Exception as e:
print("Error:",e)

Enter accno : 1004

Enter name : Mona Mantri
Enter amount : 10000
Record Inserted..

WAP To read accno and delete it from accmaster table.

import mysql.connector as sql
an=input("Enter accno: ")
try:
query="delete from accmaster where accno=%s"
conn=sql.connect(user="root",password="admin",database=
"ccitdb")
cur=conn.cursor()
lst=[an]
cur.execute(query , lst)
if cur.rowcount==1:
print("Record Deleted...")
conn.commit()
conn.close()
except Exception as e:
print("Error:",e) 12

print("record inserted..")
conn.commit()
 Python | CCIT

Enter accno: 1010

Record Deleted...

WAP to read accno and amt. Perform deposit operation on accmaster table

import mysql.connector as sql

an=input("Enter accno: ")
amt=input("Enter amount: ")
try:
query="update accmaster set balance=balance + %s where
accno=%s"
conn=sql.connect(user="root",password="admin",database=
"ccitdb")
cur=conn.cursor()
lst=[amt,an]
cur.execute(query , lst)
if cur.rowcount==1:
print("amount deposited..")
conn.commit()
else:
print("AccNo not found..")
conn.close()
except Exception as e:
print("Error:",e)
print("record inserted..") 13

conn.commit()
 Python | CCIT

Enter accno: 1010

Enter amount: 7000
amount deposited

WAP to read accno and display record of that account from accmaster table.

import mysql.connector as sql

an=input("Enter accno: ")
try:
query="select name,balance from accmaster where accno=%s"
conn=sql.connect(user="root",password="admin",database=
"ccitdb")
cur=conn.cursor()
lst=[an]
cur.execute(query , lst)
row=cur.fetchone()
if row==None:
print("AccNo. not Found")
else:
print("Name is",row[0])
print("Balance is",row[1])
conn.close()
except Exception as e:
print("Error:",e)

14
 Python | CCIT

Enter accno: 1010

Name is Raj Joshi
Balance is 25000

WAP to display all records from accmaster table.

import mysql.connector as sql

try:
query="select * from accmaster"
conn=sql.connect(user="root",password="admin",database=
"ccitdb")
cur=conn.cursor()
cur.execute(query)
rows=cur.fetchall()
for row in rows:
print(row[0],row[1],row[2])
conn.close()
except Exception as e:
print("Error:",e)

1003 Mona Mantri 16000

1004 Amar Agrawal 18500
1006 Mandar Joshi 50000
1007 Sanjay Rathi 25000
1010 Raj Joshi 25000

15
 Python | CCIT

WAP to read accno and amount and perform withdraw operation on account.
(check if balance is available or not).

import mysql.connector as sql

try:
an=input("Enter AccNo:")
amt=input("Enter Amount:")
conn=sql.connect(database="ccitdb",user="root",password=
"admin")
cmd="select balance from accmaster where accno= %s"
cur=conn.cursor()
cur.execute(cmd,[an])
row=cur.fetchone()
if row != None :
bal=row[0]
if bal>=int(amt):
cmd="update accmaster set balance=balance - %s where
accno= %s"
curx=conn.cursor()
curx.execute(cmd,[amt,an] )
conn.commit()
print("Amount withdrawn...")
else:
print("Insufficient Balance ",bal)
else:
print("AccNo Not Found..")
16
conn.close()
except Exception as e:
 Python | CCIT

conn.close()
except Exception as e:
print("Error:",e)

Enter Accno: 1010

Enter Amount: 7000
Amount Withdrawn

WAP to read job and find list of empnames from emp table for that job
import mysql.connector as sql
try:
jb=input("Enter Job:")
conn=sql.connect(database="cmpdb",user="root",password=
"admin")
cmd="select name from emp where job= %s"
cur=conn.cursor()
cur.execute(cmd ,[ jb ])
rows=cur.fetchall()
for row in rows:
print(row[0])
conn.close()
except Exception as e:
print("Error:",e) 17
 Python | CCIT

Enter Job: Clerk

Gopal Pandey
Raja Rathi
Rajev Ranjan

WAP to read empno and amt. Increment salary of that emp by specified amt

import mysql.connector as sql

try:
en=input("Enter EmpNo:")
amt=input("Enter Increment Amt:")
conn=sql.connect(database="cmpdb",user="root",password=
"admin")
cmd="update emp set salary=salary+ %s where empno= %s"
cur=conn.cursor()
cur.execute(cmd ,[amt,en])
if cur.rowcount==1:
print("Done..")
conn.commit()
else:
print("EmpNo Not Found..")
except Exception as e:
print("Error:",e)
finally:
conn.close()
18
 Python | CCIT

Enter EmpNo: 1006

Enter Increment Amt: 7000
Done...

WAP to read name, job, salary and deptno. Insert a record into emp table.

import mysql.connector as sql

try:
nm=input("Enter Name:")
jb=input("Enter Job:")
sl=input("Enter Salary:")
dn=input("Enter Deptno:")
conn=sql.connect(database="cmpdb",user="root",password=
"admin")
cmd="select max(empno) from emp"
cur=conn.cursor()
cur.execute(cmd )
row=cur.fetchone()
if row[0]==None :
en=1001
else:
en=row[0]+1
cmd="insert into emp values(%s,%s,%s,%s,%s)"
curx=conn.cursor()
curx.execute(cmd ,[en,nm,jb,sl,dn] )
print('Record Inserted with empno',en)
19
conn.commit()
conn.close()
 Python | CCIT

conn.commit()
conn.close()
except Exception as e:
print("Error:",e)

Enter Name: Gopal Pandey

Enter Job: manager
Enter Salary: 45000
Enter DeptNo: 20
Record Inserted with empno 1002

20