-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathgenerate_insert_sql.py
103 lines (95 loc) · 4.41 KB
/
generate_insert_sql.py
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
from faker import Faker
import random
# Faker 객체 생성
fake = Faker()
# SQL 파일 생성
with open("insert.sql", "w", encoding="utf-8") as f:
f.write("-- Fake Data Insert Script\n")
f.write("START TRANSACTION;\n\n")
# Author 데이터 삽입
f.write("-- Author 데이터 삽입\n")
authors = []
for _ in range(30): # 30명의 저자 생성
name = fake.name()
address = fake.address().replace("\n", ", ")
url = fake.url()
authors.append(name)
f.write(f"INSERT INTO Author (author_name, author_address, author_url) VALUES ('{name}', '{address}', '{url}');\n")
f.write("\n")
f.write("COMMIT;\n\n") # 트랜잭션 커밋
# Book 데이터 삽입
f.write("START TRANSACTION;\n\n")
f.write("-- Book 데이터 삽입\n")
books = []
for author in authors: # 각 저자에게 1권씩의 책 연결
isbn = fake.isbn13(separator="")
title = fake.sentence(nb_words=4)
year = random.randint(1990, 2024)
price = round(random.uniform(10000, 40000), -2) # 가격 범위: 10,000 ~ 40,000
category = random.choice(["Fiction", "Non-Fiction", "Fantasy", "Sci-Fi", "Romance", "Mystery"])
books.append({"isbn": isbn, "title": title})
f.write(f"INSERT INTO Book (book_ISBN, book_title, book_year, book_price, book_category, author_name) "
f"VALUES ('{isbn}', '{title}', {year}, {price}, '{category}', '{author}');\n")
f.write("\n")
f.write("COMMIT;\n\n") # 트랜잭션 커밋
# Warehouse 데이터 삽입
f.write("START TRANSACTION;\n\n")
f.write("-- Warehouse 데이터 삽입\n")
warehouses = []
for _ in range(5): # 5개의 창고 생성
code = fake.bothify(text="W###")
address = fake.address().replace("\n", ", ")
phone = fake.phone_number()
warehouses.append({"code": code, "book_count": {}}) # 각 창고의 책 개수를 기록
f.write(f"INSERT INTO Warehouse (warehouse_code, warehouse_address, warehouse_phone) "
f"VALUES ('{code}', '{address}', '{phone}');\n")
f.write("\n")
f.write("COMMIT;\n\n") # 트랜잭션 커밋
# Book_Warehouse 데이터 삽입 및 창고에 책 배분
f.write("START TRANSACTION;\n\n")
f.write("-- Book_Warehouse 데이터 삽입 및 책 배분\n")
for book in books:
total_copies = 5 # 각 책은 총 5권
while total_copies > 0:
# 랜덤 창고 선택
warehouse = random.choice(warehouses)
# 창고에 배분할 책 수량 (최대 남은 책 수량)
number = random.randint(1, min(2, total_copies)) # 한 번에 1~2권만 배치
total_copies -= number
# 창고에 책 보관 데이터 업데이트
warehouse_code = warehouse["code"]
if book["isbn"] in warehouse["book_count"]:
warehouse["book_count"][book["isbn"]] += number
else:
warehouse["book_count"][book["isbn"]] = number
f.write(f"INSERT INTO Book_Warehouse (book_ISBN, warehouse_code, number) "
f"VALUES ('{book['isbn']}', '{warehouse_code}', {number});\n")
f.write("\n")
f.write("COMMIT;\n\n") # 트랜잭션 커밋
# Customer 데이터 삽입
f.write("START TRANSACTION;\n\n")
f.write("-- Customer 데이터 삽입\n")
customers = []
for _ in range(20): # 20명의 고객 생성
email = fake.email()
name = fake.name()
address = fake.address().replace("\n", ", ")
phone = fake.phone_number()
customers.append(email)
f.write(f"INSERT INTO Customer (customer_email, customer_name, customer_address, customer_phone) "
f"VALUES ('{email}', '{name}', '{address}', '{phone}');\n")
f.write("\n")
f.write("COMMIT;\n\n") # 트랜잭션 커밋
# Shopping_basket 데이터 삽입
f.write("START TRANSACTION;\n\n")
f.write("-- Shopping_basket 데이터 삽입\n")
baskets = []
for _ in range(20): # 20개의 장바구니 생성
basket_id = fake.uuid4()[:8] # 짧은 UUID 사용
order_date = fake.date_this_year()
customer = random.choice(customers)
baskets.append(basket_id)
f.write(f"INSERT INTO Shopping_basket (basket_id, order_date, customer_email) "
f"VALUES ('{basket_id}', '{order_date}', '{customer}');\n")
f.write("\n")
f.write("COMMIT;\n\n") # 트랜잭션 커밋