PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python Exercises » Python Database Programming Exercise

Python Database Programming Exercise

Updated on: March 9, 2021 | 15 Comments

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:

  • Python MySQL
  • Python SQLite
  • Python PostgreSQL

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.

hospital table
hospital table
Doctor table
Doctor table

SQL data model that we are using for this exercise

"<yoastmark

Now, let see the exercise questions.

Exercise 1: Connect to your database server and print its version

Reference article for help: 

  • Python MySQL Database Connection
  • Python PostgreSQL Connection
  • Python SQLite Connection

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 use cursor.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)

Filed Under: Python, Python Databases, Python Exercises

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases Python Exercises

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases Python Exercises
TweetF  sharein  shareP  Pin

 Python Exercises

  • Python Exercises Home
  • Basic Exercise for Beginners
  • Input and Output Exercise
  • Loop Exercise
  • Functions Exercise
  • String Exercise
  • Data Structure Exercise
  • List Exercise
  • Dictionary Exercise
  • Set Exercise
  • Tuple Exercise
  • Date and Time Exercise
  • OOP Exercise
  • Python JSON Exercise
  • Random Data Generation Exercise
  • NumPy Exercise
  • Pandas Exercise
  • Matplotlib Exercise
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com