Interface Python with MySQL
[Link] library is used to connect to MySql Database.
A data base connection object controls the connection to the database.
It represents a unique session with a database connected from within a
script or program.
A database cursor is a special control structure that facilitates the row
by row processing of records in the resultset i.e the set of records that
retrieved as per the query.
The resultset refers to a logical set of records that are fetched from the
database by executing an SQL query and made available to the
application program.
The [Link] returns how many rows have been so far retrieved
through fetch..() methods from the [Link] it is a DML statement it
retrieves the number of records affected by the query.
connect() function of [Link] establishes connection to a
MySQL database . connect() function takes four parameters
host,user,passwd and database .
is_connected() function is used to check for successful connection . It
returns True /False .
cursor() function is used to create a cursor object.
execute() function is used to execute a query.
fetchall() function returns all the records retrieved as per queryin a tuple
form.
fetchmany() accepts number of records to fetch and returns a tuple
where each record itself is a tuple .If there are not more records it
returns an empty tuple.
fetchone() function returns on record from the result set as tuple.
close() function is used to close the connection .
commit() function is used to reflect the changes in the database
physically after executing insert/ update/ delete command.
The [Link] returns how many rows have been so far retrieved
through fetch..() methods from the cursor. If it is a DML statement it
retrieves the number of records affected by the query.
Parametrised query – The queries which are based on some parameters
or values that we provide from outside are called parametrised query.
Parameters are provided through query strings. Character or string
parameters should be quoted.
Example :
Query= “select * from student where marks>%s” %(80,)
Query=“select * from student where marks>{}” .format(80)
Query1=“insert into employees values(%s,’%s’,%s)” %(id,name,salary)
Query1=“insert into employees values({},’{}’,{})” .format(id,name,salary)
Query2=“delete from employees where id=%s”
Id=(2)
[Link](query2,id)
Table: Employees
Columns : eid int ,ename varchar(100),salary float
Programs to operate employees table from Python environment.
#To retrieve all employee records
import [Link]
mycon=[Link](host="localhost",user="root" ,
passwd="root" , database="mydb")
if(mycon.is_connected()):
print("conncted successfully")
else:
print("not connected")
cursor=[Link]()
[Link]("select * from employees")
data=[Link]()
count=[Link]
print("Number of records " ,count)
for row in data:
print(row)
#WAP in python that displays first three rows fetched
from employees table
import [Link] as sqltor
mycon=[Link](host="localhost",user="learner",pa
ssword="fast",database="test")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
[Link]("select * from employees")
data=[Link](3)
for row in data:
print(row)
[Link]()
#Display number of records present in a table
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="mydb")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
else:
print("There is a connectivity Error..")
cursor=[Link]()
[Link]("select * from employees")
data=[Link]()
count=[Link]
print("There are ",count,"Records available")
[Link]()
#Fetch specific record of employee based on given id
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="mydb")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
[Link]("select ename,salary from employees
where eid=1")
data=[Link]()
print(data)
[Link]()
#Parametrized [Link] employee records who are
getting above the given saalry.
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="mydb")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
var=int(input("Enter the required salary "))
query="select * from employees where salary>%s" %(var,)
[Link](query)
data=[Link]()
for row in data:
print(row)
[Link]()
#Insert record into employee table
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="mydb")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
id=int(input("Enter the empoyee id"))
name=input("Enter the employee name")
salary=float(input("Enter the salary"))
st="insert into employees
values({},'{}',{})".format(id,name,salary)
[Link](st)
[Link]()
print("inserted successfully")
[Link]()
#Update a record
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="mydb")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
id=int(input("Enter the employee id"))
newsal=int(input("Enter the new salary"))
updatequery="update employees set salary={} where eid
={}".format(newsal,id)
[Link](updatequery)
[Link]()
print("Update successfully")
[Link]()
#Delete a record
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="mydb")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
id=int(input("Enter the employee id"))
deletequery="delete from employee where
eid={}".format(id)
[Link](deletequery)
[Link]()
print("Employee with id ",id,"is Removed successfully")
[Link]()
The Books Table of test database contains the records
shown here.
Title ISBN
Die To Live 781224
Again? 123452
Ushakaal 126789
Ushakiran 129875
Sugandha 876543
#Write a Python database connectivity code to insert
record in the above table
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="test")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
title=input("Enter the BookTitle")
isbn=input("Enter the ISBN")
st="insert into book values('{}','{}')".format(title,isbn)
[Link](st)
[Link]()
print("Inserted successfully")
[Link]()
Output 1:
Successfully connected to MYSQL Database
Enter the BookTitle Die To Live
Enter the ISBN 781224
Inserted successfully
Output 2:
Successfully connected to MYSQL Database
Enter the BookTitle Again ?
Enter the ISBN 123452
Inserted successfully
# Write a Python database connectivity code to View all
the records of book table
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="test")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
else:
print("There is a connectivity Error..")
cursor=[Link]()
[Link]("select * from book")
data=[Link]()
print("Title", "ISBN", sep=" - ")
for d in data:
print(d[0],d[1],sep=' - ')
[Link]()
Output:
Successfully connected to MYSQL Database
Title - ISBN
Die To Live - 781224
Again ? - 123452
#Write a Python database connectivity code to update
Title of a given ISBN in the above table
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="test")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
isbn=int(input("Enter the Book ISBN"))
newtitle=input("Enter the new title")
updatequery="update book set title= '{} ' where isbn
={}".format(newtitle,isbn)
[Link](updatequery)
[Link]()
count=[Link]
print(count ," Rows updated successfully")
[Link]()
Output :
Successfully connected to MYSQL Database
Enter the Book ISBN 123452
Enter the new title NotAgain ?
1 Row updated successfully
#Write a Python database connectivity code to delete
record in the above table based on given title
import [Link] as sqltor
mycon=[Link](host="localhost",user="root",pass
word="root",database="test")
if mycon.is_connected():
print("Successfully connected to MYSQL Database")
cursor=[Link]()
isbn=int(input("Enter the isbn of book to delete"))
deletequery="delete from book where
isbn={}".format(isbn)
[Link](deletequery)
[Link]()
count=[Link]
print(count , "records Removed successfully")
[Link]()
Output:
Successfully connected to MYSQL Database
Enter the isbn of book to delete 123452
1 records Removed successfully