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()