Spaces:
Running
Running
File size: 4,704 Bytes
fe8a793 | 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 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | """
load_ims_p_csvs.py
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Loads all CSV files exported from ims_be_p.accdb into PostgreSQL.
Tables are prefixed with 'p_' to identify their source.
Usage:
python load_ims_p_csvs.py
Configuration: edit DB_* and CSV_DIR below if needed.
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
"""
import os
import sys
import re
import pandas as pd
from pathlib import Path
from sqlalchemy import create_engine, text
# ββ CONFIGURATION βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
DB_HOST = "localhost"
DB_PORT = 5433
DB_NAME = "sales_db"
DB_USER = os.popen("whoami").read().strip()
DB_PASS = ""
CSV_DIR = Path.home() / "Desktop" / "ims_p_csvs"
# ββ HELPERS βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
def clean_table_name(filename: str) -> str:
"""Convert CSV filename to a clean PostgreSQL table name with p_ prefix."""
name = Path(filename).stem
name = re.sub(r"[^a-zA-Z0-9]", "_", name).lower()
name = re.sub(r"_+", "_", name).strip("_")
return f"p_{name}"
def infer_dtype(series: pd.Series) -> str:
"""Infer a basic PostgreSQL type from a pandas Series."""
if pd.api.types.is_integer_dtype(series):
return "BIGINT"
elif pd.api.types.is_float_dtype(series):
return "DOUBLE PRECISION"
else:
return "TEXT"
# ββ MAIN βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
def main():
print("\n" + "β" * 60)
print(" IMS-P CSV Loader β PostgreSQL")
print("β" * 60)
# Connect
print(f"\n[1/3] Connecting to {DB_NAME} on port {DB_PORT}...")
engine = create_engine(
f"postgresql+psycopg2://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
)
try:
with engine.connect() as conn:
conn.execute(text("SELECT 1"))
print(" β Connected")
except Exception as e:
print(f" β Connection failed: {e}")
sys.exit(1)
# Find CSV files
print(f"\n[2/3] Scanning {CSV_DIR}...")
csv_files = sorted(CSV_DIR.glob("*.csv"))
if not csv_files:
print(f" β No CSV files found in {CSV_DIR}")
sys.exit(1)
print(f" Found {len(csv_files)} CSV files")
# Load each CSV
print(f"\n[3/3] Loading tables...")
success = 0
failed = 0
total_rows = 0
for csv_path in csv_files:
table_name = clean_table_name(csv_path.name)
try:
df = pd.read_csv(
csv_path,
encoding = "latin-1",
low_memory = False,
on_bad_lines = "skip"
)
if df.empty:
print(f" β {table_name:<45} 0 rows (empty, skipped)")
continue
# Clean column names
df.columns = [
re.sub(r"[^a-zA-Z0-9]", "_", c).lower().strip("_")
for c in df.columns
]
df.columns = [re.sub(r"_+", "_", c) for c in df.columns]
# Write to PostgreSQL
df.to_sql(
table_name,
engine,
if_exists = "replace",
index = False,
method = "multi",
chunksize = 500
)
total_rows += len(df)
success += 1
print(f" β {table_name:<45} {len(df):>6,} rows β")
except Exception as e:
failed += 1
print(f" β {table_name:<45} FAILED: {e}")
# Summary
print(f"\n{'β' * 60}")
print(f" LOAD COMPLETE")
print(f"{'β' * 60}")
print(f" Tables loaded: {success}")
print(f" Tables failed: {failed}")
print(f" Total rows: {total_rows:,}")
print(f"\n All p_* tables are now in sales_db.")
print(f" Run the Phase 2 query in pgAdmin next.\n")
if __name__ == "__main__":
main()
|