populate_db.py

import psycopg2
import pandas as pd
from datetime import datetime
import random

# --- Database Connection Details ---
DB_NAME = "org_db"
DB_USER = "root"
DB_PASSWORD = "root"
DB_HOST = "localhost"
DB_PORT = "5432"

# --- Sample Data (Expand this as needed) ---
# This data is the same as the SQL inserts, but in a Python list of dictionaries
sample_healthcare_content = [
    {'id': 1, 'title': 'Understanding Diabetes Management', 'description': 'Comprehensive guide to managing type 2 diabetes, including diet, exercise, medication adherence, and blood sugar monitoring.', 'category': 'Endocrinology', 'keywords': 'diabetes, blood sugar, insulin, diet, exercise, glucose'},
    {'id': 2, 'title': 'Heart Health: Prevention and Care', 'description': 'Learn about cardiovascular disease prevention, healthy diet, regular exercise, and lifestyle choices for a strong heart. Focus on cholesterol and blood pressure.', 'category': 'Cardiology', 'keywords': 'heart, cardiovascular, cholesterol, blood pressure, fitness, prevention'},
    {'id': 3, 'title': 'Mental Wellness: Coping with Stress', 'description': 'Strategies and techniques for managing daily stress, anxiety, and improving overall mental well-being through mindfulness, therapy, and self-care.', 'category': 'Psychiatry', 'keywords': 'stress, anxiety, depression, mindfulness, therapy, mental health'},
    {'id': 4, 'title': 'Nutrition for a Healthy Life', 'description': 'Essential nutrients, balanced diet plans, and healthy eating habits for all ages. Covers vitamins, minerals, and metabolism.', 'category': 'Nutrition', 'keywords': 'diet, vitamins, minerals, healthy eating, metabolism, food'},
    {'id': 5, 'title': 'Exercise Benefits for Seniors', 'description': 'Benefits of regular physical activity for older adults, including strength, balance, and flexibility exercises to maintain mobility and independence.', 'category': 'Geriatrics', 'keywords': 'seniors, exercise, mobility, strength, flexibility, aging'},
    {'id': 6, 'title': 'Childhood Vaccinations Explained', 'description': 'Information on common childhood vaccines, their importance in preventing diseases, and safety guidelines for parents.', 'category': 'Pediatrics', 'keywords': 'vaccines, children, immunity, immunization, preventable diseases, child health'},
    {'id': 7, 'title': 'Understanding Blood Pressure Readings', 'description': 'What is blood pressure, how to measure it accurately, and ways to maintain healthy levels to prevent hypertension or hypotension.', 'category': 'Cardiology', 'keywords': 'blood pressure, hypertension, hypotension, monitoring, healthy lifestyle, measurement'},
    {'id': 8, 'title': 'Sleep Hygiene Tips for Better Rest', 'description': 'Practical advice for improving sleep quality and overcoming insomnia, including consistent routines, bedroom environment, and avoiding stimulants.', 'category': 'Sleep Medicine', 'keywords': 'sleep, insomnia, rest, circadian rhythm, relaxation, fatigue'},
    {'id': 9, 'title': 'Managing Chronic Pain Effectively', 'description': 'Approaches to living with and alleviating chronic pain, including physical therapies, medication management, and alternative treatments for long-term relief.', 'category': 'Pain Management', 'keywords': 'pain, chronic, relief, therapy, medication, rehabilitation'},
    {'id': 10, 'title': 'Telehealth Services: A Comprehensive Guide', 'description': 'A complete guide to using telehealth services for remote medical consultations, virtual care, and accessing specialists from home.', 'category': 'Digital Health', 'keywords': 'telehealth, online doctor, virtual care, remote consultation, digital medicine'},
    {'id': 11, 'title': 'Skin Cancer Awareness and Prevention', 'description': 'Understanding different types of skin cancer, risk factors, early detection, and effective sun protection strategies.', 'category': 'Dermatology', 'keywords': 'skin cancer, melanoma, UV, sun protection, dermatology, prevention'},
    {'id': 12, 'title': 'Understanding Allergies and Anaphylaxis', 'description': 'Common allergens, symptoms of allergic reactions, management strategies, and what to do in case of anaphylaxis.', 'category': 'Allergy & Immunology', 'keywords': 'allergies, anaphylaxis, asthma, immune system, histamine, reactions'},
    {'id': 13, 'title': 'Diabetes-Friendly Recipes', 'description': 'A collection of easy-to-make and delicious recipes suitable for individuals managing diabetes, focusing on low-GI ingredients.', 'category': 'Nutrition', 'keywords': 'diabetes, recipes, diet, low-GI, healthy food, cooking'},
    {'id': 14, 'title': 'Yoga and Mindfulness for Back Pain', 'description': 'Gentle yoga poses and mindfulness exercises specifically designed to alleviate chronic back pain and improve posture.', 'category': 'Physical Therapy', 'keywords': 'yoga, mindfulness, back pain, posture, exercise, physical therapy'},
    {'id': 15, 'title': 'Understanding Autoimmune Diseases', 'description': 'An overview of common autoimmune conditions, their symptoms, diagnosis, and current treatment approaches.', 'category': 'Immunology', 'keywords': 'autoimmune, inflammation, immune system, chronic illness, diagnosis'},
    {'id': 16, 'title': 'First Aid Essentials for Home', 'description': 'A guide to essential first aid supplies and basic techniques for handling common injuries and emergencies at home.', 'category': 'Emergency Medicine', 'keywords': 'first aid, injuries, emergencies, home safety, wound care'},
    {'id': 17, 'title': 'The Benefits of Probiotics for Gut Health', 'description': 'How probiotics improve digestive health, boost immunity, and maintain a healthy gut microbiome.', 'category': 'Gastroenterology', 'keywords': 'probiotics, gut health, digestion, microbiome, immunity, fermented food'},
    {'id': 18, 'title': 'Preventing Flu and Colds Seasonally', 'description': 'Tips for staying healthy during cold and flu season, including hygiene, vaccination, and immune-boosting foods.', 'category': 'Infectious Diseases', 'keywords': 'flu, cold, prevention, vaccination, hygiene, immune system'},
    {'id': 19, 'title': 'Understanding Cholesterol: Good vs. Bad', 'description': 'A detailed explanation of HDL and LDL cholesterol, their impact on heart health, and strategies for managing levels.', 'category': 'Cardiology', 'keywords': 'cholesterol, HDL, LDL, heart disease, lipids, healthy diet'},
    {'id': 20, 'title': 'Navigating the Healthcare System', 'description': 'A practical guide for patients to understand insurance, find doctors, manage appointments, and advocate for their care.', 'category': 'Healthcare Administration', 'keywords': 'healthcare system, insurance, patient advocacy, appointments, medical care'}
]


def insert_content_data():
    conn = None
    try:
        # Connect to the PostgreSQL database
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=DB_PORT
        )
        cur = conn.cursor()

        # Optional: Clear existing data before inserting new data
        # This is useful for repeated testing
        # cur.execute("DELETE FROM healthcare_content;")
        # conn.commit()
        # print("Cleared existing data from healthcare_content table.")

        # SQL INSERT statement
        insert_sql = """
        INSERT INTO org.healthcare_content (id, title, description, category, keywords, created_at, updated_at)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (id) DO NOTHING; -- Prevents errors if ID already exists
        """

        for item in sample_healthcare_content:
            current_time = datetime.now()
            data = (
                item['id'],
                item['title'],
                item['description'],
                item.get('category'), # Use .get() in case category is missing
                item.get('keywords'),
                current_time,
                current_time
            )
            cur.execute(insert_sql, data)

        conn.commit()
        print(f"Successfully inserted/updated {len(sample_healthcare_content)} records into healthcare_content.")

    except Exception as e:
        print(f"Error inserting data: {e}")
        if conn:
            conn.rollback() # Rollback on error
    finally:
        if conn:
            cur.close()
            conn.close()
            print("Database connection closed.")

if __name__ == "__main__":
    insert_content_data()