PYnative

Python Programming

Home » Python Exercises » Python Database Programming Exercise

Python Database Programming Exercise

Updated on:  | 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.

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

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.