This Database exercise Project will help Python developers to learn database programming skills quickly. In this exercise, we will perform database CRUD operations using Python.
Also Read:
Note:
This exercise covers the following three popular database servers. You can choose the database server you are familiar with to solve this exercise.
- MySQL
- PostgreSQL
- SQLite
You can use any driver (DB module) as per your wish, for example, there are more than 5 libraries are available to communicate with MySQL. In this exercise, I am using the following libraries.
- MySQL: mysql connector python
- PostgreSQL: psycopg2
- SQLite: sqlite3
This Python database programming exercise includes: –
Now it has 5 exercise questions, which simulate the real-time queries, and each question contains a specific skill you need to learn. When you complete the exercise, you get more familiar with database operations in Python.
Note:
The solution is provided at the end of each question. There are also tips and helpful learning resources for each question, which will help you solve the exercise.
Exercise/mini Project: Hospital Information System
In this exercise, We are implementing the Hospital Information System. In this exercise, I have created two tables, Hospital and Doctor. You need to create those two tables on your database server before starting the exercise.
SQL Queries for data preparation
Please find below the SQL queries to prepare the required data for our exercise.
MySQL
Create Database
CREATE database python_db;
Create Hospital Table
CREATE TABLE Hospital (
Hospital_Id INT UNSIGNED NOT NULL,
Hospital_Name TEXT NOT NULL,
Bed_Count INT,
PRIMARY KEY (Hospital_Id)
);
INSERT INTO Hospital (Hospital_Id, Hospital_Name, Bed_Count)
VALUES
('1', 'Mayo Clinic', 200),
('2', 'Cleveland Clinic', 400),
('3', 'Johns Hopkins', 1000),
('4', 'UCLA Medical Center', 1500);
Create Doctor Table
CREATE TABLE Doctor(
Doctor_Id INT UNSIGNED NOT NULL,
Doctor_Name TEXT NOT NULL,
Hospital_Id INT NOT NULL,
Joining_Date DATE NOT NULL,
Speciality TEXT NULL,
Salary INT NULL,
Experience INT NULL,
PRIMARY KEY (Doctor_Id)
);
INSERT INTO Doctor (Doctor_Id, Doctor_Name, Hospital_Id, Joining_Date, Speciality, Salary, Experience)
VALUES
('101', 'David', '1', '2005-2-10', 'Pediatric', '40000', NULL),
('102', 'Michael', '1', '2018-07-23', 'Oncologist', '20000', NULL),
('103', 'Susan', '2', '2016-05-19', 'Garnacologist', '25000', NULL),
('104', 'Robert', '2', '2017-12-28', 'Pediatric ', '28000', NULL),
('105', 'Linda', '3', '2004-06-04', 'Garnacologist', '42000', NULL),
('106', 'William', '3', '2012-09-11', 'Dermatologist', '30000', NULL),
('107', 'Richard', '4', '2014-08-21', 'Garnacologist', '32000', NULL),
('108', 'Karen', '4', '2011-10-17', 'Radiologist', '30000', NULL);
PostgreSQL
Create Database
CREATE database python_db;
Create Hospital Table
CREATE TABLE Hospital (
Hospital_Id serial NOT NULL PRIMARY KEY,
Hospital_Name VARCHAR (100) NOT NULL,
Bed_Count serial
);
INSERT INTO Hospital (Hospital_Id, Hospital_Name, Bed_Count)
VALUES
('1', 'Mayo Clinic', 200),
('2', 'Cleveland Clinic', 400),
('3', 'Johns Hopkins', 1000),
('4', 'UCLA Medical Center', 1500);
Create Doctor Table
CREATE TABLE Doctor (
Doctor_Id serial NOT NULL PRIMARY KEY,
Doctor_Name VARCHAR (100) NOT NULL,
Hospital_Id serial NOT NULL,
Joining_Date DATE NOT NULL,
Speciality VARCHAR (100) NOT NULL,
Salary INTEGER NOT NULL,
Experience SMALLINT
);
INSERT INTO Doctor (Doctor_Id, Doctor_Name, Hospital_Id, Joining_Date, Speciality, Salary, Experience)
VALUES
('101', 'David', '1', '2005-2-10', 'Pediatric', '40000', NULL),
('102', 'Michael', '1', '2018-07-23', 'Oncologist', '20000', NULL),
('103', 'Susan', '2', '2016-05-19', 'Garnacologist', '25000', NULL),
('104', 'Robert', '2', '2017-12-28', 'Pediatric ', '28000', NULL),
('105', 'Linda', '3', '2004-06-04', 'Garnacologist', '42000', NULL),
('106', 'William', '3', '2012-09-11', 'Dermatologist', '30000', NULL),
('107', 'Richard', '4', '2014-08-21', 'Garnacologist', '32000', NULL),
('108', 'Karen', '4', '2011-10-17', 'Radiologist', '30000', NULL);
SQLite
Create Database
CREATE database python_db;
Create Hospital Table
CREATE TABLE Hospital (
Hospital_Id INTEGER NOT NULL PRIMARY KEY,
Hospital_Name TEXT NOT NULL,
Bed_Count INTEGER NOT NULL
);
INSERT INTO Hospital (Hospital_Id, Hospital_Name, Bed_Count)
VALUES
('1', 'Mayo Clinic', 200),
('2', 'Cleveland Clinic', 400),
('3', 'Johns Hopkins', 1000),
('4', 'UCLA Medical Center', 1500);
Create Doctor Table
CREATE TABLE Doctor (
Doctor_Id INTEGER NOT NULL PRIMARY KEY,
Doctor_Name TEXT NOT NULL,
Hospital_Id INTEGER NOT NULL,
Joining_Date TEXT NOT NULL,
Speciality TEXT NOT NULL,
Salary INTEGER NOT NULL,
Experience INTEGER
);
INSERT INTO Doctor (Doctor_Id, Doctor_Name, Hospital_Id, Joining_Date, Speciality, Salary, Experience)
VALUES
('101', 'David', '1', '2005-2-10', 'Pediatric', '40000', NULL),
('102', 'Michael', '1', '2018-07-23', 'Oncologist', '20000', NULL),
('103', 'Susan', '2', '2016-05-19', 'Garnacologist', '25000', NULL),
('104', 'Robert', '2', '2017-12-28', 'Pediatric ', '28000', NULL),
('105', 'Linda', '3', '2004-06-04', 'Garnacologist', '42000', NULL),
('106', 'William', '3', '2012-09-11', 'Dermatologist', '30000', NULL),
('107', 'Richard', '4', '2014-08-21', 'Garnacologist', '32000', NULL),
('108', 'Karen', '4', '2011-10-17', 'Radiologist', '30000', NULL);
These tables should look like this.


