Spaces:
Sleeping
Sleeping
| """Скрипт для загрузки xlsx-файлов из data/new_data в DuckDB напрямую (без Streamlit).""" | |
| import os | |
| import duckdb | |
| import pandas as pd | |
| DB_PATH = "data/orders.duckdb" | |
| DATA_DIR = "data/new_data" | |
| SCHEMA = """ | |
| CREATE TABLE IF NOT EXISTS orders ( | |
| date DATE NOT NULL, | |
| hour SMALLINT NOT NULL, | |
| tt_id VARCHAR NOT NULL, | |
| region VARCHAR, | |
| service_id INTEGER NOT NULL DEFAULT -1, | |
| cnt_parcel INTEGER NOT NULL DEFAULT 0, | |
| cnt_courier INTEGER NOT NULL DEFAULT 0 | |
| ); | |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_order ON orders (date, hour, tt_id, service_id); | |
| """ | |
| def load_xlsx(path: str) -> pd.DataFrame: | |
| df = pd.read_excel(path) | |
| df = df.rename(columns={ | |
| "shopNo": "tt_id", | |
| "id_service": "service_id", | |
| "region_tt": "region", | |
| }) | |
| df["tt_id"] = df["tt_id"].astype(str) | |
| df["date"] = pd.to_datetime(df["date"]).dt.date | |
| df["hour"] = df["hour"].astype(int) | |
| df["service_id"] = pd.to_numeric(df["service_id"], errors="coerce").fillna(-1).astype(int) | |
| df["cnt_parcel"] = pd.to_numeric(df["cnt_parcel"], errors="coerce").fillna(0).astype(int) | |
| df["cnt_courier"] = pd.to_numeric(df["cnt_courier"], errors="coerce").fillna(0).astype(int) | |
| df["region"] = df["region"].astype(str).str.strip() | |
| return df[["date", "hour", "tt_id", "region", "service_id", "cnt_parcel", "cnt_courier"]] | |
| os.makedirs("data", exist_ok=True) | |
| con = duckdb.connect(DB_PATH) | |
| for stmt in SCHEMA.strip().split(";"): | |
| s = stmt.strip() | |
| if s: | |
| con.execute(s) | |
| files = sorted([f for f in os.listdir(DATA_DIR) if f.endswith(".xlsx")]) | |
| print(f"Найдено файлов: {len(files)}") | |
| total_inserted = 0 | |
| for fname in files: | |
| path = os.path.join(DATA_DIR, fname) | |
| print(f"\nЗагружаю: {fname}...", flush=True) | |
| df = load_xlsx(path) | |
| before = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0] | |
| con.register("_tmp", df) | |
| con.execute(""" | |
| INSERT OR IGNORE INTO orders | |
| (date, hour, tt_id, region, service_id, cnt_parcel, cnt_courier) | |
| SELECT date, hour, tt_id, region, service_id, cnt_parcel, cnt_courier | |
| FROM _tmp | |
| """) | |
| con.unregister("_tmp") | |
| after = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0] | |
| inserted = after - before | |
| total_inserted += inserted | |
| print(f" Строк в файле: {len(df):,} | Вставлено: {inserted:,}") | |
| print(f"\n{'='*40}") | |
| print(f"Итого вставлено: {total_inserted:,}") | |
| print(f"Всего в БД: {con.execute('SELECT COUNT(*) FROM orders').fetchone()[0]:,}") | |
| print("\nСтатистика по ТТ:") | |
| stats = con.execute(""" | |
| SELECT region, COUNT(DISTINCT tt_id) as tts, SUM(cnt_parcel) as orders, MIN(date), MAX(date) | |
| FROM orders GROUP BY region ORDER BY region | |
| """).df() | |
| print(stats.to_string()) | |
| con.close() | |
| print("\nГотово!") | |