Spaces:
Sleeping
Sleeping
File size: 2,951 Bytes
66dc4a2 | 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 | """Скрипт для загрузки 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Готово!")
|