blueeye-3184
Initial Commit: Clean history without large files
7a6b92c
Raw History Blame Contribute Delete
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()