Spaces:
Configuration error
Configuration error
Download model-server/01.data/csv_to_sql.py from ajjk1/apple-sales-api: direct link, hf CLI and curl.
- Browser
- Download file 4.46 kB
-
https://huggingface.co/spaces/ajjk1/apple-sales-api/resolve/main/model-server/01.data/csv_to_sql.py
- Command line
-
hf download hf://spaces/ajjk1/apple-sales-api/model-server/01.data/csv_to_sql.py
-
curl -L -o csv_to_sql.py https://huggingface.co/spaces/ajjk1/apple-sales-api/resolve/main/model-server/01.data/csv_to_sql.py
4.46 kB
| """ | |
| data_02_inventory_final.csv โ PostgreSQL SQL ํ์ผ๋ก ๋ณํ ํ 01.data ํด๋์ ์ ์ฅ. | |
| - ์ถ๋ ฅ: Apple_Retail_Sales_Dataset_Modified_01.sql ~ _10.sql (10๊ฐ ๋ถํ ) | |
| - ํ ์ด๋ธ: sales_data (14์ปฌ๋ผ, ๊ธฐ์กด ์คํค๋ง์ ๋์ผ) | |
| """ | |
| import csv | |
| from pathlib import Path | |
| DATA_DIR = Path(__file__).parent | |
| CSV_FILE = DATA_DIR / "data_02_inventory_final.csv" | |
| NUM_FILES = 10 | |
| OUTPUT_PREFIX = "Apple_Retail_Sales_Dataset_Modified" | |
| def escape_sql(value: str) -> str: | |
| """PostgreSQL์ฉ ์์๋ฐ์ดํ ์ด์ค์ผ์ดํ.""" | |
| if value is None or (isinstance(value, str) and value.strip() == ""): | |
| return "NULL" | |
| s = str(value).strip().replace("'", "''") | |
| return "'" + s + "'" | |
| def num_sql(value: str) -> str: | |
| """์ซ์ ์ปฌ๋ผ์ฉ: ์ ํจํ๋ฉด ๊ทธ๋๋ก, ์๋๋ฉด 0.""" | |
| if value is None: | |
| return "0" | |
| s = str(value).strip() | |
| if not s: | |
| return "0" | |
| try: | |
| float(s) | |
| return s | |
| except ValueError: | |
| return "0" | |
| def main(): | |
| if not CSV_FILE.exists(): | |
| print(f"์ค๋ฅ: {CSV_FILE} ํ์ผ์ด ์์ต๋๋ค.") | |
| return | |
| with open(CSV_FILE, "r", encoding="utf-8-sig", newline="") as f: | |
| reader = csv.DictReader(f) | |
| rows = list(reader) | |
| if not rows: | |
| print("CSV์ ํ์ด ์์ต๋๋ค.") | |
| return | |
| total = len(rows) | |
| chunk_size = (total + NUM_FILES - 1) // NUM_FILES | |
| create_table_sql = """-- PostgreSQL table for Apple retail sales data (from data_02_inventory_final.csv) | |
| DROP TABLE IF EXISTS sales_data CASCADE; | |
| CREATE TABLE sales_data ( | |
| sale_id VARCHAR(50), | |
| sale_date DATE, | |
| store_id VARCHAR(20), | |
| product_id VARCHAR(20), | |
| quantity INTEGER, | |
| product_name VARCHAR(255), | |
| category_id VARCHAR(20), | |
| launch_date DATE, | |
| price DECIMAL(12,2), | |
| category_name VARCHAR(100), | |
| store_name VARCHAR(255), | |
| city VARCHAR(100), | |
| country VARCHAR(100), | |
| total_sales DECIMAL(12,2) | |
| ); | |
| """ | |
| # CSV ์ปฌ๋ผ๋ช (๋์๋ฌธ์ ํผ์ฉ ๋์) | |
| def get(row: dict, *keys: str) -> str: | |
| for k in keys: | |
| if k in row: | |
| return row[k] or "" | |
| if k.lower() in row: | |
| return row[k.lower()] or "" | |
| return "" | |
| for i in range(NUM_FILES): | |
| start = i * chunk_size | |
| end = min(start + chunk_size, total) | |
| if start >= total: | |
| break | |
| chunk = rows[start:end] | |
| out_file = DATA_DIR / f"{OUTPUT_PREFIX}_{i+1:02d}.sql" | |
| with open(out_file, "w", encoding="utf-8") as f: | |
| if i == 0: | |
| f.write(create_table_sql) | |
| f.write(f"-- Part {i+1}/{NUM_FILES}: rows {start+1} to {end} (total: {len(chunk)} rows)\n") | |
| f.write("INSERT INTO sales_data (sale_id, sale_date, store_id, product_id, quantity, product_name, category_id, launch_date, price, category_name, store_name, city, country, total_sales) VALUES\n") | |
| values_list = [] | |
| for row in chunk: | |
| values = [ | |
| escape_sql(get(row, "sale_id")), | |
| escape_sql(get(row, "sale_date")), | |
| escape_sql(get(row, "store_id")), | |
| escape_sql(get(row, "product_id")), | |
| num_sql(get(row, "quantity")), | |
| escape_sql(get(row, "Product_Name", "product_name")), | |
| escape_sql(get(row, "category_id")), | |
| escape_sql(get(row, "Launch_Date", "launch_date")), | |
| num_sql(get(row, "price")), | |
| escape_sql(get(row, "category_name")), | |
| escape_sql(get(row, "Store_Name", "store_name")), | |
| escape_sql(get(row, "City", "city")), | |
| escape_sql(get(row, "Country", "country")), | |
| num_sql(get(row, "total_sales")), | |
| ] | |
| values_list.append("(" + ", ".join(values) + ")") | |
| batch_size = 500 | |
| for j in range(0, len(values_list), batch_size): | |
| batch = values_list[j : j + batch_size] | |
| f.write(",\n".join(batch)) | |
| if j + batch_size < len(values_list): | |
| f.write(",\n") | |
| else: | |
| f.write("\n") | |
| f.write(";\n") | |
| print(f"์์ฑ: {out_file.name} ({len(chunk)} rows)") | |
| print(f"\n์๋ฃ: {NUM_FILES}๊ฐ SQL ํ์ผ์ด {DATA_DIR} (01.data)์ ์ ์ฅ๋์์ต๋๋ค.") | |
| if __name__ == "__main__": | |
| main() | |