How to build a simple database for a patient in the hospital

To build a simple database for a patient in a hospital, you can use Python’s built-in sqlite3 module to create and manage a SQLite database.

Here, I’ll provide a basic example of how to create such a database with tables for patients, appointments, and doctors.

First, let’s import the necessary module:

import sqlite3

Next, create a connection to a new SQLite database named hospital.db:

conn = sqlite3.connect(‘hospital.db’)
cursor = conn.cursor()

Now, let’s create the tables for patients, appointments, and doctors:

# Patients table
cursor.execute(”’CREATE TABLE IF NOT EXISTS patients
(patient_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
phone_number TEXT NOT NULL);”’)

# Appointments table
cursor.execute(”’CREATE TABLE IF NOT EXISTS appointments
(appointment_id INTEGER PRIMARY KEY AUTOINCREMENT,
patient_id INTEGER NOT NULL,
appointment_date TEXT NOT NULL,
FOREIGN KEY(patient_id) REFERENCES patients(patient_id));”’)

# Doctors table
cursor.execute(”’CREATE TABLE IF NOT EXISTS doctors
(doctor_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
department TEXT NOT NULL);”’)

# Save the changes
conn.commit()

Now, let’s add some sample

# Add patients
cursor.execute(“INSERT INTO patients (first_name, last_name, phone_number) VALUES (?, ?, ?)”,
(‘John’, ‘Doe’, ‘555-123-4567’))
cursor.execute(“INSERT INTO patients (first_name, last_name, phone_number) VALUES (?, ?, ?)”,
(‘Jane’, ‘Doe’, ‘555-987-6543’))

# Add appointments
cursor.execute(“INSERT INTO appointments (patient_id, appointment_date) VALUES (?, ?)”,
(1, ‘2023-03-20’))

# Add doctors
cursor.execute(“INSERT INTO doctors (first_name, last_name, department) VALUES (?, ?, ?)”,
(‘John’, ‘Doe’, ‘Surgery’))
cursor.execute(“INSERT INTO doctors (first_name, last_name, department) VALUES (?, ?, ?)”,
(‘Jane’, ‘Doe’, ‘Pediatrics’))

# Save the changes
conn.commit()

Finally, let’s retrieve the required information:

# Question 2: First, last name, and phone number of patients who have an upcoming appointment
cursor.execute(”’SELECT patients.first_name, patients.last_name, patients.phone_number
FROM patients
JOIN appointments ON patients.patient_id = appointments.patient_id
WHERE appointments.appointment_date >= date(‘now’);”’)
result = cursor.fetchall()
for patient in result:
print(patient)

# Question 3: Hospital information for locations in either Texas or New York
# (Assuming we have a “locations” table with a “state” column)
cursor.execute(”’SELECT * FROM locations
WHERE state IN (‘Texas’, ‘New York’);”’)
result = cursor.fetchall()
for location in result:
print(location)

# Question 4: First and last names of doctors assigned to an Accident Department
# (Assuming we have an “assigned_to” table with “doctor_id” and “department” columns)
cursor.execute(”’SELECT doctors.first_name, doctors.last_name
FROM doctors
JOIN assigned_to ON doctors.doctor_id = assigned_to.doctor_id
WHERE assigned_to.department = ‘Accident’;”’)
result = cursor.fetchall()
for doctor in result:

About Author

SURF

See author's posts