1

PYTHON TO SQL
CONNECTIVITY

VIJAYASHANTHI. A – PYTHON NOTES_01 1

 2

VIJAYASHANTHI. A – PYTHON NOTES_01 2

 3

1)A table, named THEATRE, in CINEMA database, has the following

structure:
Fields Type
Th_ID char(5)
Name varchar(15)
City varchar(15)
Location varchar(15)
Seats int
Write a function Delete_Theatre(), to input the value of Th_ID from the
user and permanently delete the corresponding record from the table.
Assume the following for Python-Database connectivity:
Host:localhost, User:root, Password: Ex2025
Ans:
import pymysql as pm #OR import [Link] as pm
def Delete_Theatre():
Mydb=[Link](host=’localhost’, user=’root’, password=’Ex2025’,
database=’CINEMA’)
MyCursor=[Link]()
TID=input(“Theatre ID:”)
Query = “DELETE FROM Theatre WHERE Th_ID=’{ }’”.format(TID)
# or Query = ”DELETE FROM Theatre WHERE Th_ID=’{TID}’ ”
[Link](Query)
[Link]()
[Link]()

2)A table, named ADDRESS, in HOTEL database, has the following

structure:

VIJAYASHANTHI. A – PYTHON NOTES_01 3

 4

Field Type
Name Varchar(15)
Place Varchar(20)
Post_office Varchar(15)
Pincode integer
Write the following Python function to perform the specified
operation:
Addrec(): To input the address of customers and store it in the table
ADDRESS. The function should then display total number of records
whose Place starts with ‘A; in table ADDRESS.
Assume the following for Python-Database connectivity:
Host: localhost, User:root, Password:mytab
Ans.
import [Link] as m
mycon=[Link](host=’localhost’, user=’root’, password=’mytab’,
database=’HOTEL’)
mycur=[Link]()
[Link](‘select * from ADDRESS where Place like “A%” ‘)
data=[Link]()
print(len(data))
[Link]()

3)Write a python code to view all the tables which are available in
database named mydatabase.
Ans:
import [Link] as mycon

VIJAYASHANTHI. A – PYTHON NOTES_01 4

 5

mydb=[Link](host=’localhost’, database=’mydatabase’,
user=’root’, passwd=’1234’)
mycursor=[Link]()
[Link](“show tables”)
print(“Tables available in the database mydatabase1 are:”)
for i in mycursor:
print(i)

4)Write a python code to view the structure of a table (say, Company)

which is available in database named mydatabase.
Ans:
import [Link] as mycon
mydb=[Link](host=’localhost’, database=’mydatabase’,
user=’root’, passwd=’1234’)
mycursor=[Link]()
[Link](“desc company”)
print(“Structure of the table ‘Company’:”)
for i in mycursor:
print(i)
5)A table named Event in VRMALL database has the following
structure:
Field Type
EventID int(9)
EventName Varchar(25)
EventDate Date
Description Varchar(30)

VIJAYASHANTHI. A – PYTHON NOTES_01 5

 6

Write the following Python function to perform the specified

operations:
Input_Disp(): to input details of an event from the user and store into
the table Event.
The function should then display all the records organized in the year
2024.
Assume the following values for Python Database Connectivity: Host-
localhost, user-root, password-tiger.
Ans:
import [Link] as mn def Input_Disp():
con=[Link](host=”localhost”, user=”root”, password=”tiger”,
database=”VRMALL”)
cur=[Link]()
print(“Enter Event Details:”)
eid=input(“ID:”)
ename=input(“NAME:”)
edate=input(“DATE:”)
des=input(“Description:”)
query=insert into Event values(“+eid+”,”’+ename+”’,”’+edate+”’,”’+des+’”)”
[Link](query)
[Link]()
print(“Record Inserted”)
print(“Details of Event organized in year 2024”)
query=”select * from Event where eventdate like ‘2024’ “
[Link](query)
data=[Link]()
print(“ID NAME DATE DESCRIPTION”)

VIJAYASHANTHI. A – PYTHON NOTES_01 6

 7

for rec in data:

print(rec[0],rec[1],rec[2],rec[3],sep=” “)
[Link]()

6)MySQL database named WarehouseDB has a product_inventory

table in MySQL which contains the following attributes:
->Item_code: Item code (Integer)
->Product_name: Name of product (String)
->Quantity: Quantity of product (Integer)
->Cost: Cost of product (Integer)
Consider the following details to establish Python-MySQL
connectivity:
->User name: admin_user
->Password: warehouse2024
->Host: localhost
Write a Python program to change the Quantity of the product to 91
whose Item_code is 208 in the product_Inventory table.

Ans:
import [Link]
connection=[Link] (host=’localhost’, user=’admin_user’,
password=’warehouse2024’, database=’warehouseDB’)
cursor=[Link]()
update_query=”UPDATE product_inventory SET Quantity=91 WHERE
Item_code=208”
[Link](update_query)
[Link]()

VIJAYASHANTHI. A – PYTHON NOTES_01 7

 8

print(“Data updated successfully.”)

[Link]()
[Link]()
7)Write the code to create a table Product in database Inventory with
following fields:
Fields Datatype
PID varchar(5)
PName char(30)
Price float
Rank varchar(2)

Ans:
import [Link]
mycon=[Link](host=’localhost’, user=’system’,
passwd=’hello’, database=’Inventory’)
cur=[Link]()
db=[Link](“CREATE TABLE Production(PID varchar(5) Primary key,
PName char(5), Price float, Rank varchar(2)”)
[Link]()
8)Consider the table product and client with structures as follows:
Product Client
ProductName ClientName
Manufacturer City
Price P_ID
Write Python codes for the following:
i)To display the details of those clients whose city is Delhi.

VIJAYASHANTHI. A – PYTHON NOTES_01 8

 9

ii)To display the details of products whose price is in range of 50 to

100 (Both values included)
iii)To display the Client Name, City from table Client.
iv)To display the price of all the items by the manufacturer ABC.
v)To display the product name and 4 items its price from the product
table.
Ans:
i)
import MySQLdb
db=[Link](‘localhost’,’Admin’,’Admin@pwd’,’cosmetics’)
cursor=[Link]()
sql=”””Select * from client where city=%s”””
city_query=(‘Delhi’)
try:
[Link](sql,city_query)
results=[Link]()
print(“Clent ID, Name, City, Product”)
for row in results:
CID=row[0]
CName=row[1]
CCity=row[2]
CProd=row[3]
print “%s%s%s%s”,%\(CID, CName, CCity, CProd)
except:
print (“Error: unable to fetch data”)
[Link]()

VIJAYASHANTHI. A – PYTHON NOTES_01 9

 10

[Link]()
ii)import [Link]
db=[Link](‘localhost’,’Admin’,’Admin@pwd’,’cosmetics’)
cursor=[Link]()
sql=”””select * from Product where Price BETWEEN %F to %F”””
value=(50,100)
try:
[Link](sql,value)
results=[Link]()
print(“ID Product Name Manufacturer Price”
for row in results:
PID=row[0]
PName=row[1]
PManu=row[2]
PPrice=row[3]
Print “%s%s%s%s”,%\(PID,PName,PManu,PPrice)
Except:
Print(“Error unable to fetch data”)
[Link]()
[Link]()
iii)import [Link]
db=[Link](‘localhost’,’Admin’,’Admin@pwd’,’cosmetics’)
cursor=[Link]()
sql=”””select ClientName, City From Client”””
try:

VIJAYASHANTHI. A – PYTHON NOTES_01 10

 11

[Link](sql)
results=[Link]()
print(”ClientName”,”City”)
for row in results:
print “%s%s”,%\(row[0],row[1])
print([Link], “Records Found”)
except:
print(“Error unable to Fetch data”)
[Link]()
[Link]()
iv)import [Link]
db=[Link](‘localhost’,’Admin’,’Admin@pwd’,’cosmetics’)
cursor=[Link](buffered=TRUE)
sql=”””select price From Product where Manfacturer=%s”””
query_value=(‘ABC’,)
try:
[Link](sql,query+value)
results=[Link]()
print(“Price of Items by %s”,%\(query_value)
for row in results:
print row[0]
print([Link],”Items listed”)
except:
print(“Error unable to Fetch data”)
[Link]()

VIJAYASHANTHI. A – PYTHON NOTES_01 11

 12

[Link]()
v)import [Link]
db=[Link](‘localhost’,’Admin’,’Admin@pwd’,’cosmetics’)
cursor=[Link]()
sql=”””Select ProductName, Price *4 From Product”””
try:
[Link](sql)
results=[Link]()
print(“ProductName”,”Price”)
for row in results:
print “%s%s”,%\(row[0],row[1])
print([Link]”Records found”)
except:
print(“Unable to fetch data”)
[Link]()
[Link]()
9)The code given below deletes the record from the table employee
which contains the following record structure:
E_code – String
E_name – String
Sal – Integer
City – String
Note the following to establish connectivity between Python and
MySQL:
Username is root
Password is root

VIJAYASHANTHI. A – PYTHON NOTES_01 12

 13

The table exists in a MySQL database named emp.

The details(E_code, E_name, Sal, City) are the attributes of the table.
Write the following statements to complete the code:
Statement 1 – to import the desired library.
Statement 2 – to execute the command that deletes the record with
E_code as ‘E101’.
Statement 3 – to delete the record permanently from the database.
import _________ as mysql #statement 1
def delete():
mydb=[Link](host=”localhost”, user=”root”,
passwd=”root”, database=”emp”)
mycursor=[Link]()
_________________ #statement 2
_________________ #statement 3
print(“Record deleted”)
Ans:
import [Link] as mysql #statement1
def delete():
mydb=[Link](host=”localhost”, user=”root”, passwd=”root”,
database=”emp”)
mycursor=[Link]()
[Link](“Delete FROM employee WHERE E_code=’E101’
“) #statement 2
[Link]() #statement 3
print(“Record deleted”)
Explanation:

VIJAYASHANTHI. A – PYTHON NOTES_01 13

 14

Statement 1: Import the MySQL connector library for Python.

Statement 2: Execite the SQL command to delete the record with ‘E_code’
as ‘E101’.
Statement 3: Commit the transaction to permanently delete the record from
the database.
10)The code given below reads the following records from the table
employee and displays only those records who have employees
coming fromcity ‘Delhi’:
E-code – String
E_name – Strin
Sal – Integer
City – String
Note the following to establish connectivity between Python and
MySQL:
Username is root
Password is root
The table exists in a MySQL database named emp.
The details (E_code, E_Name, Sal, City) are the attributes of the table.
Write the following statements to complete the code:
Statement 1 – import the desired library.
Statement 2 – to execute the query that fetches records of the
employees coming from city ‘Delhi’
Statement 3 – to read the complete data of the query (rows whose city
is Delhi) into the object named details, from the table employee in the
database.
import _______ as mysql #statement 1
def display():

VIJAYASHANTHI. A – PYTHON NOTES_01 14

 15

mydb=[Link](host=”localhost”, user=”roor”,
passwd=”root”, database=”emp”)
mycursor=[Link]()
____________________ #statement 2
details=_____________ #statement 3
for I in details:
print(i)
Ans.
import [Link] as mysql #statement 1
def display():
mydb=[Link](host=”localhost”, user=”roor”, passwd=”root”,
database=”emp”)
mycursor=[Link]()
[Link](“SELECT * FROM employee WHERE City=’Delhi’
“) #statement 2
details=[Link]() #statement 3
for i in details:
print(i)
#Call the function to execute
display()
Explanation of statements:
Statement 1: Import the ‘[Link]’ library to connect to MySQL.
Statement 2: Execute the SQL query to fetch records form the ‘employee’
table where the ‘City’ is “Delhi”.
Statement 3: Fetch all the results of the query and store them in the details’
variable.

VIJAYASHANTHI. A – PYTHON NOTES_01 15

 16

This code will connect to the MySQL database, run the query, and
print out all records where the city is ‘Delhi’.

VIJAYASHANTHI. A – PYTHON NOTES_01 16