import sqlite3
from datetime import datetime
def create_database():
"""Create the database and patients table"""
conn = sqlite3.connect('hospital.db')
cursor = conn.cursor()
# Create 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,
date_of_birth TEXT NOT NULL, -- SQLite doesn't have DATE type
gender TEXT CHECK (gender IN ('Male', 'Female', 'Other')),
phone_number TEXT,
email TEXT,
address TEXT,
city TEXT,
state TEXT,
postal_code TEXT,
country TEXT,
blood_type TEXT CHECK (blood_type IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-')),
insurance_provider TEXT,
insurance_number TEXT,
registration_date TEXT DEFAULT CURRENT_TIMESTAMP,
last_visit_date TEXT,
allergies TEXT,
chronic_conditions TEXT,
emergency_contact_name TEXT,
emergency_contact_phone TEXT,
notes TEXT
)
""")
conn.commit()
conn.close()
print("Database and table created successfully!")
def insert_sample_data():
"""Insert sample patient data"""
conn = sqlite3.connect('hospital.db')
cursor = conn.cursor()
patients = [
('Ahmed', 'Mohamed', '1985-07-15', 'Male', '+20123456789', 'ahmed.m@example.com',
'123 Main St', 'Cairo', 'Cairo', '11511', 'A+'),
('Fatima', 'Ali', '1990-11-22', 'Female', '+20109876543', 'fatima.a@example.com',
'456 Park Ave', 'Alexandria', 'Alexandria', '21500', 'O-')
]
cursor.executemany("""
INSERT INTO patients (
first_name, last_name, date_of_birth, gender, phone_number, email,
address, city, state, postal_code, blood_type
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", patients)
conn.commit()
print(f"Inserted {len(patients)} sample patients")
# Show inserted patients
cursor.execute("SELECT patient_id, first_name, last_name FROM patients")
print("\nPatients in database:")
for row in cursor.fetchall():
print(f"ID: {row[0]}, Name: {row[1]} {row[2]}")
conn.close()
def get_all_patients():
"""Retrieve all patients"""
conn = sqlite3.connect('hospital.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM patients")
patients = cursor.fetchall()
print("\nAll Patients:")
print("ID | First Name | Last Name | DOB | Gender | Phone | Email")
print("-" * 80)
for patient in patients:
print(f"{patient[0]} | {patient[1]} | {patient[2]} | {patient[3]} | {patient[4]} | {patient[5]} | {patient[6]}")
conn.close()
return patients
def search_patient_by_name(name):
"""Search patients by name"""
conn = sqlite3.connect('hospital.db')
cursor = conn.cursor()
cursor.execute("""
SELECT * FROM patients
WHERE first_name LIKE ? OR last_name LIKE ?
""", (f'%{name}%', f'%{name}%'))
patients = cursor.fetchall()
print(f"\nPatients matching '{name}':")
if patients:
for patient in patients:
print(f"ID: {patient[0]}, Name: {patient[1]} {patient[2]}, Phone: {patient[5]}")
else:
print("No patients found")
conn.close()
return patients
def update_patient(patient_id, phone, email):
"""Update patient information"""
conn = sqlite3.connect('hospital.db')
cursor = conn.cursor()
cursor.execute("""
UPDATE patients
SET phone_number = ?, email = ?
WHERE patient_id = ?
""", (phone, email, patient_id))
conn.commit()
if cursor.rowcount > 0:
print(f"\nUpdated patient ID {patient_id}: New phone: {phone}, New email: {email}")
else:
print(f"\nNo patient found with ID {patient_id}")
conn.close()
def delete_patient(patient_id):
"""Delete a patient"""
conn = sqlite3.connect('hospital.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM patients WHERE patient_id = ?", (patient_id,))
patient = cursor.fetchone()
if patient:
cursor.execute("DELETE FROM patients WHERE patient_id = ?", (patient_id,))
conn.commit()
print(f"\nDeleted patient ID {patient_id}: {patient[1]} {patient[2]}")
else:
print(f"\nNo patient found with ID {patient_id}")
conn.close()
def create_index_and_view():
"""Create index and view"""
conn = sqlite3.connect('hospital.db')
cursor = conn.cursor()
# Create index
cursor.execute("CREATE INDEX IF NOT EXISTS idx_patient_name ON patients(first_name, last_name)")
print("\nCreated index for faster name searches")
# Create view
cursor.execute("""
CREATE VIEW IF NOT EXISTS patient_summary AS
SELECT
patient_id,
first_name || ' ' || last_name AS full_name,
CAST((julianday('now') - julianday(date_of_birth))/365 AS INTEGER) AS age,
gender,
phone_number,
last_visit_date
FROM patients
""")
print("Created patient summary view")
# Query the view
cursor.execute("SELECT * FROM patient_summary")
print("\nPatient Summary:")
print("ID | Full Name | Age | Gender | Phone")
print("-" * 60)
for row in cursor.fetchall():
print(f"{row[0]} | {row[1]} | {row[2]} | {row[3]} | {row[4]}")
conn.close()
def main():
# Create database and table
create_database()
# Insert sample data
insert_sample_data()
# Get all patients
get_all_patients()
# Search for a patient
search_patient_by_name("Ahmed")
# Update patient information (using the first patient's ID)
update_patient(1, '+20111222333', 'ahmed.mohamed@newemail.com')
# Show patients after update
search_patient_by_name("Ahmed")
# Delete a patient (using the second patient's ID)
delete_patient(2)
# Show all patients after deletion
get_all_patients()
# Create index and view
create_index_and_view()
if __name__ == "__main__":
main()
Telegram: DB developers channel