OC_P5 / database /insert_data.py
KLEB38's picture
Claude Opus 5
Make insert_data.py runnable against an empty database
b909dbb
Raw History Blame Contribute Delete
3.34 kB
import os
from pathlib import Path
import pandas as pd
from create_db import Base
from dotenv import load_dotenv
from sqlalchemy import create_engine
# Column, Integer, String, Float, DateTime
from sqlalchemy.sql import text
# Connection string
load_dotenv()
DATABASE_URL = os.getenv("DATABASE_URL")
if not DATABASE_URL:
raise SystemExit("DATABASE_URL is not set. Put it in .env or export it.")
engine = create_engine(DATABASE_URL)
# Source CSVs. They live outside this repository (P4 deliverables), so the
# directory is configurable instead of hard-coded to one machine's layout.
DATA_DIR = Path(os.getenv("P5_DATA_DIR", r"C:\Users\Kevin\projects\OC_P4\Projet 4"))
REQUIRED = ["extrait_sirh.csv", "extrait_eval.csv", "extrait_sondage.csv"]
missing = [name for name in REQUIRED if not (DATA_DIR / name).is_file()]
if missing:
raise SystemExit(
f"Missing source file(s) in {DATA_DIR}: {', '.join(missing)}.\n"
"Set P5_DATA_DIR to the folder holding the three P4 extracts."
)
with engine.connect() as conn:
conn.execute(text("DROP VIEW IF EXISTS employees_full CASCADE"))
conn.commit()
# Load CSVs
df_sirh = pd.read_csv(DATA_DIR / "extrait_sirh.csv")
df_eval = pd.read_csv(DATA_DIR / "extrait_eval.csv")
df_eval["eval_number"] = pd.to_numeric(df_eval["eval_number"].str[2:], errors="raise")
df_sondage = pd.read_csv(DATA_DIR / "extrait_sondage.csv")
df_sondage = df_sondage.rename(columns={"code_sondage": "id_employee"})
df_eval = df_eval.rename(columns={"eval_number": "id_employee"})
with engine.connect() as conn:
# Supprimer les FK si elles existent déjà. ALTER TABLE IF EXISTS is what
# makes this script runnable against an empty database: the tables are only
# created further down by to_sql(), so on a fresh instance there is nothing
# to alter yet.
conn.execute(
text(
"ALTER TABLE IF EXISTS employees_sondage "
"DROP CONSTRAINT IF EXISTS fk_sondage_employee"
)
)
conn.execute(
text(
"ALTER TABLE IF EXISTS employees_eval "
"DROP CONSTRAINT IF EXISTS fk_eval_employee"
)
)
conn.commit()
# Insert into DB
df_sirh.to_sql("employees_sirh", engine, if_exists="replace", index=False)
df_eval.to_sql("employees_eval", engine, if_exists="replace", index=False)
df_sondage.to_sql("employees_sondage", engine, if_exists="replace", index=False)
Base.metadata.create_all(engine, checkfirst=True)
with engine.connect() as conn:
conn.execute(text("ALTER TABLE employees_sirh ADD PRIMARY KEY (id_employee)"))
conn.execute(
text(
"ALTER TABLE employees_sondage ADD CONSTRAINT fk_sondage_employee FOREIGN KEY (id_employee) REFERENCES employees_sirh(id_employee)"
)
)
conn.execute(
text(
"ALTER TABLE employees_eval ADD CONSTRAINT fk_eval_employee FOREIGN KEY (id_employee) REFERENCES employees_sirh(id_employee)"
)
)
conn.commit()
print("Data inserted successfully!")
with engine.connect() as conn:
conn.execute(
text("""
CREATE OR REPLACE VIEW employees_full AS
SELECT * FROM employees_sirh
INNER JOIN employees_sondage USING(id_employee)
INNER JOIN employees_eval USING(id_employee)
""")
)
conn.commit()
print("View created successfully!")