forked from FPG-992/Databases
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpopulate.py
More file actions
180 lines (142 loc) · 5.2 KB
/
populate.py
File metadata and controls
180 lines (142 loc) · 5.2 KB
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
import mysql.connector
from mysql.connector import Error
import random
def create_connection(host_name, user_name, user_password, db_name):
connection = None
try:
connection = mysql.connector.connect(
host=host_name, user=user_name, password=user_password, database=db_name
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")
return connection
def execute_query(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
print("Query executed successfully")
except Error as e:
print(f"The error '{e}' occurred")
def fetch_query(connection, query):
cursor = connection.cursor(dictionary=True)
result = None
try:
cursor.execute(query)
result = cursor.fetchall()
return result
except Error as e:
print(f"The error '{e}' occurred")
return result
def insert_episodes(connection, year, num_episodes=10):
for i in range(num_episodes):
day = random.randint(1, 28)
month = i + 1
query = f"""
INSERT INTO episodes (day, month, year)
VALUES ({day}, {month}, {year});
"""
execute_query(connection, query)
def select_random_cuisines(connection, limit):
query = f"""
SELECT name FROM national_cuisines
ORDER BY RAND()
LIMIT {limit};
"""
return [cuisine["name"] for cuisine in fetch_query(connection, query)]
def select_random_chefs_by_cuisine(connection, cuisine, limit):
query = f"""
SELECT c.id, csinc.national_cuisine FROM chefs c
JOIN chef_specializes_in_national_cuisine csinc ON c.id = csinc.chef_id
WHERE csinc.national_cuisine = '{cuisine}'
ORDER BY RAND()
LIMIT {limit};
"""
return [
(chef["id"], chef["national_cuisine"])
for chef in fetch_query(connection, query)
]
def select_random_judge_chefs(connection, limit):
query = f"""
SELECT c.id FROM chefs c
ORDER BY RAND()
LIMIT {limit};
"""
return [chef["id"] for chef in fetch_query(connection, query)]
def select_random_recipe_by_cuisine(connection, cuisine):
query = f"""
SELECT id FROM recipes
WHERE national_cuisine = '{cuisine}'
ORDER BY RAND()
LIMIT 1;
"""
result = fetch_query(connection, query)
return result[0]["id"] if result else None
def insert_episode_participants(connection, episode_id, participant_cooks, judge_cooks):
for cook_id, cuisine in participant_cooks:
recipe_id = select_random_recipe_by_cuisine(connection, cuisine)
query = f"""
INSERT INTO episode_participants (episode_id, chef_id, recipe_id, role)
VALUES ({episode_id}, {cook_id}, {recipe_id}, 'participant');
"""
execute_query(connection, query)
for cook_id in judge_cooks:
query = f"""
INSERT INTO episode_participants (episode_id, chef_id, role)
VALUES ({episode_id}, {cook_id}, 'judge');
"""
execute_query(connection, query)
def insert_judge_scores(connection, episode_id):
# Get judges from episode_id
query = f"""
SELECT chef_id FROM episode_participants
WHERE episode_id = {episode_id} AND role = 'judge';
"""
judges = fetch_query(connection, query)
# Get participants from episode_id
query = f"""
SELECT chef_id FROM episode_participants
WHERE episode_id = {episode_id} AND role = 'participant';
"""
participants = fetch_query(connection, query)
# Fill the scores for each participant
for participant in participants:
for judge in judges:
score = random.randint(1, 5)
query = f"""
INSERT INTO judge_rates_chef (score, chef_id, judge_id, episode_id)
VALUES ({score}, {participant['chef_id']}, {judge['chef_id']}, {episode_id});
"""
execute_query(connection, query)
def main():
connection = create_connection("127.0.0.1", "root", "root", "final_dbp")
year = 2025
num_episodes = 10
num_cuisines = 10
num_judges = 3
# Insert episodes
insert_episodes(connection, year, num_episodes)
# Fetch episode IDs
episodes = fetch_query(connection, f"SELECT id FROM episodes WHERE year = {year};")
for episode in episodes:
episode_id = episode["id"]
# Select 10 distinct national cuisines
cuisines = select_random_cuisines(connection, num_cuisines)
participant_cooks = []
for cuisine in cuisines:
# Select one cook from each national cuisine
cooks = select_random_chefs_by_cuisine(connection, cuisine, 1)
if cooks:
participant_cooks.extend(cooks)
# Select 3 judge cooks
judge_cooks = select_random_judge_chefs(connection, num_judges)
while any(judge in participant_cooks for judge in judge_cooks):
judge_cooks = select_random_judge_chefs(connection, num_judges)
# Insert participants into episode_participants table
insert_episode_participants(
connection, episode_id, participant_cooks, judge_cooks
)
insert_judge_scores(connection, episode_id)
if __name__ == "__main__":
main()