File size: 4,463 Bytes
7a6b92c | 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 136 137 | """
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()
|