loading_csv_to_database.py

import pandas as pd
import mysql.connector
from dotenv import load_dotenv
import os

load_dotenv()

# Load CSV
df = pd.read_csv(r"C:\\Users\\HP\\datathon\\data_synthesis\\data\\healthcare_data.csv")

# Connect to MySQL
conn = mysql.connector.connect(
    host=os.getenv("MYSQL_HOST"),
    user=os.getenv("MYSQL_USER"),
    password=os.getenv("MYSQL_PASSWORD"),
    database=os.getenv("MYSQL_DB"),
    port=int(os.getenv("MYSQL_PORT", 3333))
)
cursor = conn.cursor()

# Insert CSV data into MySQL
for i, row in df.iterrows():
    sql = """INSERT INTO phc_inventory 
             (date, Total_Patients, OutPatients, InPatients, Medical_Staff, Total_Beds, Beds_Occupied, Beds_Available,
              Malaria_Drugs_In_Stock, Typhoid_Drugs_In_Stock, Cough_Drugs_In_Stock, Cold_Drugs_In_Stock, Paracetamol_Drugs_In_Stock)
             VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
    cursor.execute(sql, tuple(row))

conn.commit()
cursor.close()
conn.close()