-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathrds_db.py
275 lines (232 loc) · 10.6 KB
/
rds_db.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
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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
import json
from decimal import Decimal
from time import sleep
import pymysql
# Connection to database (sensitive data removed)
conn = pymysql.connect(
host = "rds.amazonaws.com", # not real endpoint link
port = 1111, # not real port
user = "admin", # not real admin login
password = "my_password", # not real password
db = "food_items", # real database table name
)
# ~~~~~~~~~~[ Create: INSERT FOOD ITEM]~~~~~~~~~~~~~ #
def insert_food_item_into_food_diary(food_name, calories, protein, carbohydrate, fat, user_id):
cur = conn.cursor()
cur.execute("SET time_zone = 'US/Pacific'")
cur.execute("INSERT INTO FoodDiary (food_name, calories, protein, carbohydrate, fat, user_id, dt) VALUES (%s, %s, %s, %s, %s, %s, NOW())",
(food_name, calories, protein, carbohydrate, fat, user_id))
conn.commit()
# ~~~~~~~~~[ Quickly Add Food Items for Debugging Purposes ]~~~~~~~~~~~~~~~~~~ #
def debug_populate_food_diary():
cur = conn.cursor()
food_name = "Apple"
calories = 95
protein = 1
carbohydrate = 25
fat = 0
user_id = 1
cur.execute("SET time_zone = 'US/Pacific'")
cur.execute("INSERT INTO FoodDiary (food_name, calories, protein, carbohydrate, fat, user_id, dt) VALUES (%s, %s, %s, %s, %s, %s, NOW())",
(food_name, calories, protein, carbohydrate, fat, user_id))
conn.commit()
# ~~~~~~~~~~[ Update: CLEAR FOOD ITEMS FoodDiary DB]~~~~~~~~~~~~~ #
def delete_all_rows_from_food_diary():
cur = conn.cursor()
cur.execute("DELETE FROM FoodDiary")
conn.commit()
# ~~~~~~~~~~[ Read: GET FOOD ITEMS]~~~~~~~~~~~~~ #
def get_food_items_from_food_diary():
cur = conn.cursor()
cur.execute("SELECT * FROM FoodDiary")
food_items = cur.fetchall()
return food_items
# ~~~~~~~~~~[ Get all food items and stats for a particular user]~~~~~~~~~~~~~ #
def get_food_stats_for_user_from_food_diary(user_id):
cur = conn.cursor()
cur.execute("SELECT * FROM FoodDiary WHERE user_id = %s", user_id)
food_items = cur.fetchall()
return food_items
# ~~~~~~~~~~[ GET USER STATS]~~~~~~~~~~~~~ #
def get_current_user(name):
cur = conn.cursor()
cur.execute("SELECT * FROM Users WHERE name = %s", name)
current_user = cur.fetchall()
return current_user
def get_current_user_caloric_goal(name):
current_user = get_current_user(name)
user_caloric_goal = current_user[0][2]
user_caloric_goal = float(user_caloric_goal)
return user_caloric_goal
def get_current_user_protein_goal(name):
current_user = get_current_user(name)
user_protein_goal = current_user[0][3]
user_protein_goal = float(user_protein_goal)
return user_protein_goal
def get_current_user_carbohydrate_goal(name):
current_user = get_current_user(name)
user_carbohydrate_goal = current_user[0][4]
user_carbohydrate_goal = float(user_carbohydrate_goal)
return user_carbohydrate_goal
def get_current_user_fat_goal(name):
current_user = get_current_user(name)
user_fat_goal = current_user[0][5]
user_fat_goal = float(user_fat_goal)
return user_fat_goal
# Import JSON Formatted User List into MySQL DB
def add_userlist_from_txt_file():
with open("users_goals.txt") as fp:
while True:
curr_line = fp.readline()
if curr_line == "":
break
curr_line = json.loads(curr_line)
user_id = curr_line["user_id"]
name = curr_line["name"]
calories_goal = curr_line["calories_goal"]
protein_goal = curr_line["protein_goal"]
carbohydrate_goal = curr_line["carbohydrate_goal"]
fat_goal = curr_line["fat_goal"]
cur = conn.cursor()
cur.execute("INSERT IGNORE INTO Users (user_id, name, calories_goal, protein_goal, carbohydrate_goal, fat_goal) VALUES (%s, %s, %s, %s, %s, %s)",
(user_id, name, calories_goal, protein_goal, carbohydrate_goal, fat_goal))
conn.commit()
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~[ Getting User Nutrients Consumed from MySQL DB] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #
def get_user_id(name):
cur = conn.cursor()
cur.execute("SELECT user_id FROM Users WHERE name = %s", (name))
user_id = cur.fetchone()
user_id = int(user_id[0])
return user_id
# Remove the 'Decimal' wording
def remove_decimal_fix(var):
var_fixed = []
for row in var:
var_fixed.append(list(map(str, list(row))))
# Take the calories from the array of arrays (since we're just getting the #)
var_fixed = var_fixed[0][0]
if var != None:
try:
var_fixed = float(var_fixed)
except:
var_fixed = 0
else:
print("remove_decimal_fix bug")
return var_fixed
def get_calories_consumed_from_user(user_id):
cur = conn.cursor()
cur.execute("SELECT SUM(calories) FROM FoodDiary, Users WHERE FoodDiary.user_id = Users.user_id AND FoodDiary.user_id = %s", (user_id))
calories = cur.fetchall()
calories = remove_decimal_fix(calories)
return calories
def get_protein_consumed_from_user(user_id):
cur = conn.cursor()
cur.execute("SELECT SUM(protein) FROM FoodDiary, Users WHERE FoodDiary.user_id = Users.user_id AND FoodDiary.user_id = %s", (user_id))
protein = cur.fetchall()
protein = remove_decimal_fix(protein)
return protein
def get_carbohydrate_consumed_from_user(user_id):
cur = conn.cursor()
cur.execute("SELECT SUM(carbohydrate) FROM FoodDiary, Users WHERE FoodDiary.user_id = Users.user_id AND FoodDiary.user_id = %s", (user_id))
carbohydrate = cur.fetchall()
carbohydrate = remove_decimal_fix(carbohydrate)
return carbohydrate
def get_fat_consumed_from_user(user_id):
cur = conn.cursor()
cur.execute("SELECT SUM(fat) FROM FoodDiary, Users WHERE FoodDiary.user_id = Users.user_id AND FoodDiary.user_id = %s", (user_id))
fat = cur.fetchall()
fat = remove_decimal_fix(fat)
return fat
def get_food_consumed_for_user_from_food_diary_db(user_id):
cur = conn.cursor()
cur.execute("SELECT * FROM FoodDiary, Users WHERE FoodDiary.user_id = Users.user_id AND FoodDiary.user_id = %s", (user_id))
food_items_for_user = cur.fetchall()
food_list = []
for row in food_items_for_user:
food_list.append(list(map(str, list(row))))
result = []
for item in food_list:
result.append(item[0])
return result
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~[Get Stats from FoodItem DB for each food item]~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #
# ~~~~~[ Return Grams for Certain Food Item]~~~~~~ #
def get_food_item_grams_facts(food_name):
cur = conn.cursor()
cur.execute("SELECT grams FROM FoodItems WHERE food_name = %s", (food_name))
food_item = cur.fetchone()
food_item_calories = food_item[0]
return float(food_item_calories)
# ~~~~~[ Return Calories for Certain Food Item]~~~~~~ #
def get_food_item_calorie_facts(food_name):
cur = conn.cursor()
cur.execute("SELECT calories FROM FoodItems WHERE food_name = %s", (food_name))
food_item = cur.fetchone()
food_item_calories = food_item[0]
return float(food_item_calories)
# ~~~~~[ Return Protein Content for Certain Food Item]~~~~~~ #
def get_food_item_protein_facts(food_name):
cur = conn.cursor()
cur.execute("SELECT protein FROM FoodItems WHERE food_name = %s", (food_name))
food_item = cur.fetchone()
food_item_protein = food_item[0]
return float(food_item_protein)
# ~~~~~[ Return Carbohydrate Content for Certain Food Item]~~~~~~ #
def get_food_item_carbohydrate_facts(food_name):
cur = conn.cursor()
cur.execute("SELECT carbohydrate FROM FoodItems WHERE food_name = %s", (food_name))
food_item = cur.fetchone()
food_item_carbohydrate = food_item[0]
return float(food_item_carbohydrate)
# ~~~~~[ Return Fat Content for Certain Food Item]~~~~~~ #
def get_food_item_fat_facts(food_name):
cur = conn.cursor()
cur.execute('SELECT fat FROM FoodItems WHERE food_name = %s', (food_name))
food_item = cur.fetchone()
food_item_fat = food_item[0]
return float(food_item_fat)
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #
# (Unused) Might use for a future iteration
def get_time_from_dt(dt):
time_formatted = dt.strftime("%H:%M:%S")
hours = time_formatted[0:2]
minutes = time_formatted[3:5]
seconds = time_formatted[6:9]
result = hours + minutes
print(result)
return result
def get_calories_and_time_consumed_from_user(user_id):
cur = conn.cursor()
cur.execute("SELECT calories, dt FROM FoodDiary, Users WHERE FoodDiary.user_id = Users.user_id AND FoodDiary.user_id = %s", (user_id))
calories_dt_obj = cur.fetchall()
# Remove the "Decimal" part
calorie_timeread_list = []
for row in calories_dt_obj:
calorie_timeread_list.append(list(map(str, list(row))))
for food_item_reading in calorie_timeread_list:
food_item_reading[1] = food_item_reading[1][11:]
hour = int(food_item_reading[1][0:2])
minute = food_item_reading[1][3:5]
second = food_item_reading[1][6:9]
if hour > 12:
hour -= 12
hour = str(hour)
am_pm = "PM"
else:
if hour < 10:
hour = food_item_reading[1][1:2]
am_pm = "AM"
my_formatted_time = "%s:%s:%s %s" % (hour, minute, second, am_pm)
food_item_reading[1] = my_formatted_time
# Initialize arrays with 0 to better see the line chart (Graph 1)
calorie_consumption_list = [0]
calorie_accumulation_list = [0]
time_calories_read_list = [0]
accumulation_calories = 0
for i in range(len(calorie_timeread_list)):
calorie_consumption_list.append(calorie_timeread_list[i][0])
accumulation_calories += float(calorie_timeread_list[i][0])
calorie_accumulation_list.append(str(accumulation_calories))
time_calories_read_list.append(calorie_timeread_list[i][1])
return calorie_consumption_list, calorie_accumulation_list, time_calories_read_list
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #