CE PYTHON PROGRAMMING AB12

PRACTICAL-9: Python Database Connectivity

1. Write a python code to establish connection with MySQL and create database demo_data.
Also display list of all the availabledatabase.
Input:
import pymysql
try:
con = pymysql.connect(host="localhost",user="root",passwd="") query = "show
databases" print("available databases are") cursor = con.cursor() cursor.execute(query)
print(type(cursor)) print(cursor)
for i in cursor: print(i)

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally:
if con:
con.close()

import pymysql try: con =

pymysql.connect(host="localhost",user="root",passwd="") query = "create
database demo" cursor = con.cursor() cursor.execute(query)

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally: if cursor:
cursor.close() if con:
con.close()

Output:

2. Write a python code to create below specified two tables ‘hospital_details’ and
‘doctor_details’ and insert values as mentionedbelow.
Input:
import pymysql try: con =
pymysql.connect(host="localhost",user="root",passwd="",database="demo") query =

DIKSHANT | 20012011037 1
 CE PYTHON PROGRAMMING AB12

"create table doctor_details(doctor_idint,doctor_name

varchar(20),hospital_idint,speciality varchar(30),salary int,experinve int)"
cursor = con.cursor() cursor.execute(query) con.commit()

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally: if cursor:
cursor.close() if con:
con.close()

import pymysql try: con =

pymysql.connect(host="localhost",user="root",passwd="",database="demo") query =
"insert into doctor_details values(%s,%s,%s,%s,%s,%s)" cursor = con.cursor() record =
[(101,'karan',1,'Pediatric',40000,0),(102,'naresh',1,'Onchologist',80000,5),(103,'hardil',
2,'surgen',60000,2),(104,'vishal',2,'Homeopathy',50000,1),(105,'jay',3,'Aayurvedic',40
000,0),(106,'deep',3,'Physeotherapist',70000,4),(107,'diveys',4,'Pediatric
',55000,3),(108,'arjun',4,'scin ',55000,3)]
cursor.executemany(query,record)
con.commit()

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally: if cursor:
cursor.close() if con:
con.close()

import pymysql try: con =

pymysql.connect(host="localhost",user="root",passwd="",database="demo")
query = "create table hospital_details(hospital_idint,hodpital_name
varchar(20),bedcount int)" cursor = con.cursor()
cursor.execute(query) con.commit()

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally: if cursor:
cursor.close() if con:
con.close()

import pymysql try: con

=pymysql.connect(host="localhost",user="root",passwd="",database="demo") query =
"insert into hospital_details values(%s,%s,%s)" cursor
= con.cursor() record
=

DIKSHANT | 20012011037 2
 CE PYTHON PROGRAMMING AB12

[(1,'janta',200),(2,'zydus',500),(3,'sal',1000),(4,'stirling',1500)]
cursor.executemany(query,record) con.commit()

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally: if cursor:
cursor.close() if con:
con.close()

Output:

3. Write a python code to retrieve all the details of doctors.

Input: import pymysql try:
con = pymysql.connect(host="localhost",user="root",passwd="",database="demo") query =
"select * from
doctor_details" cursor =
con.cursor()
cursor.execute(query)
x=cursor.fetchall() for i inx:
print(i) con.commit()

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally: if cursor:
cursor.close() if con:
con.close()

Output:

DIKSHANT | 20012011037 3
 CE PYTHON PROGRAMMING AB12

4. Write a python code to retrieve all the doctors who are in Janta hospital.
Input:
import pymysql try: con =
pymysql.connect(host="localhost",user="root",passwd="",database="demo") query =
"select * from doctor_detailsd,hospital_details h where
h.hospital_id=d.hospital_id and h.hodpital_name='janta'" cursor =
con.cursor() cursor.execute(query) x=cursor.fetchall() for i inx:
print(i) con.commit()

except pymysql.DatabaseError as e:
print("Your error is : ",e)
finally:
if cursor:
cursor.close()
if con:
con.close()

Output:

5. Write a python code to update experience of doctors

Input:
import pymysql try: con =
pymysql.connect(host="localhost",user="root",passwd="",database="demo")

query = "update doctor_details set experinve=5 where doctor_id=101" cursor =

con.cursor() if con:

cursor.execute(query)
con.commit()

else:

DIKSHANT | 20012011037 4
 CE PYTHON PROGRAMMING AB12

print("notcommited")
except pymysql.DatabaseError as e:
print("Your error is :",e)
finally: if cursor:
cursor.close() if con:
con.close()

Output:

6. Write a python code to drop table‘hospital_details’.

Input:
import pymysql
try:
con = pymysql.connect(host="localhost",user="root",passwd="",database="demo")

query = "drop table hospital_details"

print("tabeledroped") cursor = con.cursor() if
con:
cursor.execute(query)
con.commit()

else:
print("notcommited")

except pymysql.DatabaseError as e:
print("Your error is :",e)
finally: if cursor:
cursor.close() if con:
con.close()

Output:

DIKSHANT | 20012011037 5
 CE PYTHON PROGRAMMING AB12

7. Write a python code to create in memory database and table using sqlite databaseengine.
Input:
import sqlite3 try: con =
sqlite3.connect("demo_data.db") cursor =
con.cursor() query = "" if con:
cursor.execute(query)
print("Database created")
con.commit() else:
print("Not Established")

except sqlite3.DatabaseError as e:
print("Exception: " , e) finally: if cursor:
cursor.close() if con:
con.close()

Output:

8. Establish connection with sqlite database engine and create above tables into database
demo. Show demonstration of executescript to execute multiple queries at a time.
Input:
import sqlite3 try: con =
sqlite3.connect("demo_data.db") cursor =
con.cursor() query ="" if con:
cursor.executescript( """ DROP TABLE IF EXISTSdoctors_details;
CREATE TABLE doctors_details(doctor_idint,doctor_name
varchar(20),hospital_idint,speciality varchar(30),salary int,experinveint);
INSERT INTO doctors_details VALUES(101,'karan',1,'Pediatric',40000,0); INSERT INTO
doctors_details VALUES(102,'naresh',1,'Onchologist',80000,5); INSERT INTO
doctors_details VALUES(103,'hardil',2,'surgen',60000,2); INSERT INTO doctors_details
VALUES(104,'vishal',2,'Homeopathy',50000,1); INSERT INTO doctors_details
VALUES(105,'jay',3,'Aayurvedic',40000,0); INSERT INTO doctors_details
VALUES(106,'deep',3,'Physeotherapist',70000,4); INSERT INTO doctors_details
VALUES(107,'diveys',4,'Pediatric ',55000,3);""") print("tabel created")
con.commit() else: print("Not Established")

except sqlite3.DatabaseError as e:
print("Exception: " , e) finally: if
cursor: cursor.close() if con:
DIKSHANT | 20012011037 6
 CE PYTHON PROGRAMMING AB12

con.close()

Output:

DIKSHANT | 20012011037 7