Download database/insert_data.py from KLEB38/OC_P5: direct link, hf CLI and curl.
- Browser
- Download file 3.34 kB
-
https://huggingface.co/spaces/KLEB38/OC_P5/resolve/main/database/insert_data.py
- Command line
-
hf download hf://spaces/KLEB38/OC_P5/database/insert_data.py
-
curl -L -o insert_data.py https://huggingface.co/spaces/KLEB38/OC_P5/resolve/main/database/insert_data.py
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!") | |