File size: 3,342 Bytes
d2a2a12 ef8489a bc98bda ef8489a d2a2a12 ef8489a d2a2a12 ef8489a d2a2a12 ef8489a d2a2a12 bc98bda b909dbb ef8489a b909dbb ef8489a b909dbb ef8489a bc98bda d2a2a12 ef8489a d2a2a12 bc98bda ef8489a bc98bda d2a2a12 ef8489a d2a2a12 bc98bda ef8489a d2a2a12 ef8489a | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | 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!")
|