SQL data model that we are using for this exercise

Now, let see the exercise questions.
Exercise 1: Connect to your database server and print its version
Reference article for help:
Note:
- Write SQL query to get the database server version.
- Connect to the database and use
cursor.execute()
to execute this query. - Next, use
cursor.fetchone()
to fetch the record.
Python MySQL Solution
import mysql.connector
def get_connection():
connection = mysql.connector.connect(host='localhost',
database='python_db',
user='pynative',
password='pynative@#29')
return connection
def close_connection(connection):
if connection:
connection.close()
def read_database_version():
try:
connection = get_connection()
cursor = connection.cursor()
cursor.execute("SELECT version();")
db_version = cursor.fetchone()
print("You are connected to MySQL version: ", db_version)
close_connection(connection)
except (Exception, mysql.connector.Error) as error:
print("Error while getting data", error)
print("Question 1: Print Database version")
read_database_version()
Python PostgreSQL Solution
import psycopg2
def get_connection():
connection = psycopg2.connect(user="postgres",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="python_db")
return connection
def close_connection(connection):
if connection:
connection.close()
def read_database_version():
try:
connection = get_connection()
cursor = connection.cursor()
cursor.execute("SELECT version();")
db_version = cursor.fetchone()
print("You are connected to PostgreSQL version: ", db_version)
close_connection(connection)
except (Exception, psycopg2.Error) as error:
print("Error while getting data", error)
print("Question 1: Print Database version")
read_database_version()
Python SQLite Solution
import sqlite3
def get_connection():
connection = sqlite3.connect('python_db.db')
return connection
def close_connection(connection):
if connection:
connection.close()
def read_database_version():
try:
connection = get_connection()
cursor = connection.cursor()
cursor.execute("select sqlite_version();")
db_version = cursor.fetchone()
print("You are connected to SQLite version: ", db_version)
close_connection(connection)
except (Exception, sqlite3.Error) as error:
print("Error while getting data", error)
print("Question 1: Print Database version")
read_database_version()
Question 2: Fetch Hospital and Doctor Information using hospital Id and doctor Id
Implement the functionality to read the details of a given doctor from the doctor table and Hospital from the hospital table. i.e., read records from Hospital and Doctor Table as per given hospital Id and Doctor Id.
Given:
def get_hospital_detail(hospital_id):
#Read data from Hospital table
def get_doctor_detail(doctor_id):
# Read data from Doctor table
get_hospital_details(2)
get_doctor_details(105)
Hint
- Connect to
python_db
and usecursor.execute()
to execute the parameterized query. - Next, use
cursor.fetchall()
to fetch the record. - Next, iterate record/resultSet to print all column values
Expected Output
Question 2: Read given hospital and doctor details Printing Hospital record Hospital Id: 2 Hospital Name: Cleveland Clinic Bed Count: 400 Printing Doctor record Doctor Id: 105 Doctor Name: Linda Hospital Id: 3 Joining Date: 2004-06-04 Specialty: Garnacologist Salary: 42000 Experience: None
Reference article for help:
- How to use database parameterized query in Python.
- Python Select data from MySQL Table
- Python select from PostgreSQL Table
- Python Select from SQLite Table
Python MySQL Solution
import mysql.connector
def get_connection():
connection = mysql.connector.connect(host='localhost',
database='python_db',
user='pynative',
password='pynative@#29')
return connection
def close_connection(connection):
if connection:
connection.close()
def get_hospital_detail(hospital_id):
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Hospital where Hospital_Id = %s"""
cursor.execute(select_query, (hospital_id,))
records = cursor.fetchall()
print("Printing Hospital record")
for row in records:
print("Hospital Id:", row[0], )
print("Hospital Name:", row[1])
print("Bed Count:", row[2])
close_connection(connection)
except (Exception, mysql.connector.Error) as error:
print("Error while getting data", error)
def get_doctor_detail(doctor_id):
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Doctor where Doctor_Id = %s"""
cursor.execute(select_query, (doctor_id,))
records = cursor.fetchall()
print("Printing Doctor record")
for row in records:
print("Doctor Id:", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6])
close_connection(connection)
except (Exception, mysql.connector.Error) as error:
print("Error while getting data", error)
print("Question 2: Read given hospital and doctor details \n")
get_hospital_detail(2)
print("\n")
get_doctor_detail(105)
Python PostgreSQL Solution
import psycopg2
def get_connection():
connection = psycopg2.connect(user="postgres",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="python_db")
return connection
def close_connection(connection):
if connection:
connection.close()
print("Postgres connection is closed")
def get_hospital_detail(hospital_id):
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Hospital where Hospital_Id = %s"""
cursor.execute(select_query, (hospital_id,))
records = cursor.fetchall()
print("Printing Hospital record")
for row in records:
print("Hospital Id:", row[0], )
print("Hospital Name:", row[1])
print("Bed Count:", row[2])
close_connection(connection)
except (Exception, psycopg2.Error) as error:
print("Error while getting data", error)
def get_doctor_detail(doctor_id):
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Doctor where Doctor_Id = %s"""
cursor.execute(select_query, (doctor_id,))
records = cursor.fetchall()
print("Printing Doctor record")
for row in records:
print("Doctor Id:", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6])
close_connection(connection)
except (Exception, psycopg2.Error) as error:
print("Error while getting data", error)
print("Question 2: Read given hospital and doctor details \n")
get_hospital_detail(2)
print("\n")
get_doctor_detail(105)
Python SQLite Solution
import sqlite3
def get_connection():
connection = sqlite3.connect('python_db.db')
return connection
def close_connection(connection):
if connection:
connection.close()
def get_hospital_detail(hospital_id):
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Hospital where Hospital_Id = ?"""
cursor.execute(select_query, (hospital_id,))
records = cursor.fetchall()
print("Printing Hospital record")
for row in records:
print("Hospital Id:", row[0], )
print("Hospital Name:", row[1])
print("Bed Count:", row[2])
close_connection(connection)
except (Exception, sqlite3.Error) as error:
print("Error while getting data", error)
def get_doctor_detail(doctor_id):
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Doctor where Doctor_Id = ?"""
cursor.execute(select_query, (doctor_id,))
records = cursor.fetchall()
print("Printing Doctor record")
for row in records:
print("Doctor Id:", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6])
close_connection(connection)
except (Exception, sqlite3.Error) as error:
print("Error while getting data", error)
print("Question 2: Read given hospital and doctor details \n")
get_hospital_detail(2)
print("\n")
get_doctor_detail(105)
Exercise 3: Get the list Of doctors as per the given specialty and salary
Note: Fetch all doctors whose salary higher than the input amount and specialty is the same as the input specialty.
Given:
def get_specialist_doctors_list(speciality, salary):
#Fetch doctor's details as per Speciality and Salary
get_specialist_doctors_list("Garnacologist", 30000)
Hint
- Define the parameterized select query to fetch data from the table as per the given specialty and salary.
- Next, use the cursor.execute() to execute the query.
- Next, get all records using
cursor.fetchall()
- Iterate those records and print each row.
Expected output
Printing doctors whose specialty is Garnacologist and salary greater than 30000 Doctor Id: 105 Doctor Name: Linda Hospital Id: 3 Joining Date: 2004-06-04 Specialty: Garnacologist Salary: 42000 Experience: None Doctor Id: 107 Doctor Name: Richard Hospital Id: 4 Joining Date: 2014-08-21 Specialty: Garnacologist Salary: 32000 Experience: None
Python MySQL Solution
import mysql.connector
def get_connection():
connection = mysql.connector.connect(host='localhost',
database='python_db',
user='pynative',
password='pynative@#29')
return connection
def close_connection(connection):
if connection:
connection.close()
def get_specialist_doctors_list(speciality, salary):
try:
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """select * from Doctor where Speciality=%s and Salary > %s"""
cursor.execute(sql_select_query, (speciality, salary))
records = cursor.fetchall()
print("Printing doctors whose specialty is", speciality, "and salary greater than", salary, "\n")
for row in records:
print("Doctor Id: ", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6], "\n")
close_connection(connection)
except (Exception, mysql.connector.Error) as error:
print("Error while getting data", error)
print("Question 3: Get Doctors as per given Speciality\n")
get_specialist_doctors_list("Garnacologist", 30000)
Python PostgreSQL Solution
import psycopg2
def get_connection():
connection = psycopg2.connect(user="postgres",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="python_db")
return connection
def close_connection(connection):
if connection:
connection.close()
print("Postgres connection is closed")
def get_specialist_doctors_list(speciality, salary):
try:
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """select * from Doctor where Speciality=%s and Salary > %s"""
cursor.execute(sql_select_query, (speciality, salary))
records = cursor.fetchall()
print("Printing doctors whose specialty is", speciality, "and salary greater than", salary, "\n")
for row in records:
print("Doctor Id: ", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6], "\n")
close_connection(connection)
except (Exception, psycopg2.Error) as error:
print("Error while getting data", error)
print("Question 3: Get Doctors as per given Speciality\n")
get_specialist_doctors_list("Garnacologist", 30000)
Python SQLite Solution
import sqlite3
def get_connection():
connection = sqlite3.connect('python_db.db')
return connection
def close_connection(connection):
if connection:
connection.close()
def get_specialist_doctors_list(speciality, salary):
try:
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """select * from Doctor where Speciality = ? and Salary > ?"""
cursor.execute(sql_select_query, (speciality, salary))
records = cursor.fetchall()
print("Printing doctors whose specialty is", speciality, "and salary greater than", salary, "\n")
for row in records:
print("Doctor Id: ", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6], "\n")
close_connection(connection)
except (Exception, sqlite3.Error) as error:
print("Error while getting data", error)
print("Question 3: Get Doctors as per given Speciality\n")
get_specialist_doctors_list("Garnacologist", 30000)
Exercise 4: Get a list of doctors from a given hospital
Note: Implement the functionality to fetch all the doctors as per the given Hospital Id. You must display the hospital name of a doctor.
Given:
def get_doctors(hospital_id):
#Fetch All doctors within given Hospital
get_doctors(2)
Hint:
- Define the parameterized select query to get the hospital name as per the given hospital id.
- Next, use the
cursor.execute()
to execute this query and store the hospital name in a variable. - Define the parameterized select query to fetch all doctors from the doctor table as per the given hospital id.
- Next, use the
cursor.execute()
to execute the query. - Next, get all records using
cursor.fetchall()
- Iterate those records and print each column. Also, display the hospital name we fetched in the first query in each doctor’s entry
Python MySQL Solution
import mysql.connector
def get_connection():
connection = mysql.connector.connect(host='localhost',
database='python_db',
user='pynative',
password='pynative@#29')
return connection
def close_connection(connection):
if connection:
connection.close()
def get_hospital_name(hospital_id):
# Fetch Hospital Name using Hospital id
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Hospital where Hospital_Id = %s"""
cursor.execute(select_query, (hospital_id,))
record = cursor.fetchone()
close_connection(connection)
return record[1]
except (Exception, mysql.connector.Error) as error:
print("Error while getting data", error)
def get_doctors(hospital_id):
# Fetch Hospital Name using Hospital id
try:
hospital_name = get_hospital_name(hospital_id)
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """select * from Doctor where Hospital_Id = %s"""
cursor.execute(sql_select_query, (hospital_id,))
records = cursor.fetchall()
print("Printing Doctors of ", hospital_name, "Hospital")
for row in records:
print("Doctor Id:", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Hospital Name:", hospital_name)
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6], "\n")
close_connection(connection)
except (Exception, mysql.connector.Error) as error:
print("Error while getting doctor's data", error)
print("Question 4: Get List of doctors of a given Hospital Id\n")
get_doctors(2)
Python PostgreSQL Solution
import psycopg2
def get_connection():
connection = psycopg2.connect(user="postgres",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="python_db")
return connection
def close_connection(connection):
if connection:
connection.close()
def get_hospital_name(hospital_id):
# Fetch Hospital Name using Hospital id
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Hospital where Hospital_Id = %s"""
cursor.execute(select_query, (hospital_id,))
record = cursor.fetchone()
close_connection(connection)
return record[1]
except (Exception, psycopg2.Error) as error:
print("Error while getting data from PostgreSQL", error)
def get_doctors(hospital_id):
# Fetch Hospital Name using Hospital id
try:
hospital_name = get_hospital_name(hospital_id)
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """select * from Doctor where Hospital_Id = %s"""
cursor.execute(sql_select_query, (hospital_id,))
records = cursor.fetchall()
print("Printing Doctors of ", hospital_name, "Hospital")
for row in records:
print("Doctor Id:", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Hospital Name:", hospital_name)
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6], "\n")
close_connection(connection)
except (Exception, psycopg2.Error) as error:
print("Error while getting doctor's data", error)
print("Question 4: Get List of doctors of a given Hospital Id\n")
get_doctors(2)
Python SQLite Solution
import sqlite3
def get_connection():
connection = sqlite3.connect('python_db.db')
return connection
def close_connection(connection):
if connection:
connection.close()
def get_hospital_name(hospital_id):
# Fetch Hospital Name using Hospital id
try:
connection = get_connection()
cursor = connection.cursor()
select_query = """select * from Hospital where Hospital_Id = ?"""
cursor.execute(select_query, (hospital_id,))
record = cursor.fetchone()
close_connection(connection)
return record[1]
except (Exception, sqlite3.Error) as error:
print("Error while getting data", error)
def get_doctors(hospital_id):
# Fetch Hospital Name using Hospital id
try:
hospital_name = get_hospital_name(hospital_id)
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """select * from Doctor where Hospital_Id = ?"""
cursor.execute(sql_select_query, (hospital_id,))
records = cursor.fetchall()
print("Printing Doctors of ", hospital_name, "Hospital")
for row in records:
print("Doctor Id:", row[0])
print("Doctor Name:", row[1])
print("Hospital Id:", row[2])
print("Hospital Name:", hospital_name)
print("Joining Date:", row[3])
print("Specialty:", row[4])
print("Salary:", row[5])
print("Experience:", row[6], "\n")
close_connection(connection)
except (Exception, sqlite3.Error) as error:
print("Error while getting doctor's data", error)
print("Question 4: Get List of doctors of a given Hospital Id\n")
get_doctors(2)
Operation 5: Update doctor experience in years
The value of the experience column for each doctor is null
. Implement the functionality to update the experience of a given doctor in years.
Given:
def def update_doctor_experience(doctor_id):
# Update Doctor Experience in Years
update_doctor_experience(101)
Hint
- The doctor table has the joining date for each doctor.
- Get a given doctor’s joining date.
- To get a difference in a year, we can calculate the difference between today’s date and joining-date in years.
- After calculating the difference in a year, you can execute the update table query to update the experience of a given doctor.
Expected Output
Before:
Printing Doctor record Doctor Id: 101 Doctor Name: David Hospital Id: 1 Joining Date: 2005-02-10 Specialty: Pediatric Salary: 40000 Experience: None
After:
Printing Doctor record Doctor Id: 101 Doctor Name: David Hospital Id: 1 Joining Date: 2005-02-10 Specialty: Pediatric Salary: 40000 Experience: 15
Reference article for help:
- How to use database parameterized query in Python.
- Python update MySQL Table
- Python PostgreSQL CRUD Operations
- Python Update SQLite Table
Python MySQL Solution
import mysql.connector
import datetime
from dateutil.relativedelta import relativedelta
def get_connection():
connection = mysql.connector.connect(host='localhost',
database='python_db',
user='pynative',
password='pynative@#29')
return connection
def close_connection(connection):
if connection:
connection.close()
def update_doctor_experience(doctor_id):
# Update Doctor Experience in Years
try:
# Get joining date
connection = get_connection()
cursor = connection.cursor()
select_query = """select Joining_Date from Doctor where Doctor_Id = %s"""
cursor.execute(select_query, (doctor_id,))
joining_date = cursor.fetchone()
# calculate Experience in years
joining_date_1 = datetime.datetime.strptime(''.join(map(str, joining_date)), '%Y-%m-%d')
today_date = datetime.datetime.now()
experience = relativedelta(today_date, joining_date_1).years
# Update doctor's Experience now
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """update Doctor set Experience = %s where Doctor_Id =%s"""
cursor.execute(sql_select_query, (experience, doctor_id))
connection.commit()
print("Doctor Id:", doctor_id, " Experience updated to ", experience, " years")
close_connection(connection)
except (Exception, mysql.connector.Error) as error:
print("Error while getting doctor's data", error)
print("Question 5: Calculate and Update experience of all doctors \n")
update_doctor_experience(101)
Python PostgreSQL Solution
import psycopg2
import datetime
from dateutil.relativedelta import relativedelta
def get_connection():
connection = psycopg2.connect(user="postgres",
password="pynative@#29",
host="127.0.0.1",
port="5432",
database="python_db")
return connection
def close_connection(connection):
if connection:
connection.close()
def update_doctor_experience(doctor_id):
# Update Doctor Experience in Years
try:
# Get joining date
connection = get_connection()
cursor = connection.cursor()
select_query = """select Joining_Date from Doctor where Doctor_Id = %s"""
cursor.execute(select_query, (doctor_id,))
joining_date = cursor.fetchone()
# calculate Experience in years
joining_date_1 = datetime.datetime.strptime(''.join(map(str, joining_date)), '%Y-%m-%d')
today_date = datetime.datetime.now()
experience = relativedelta(today_date, joining_date_1).years
# Update doctor's Experience now
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """update Doctor set Experience = %s where Doctor_Id =%s"""
cursor.execute(sql_select_query, (experience, doctor_id))
connection.commit()
print("Doctor Id:", doctor_id, " Experience updated to ", experience, " years")
close_connection(connection)
except (Exception, psycopg2.Error) as error:
print("Error while getting doctor's data", error)
print("Question 5: Calculate and Update experience of all doctors \n")
update_doctor_experience(101)
Python SQLite Solution
import sqlite3
import datetime
from dateutil.relativedelta import relativedelta
def get_connection():
connection = sqlite3.connect('python_db.db')
return connection
def close_connection(connection):
if connection:
connection.close()
def update_doctor_experience(doctor_id):
# Update Doctor Experience in Years
try:
# Get joining date
connection = get_connection()
cursor = connection.cursor()
select_query = """select Joining_Date from Doctor where Doctor_Id = ?"""
cursor.execute(select_query, (doctor_id,))
joining_date = cursor.fetchone()
# calculate Experience in years
joining_date_1 = datetime.datetime.strptime(''.join(map(str, joining_date)), '%Y-%m-%d')
today_date = datetime.datetime.now()
experience = relativedelta(today_date, joining_date_1).years
# Update doctor's Experience now
connection = get_connection()
cursor = connection.cursor()
sql_select_query = """update Doctor set Experience = ? where Doctor_Id = ?"""
cursor.execute(sql_select_query, (experience, doctor_id))
connection.commit()
print("Doctor Id:", doctor_id, " Experience updated to ", experience, " years")
close_connection(connection)
except (Exception, sqlite3.Error) as error:
print("Error while getting doctor's data", error)
print("Question 5: Calculate and Update experience of all doctors \n")
update_doctor_experience(101)