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;
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.
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
Python PostgreSQL Solution
Python SQLite 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 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
Python PostgreSQL Solution
Python SQLite 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 MySQL Solution
Python PostgreSQL Solution
Python SQLite 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 MySQL Solution
Python PostgreSQL Solution
Python SQLite 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:
- How to use database parameterized query in Python.
- Python update MySQL Table
- Python PostgreSQL CRUD Operations
- Python Update SQLite Table