RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://pynative.com/python-database-programming-exercise-with-solution/ below:

Python Database Programming Exercise with Solution

Python Database Programming Exercise

Updated on: June 5, 2025 | 17 Comments

Here’s the corrected text:

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.

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: –

Here’s the corrected text:

Now it has 5 exercise questions, which simulate real-time queries, and each question contains a specific skill you need to learn. When you complete the exercise, you will become 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;Code language: Python (python)

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);Code language: Python (python)

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);Code language: Python (python)

PostgreSQL

Create Database

CREATE database python_db;Code language: Python (python)

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);Code language: Python (python)

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);Code language: Python (python)

SQLite

Create Database

CREATE database python_db;Code language: Python (python)

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);Code language: Python (python)

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);Code language: Python (python)

These tables should look like this.

hospital table Doctor table 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 PostgreSQL Solution 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)Code language: Python (python)

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:

Python PostgreSQL Solution 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)Code language: Python (python)

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 PostgreSQL Solution 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)
Code language: Python (python)

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 PostgreSQL Solution 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)Code language: Python (python)

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:

Python PostgreSQL Solution About Vishal

I’m Vishal Hule, the Founder of PYnative.com. As a Python developer, I enjoy assisting students, developers, and learners. Follow me on Twitter.

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4