def get_nuts_from_db():
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
sql_stmt = ('SELECT * ' 'FROM nuts ' 'ORDER BY rowid ')
cur.execute(sql_stmt)
nuts = cur.fetchall()
macro_end = vit_start = len(req.macro_names)
vit_end = mineral_start = len(req.vit_names) + len(req.mineral_names)
macro = [
Nutrient(name=n[0], min=n[1], max=n[2], target=n[3])
for n in nuts[0:macro_end]
]
vit = [
Nutrient(name=n[0], min=n[1], max=n[2], target=n[3])
for n in nuts[vit_start:vit_end]
]
mineral = [
Nutrient(name=n[0], min=n[1], max=n[2], target=n[3])
for n in nuts[mineral_start:]
]
return macro, vit, mineral
def get_selectable_units(self):
con = sql.connect(database.resource_path('sr_legacy/sr_legacy.db'))
cur = con.cursor()
sql_stmnt = ('SELECT amount, description, gm_weight '
'FROM weight '
'WHERE food_id = ?'
'ORDER BY sequence_num')
cur.execute(sql_stmnt, [self.food_id])
units = cur.fetchall()
con.commit()
cur.close()
selectable_units = ['g', 'oz (28.35 g)', 'lb (453.6 g)']
for unit in units:
# Strip amount prefix if it's 1.0
if unit[0] == 1.0:
amount_display = ''
else:
amount_display = '{:f}'.format(
unit[0]).rstrip('0').rstrip('.') + ' '
# Make unit text description with equivalent grams
description = unit[1]
gm_weight_display = '{:f}'.format(unit[2]).rstrip('0').rstrip('.')
gm_weight_display = ' (' + gm_weight_display + ' g)'
unit = amount_display + description + gm_weight_display
# Add to selectable units if unique
if unit not in selectable_units:
selectable_units.append(amount_display + description +
gm_weight_display)
return selectable_units
def create_spartan_db():
if os.path.isfile(database.resource_path('spartan.db')):
return
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
person_stmt = (
'CREATE TABLE person ( '
'sex TEXT, '
'bd_year INTEGER,'
'bd_mon INTEGER,'
'bd_day INTEGER)'
)
nuts_stmt = (
'CREATE TABLE nuts ( '
'name TEXT, '
'min REAL, '
'max REAL, '
'target REAL )'
)
foods_stmt = (
'CREATE TABLE foods ( '
'food_id INTEGER, '
'name TEXT, '
'price REAL, '
'price_quantity REAL, '
'price_unit TEXT, '
'min REAL, '
'min_unit TEXT, '
'max REAL, '
'max_unit TEXT, '
'target REAL, '
'target_unit TEXT, '
'nut_quantity REAL, '
'nut_quantity_unit REAL )'
)
cur.execute(person_stmt)
cur.execute(nuts_stmt)
cur.execute(foods_stmt)
insert_nuts()
insert_default_person()
con.commit()
con.close()
def get_sex_bd_from_db():
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
sql_stmt = ('SELECT * ' 'FROM person ' 'ORDER BY rowid ')
cur.execute(sql_stmt)
(sex, bd_year, bd_mon, bd_day) = cur.fetchall()[0]
return sex, bd_year, bd_mon, bd_day
def get_unit(self):
con = sql.connect(database.resource_path('sr_legacy/sr_legacy.db'))
cur = con.cursor()
sql_stmt = ('SELECT units ' 'FROM nutr_def ' 'WHERE id = ?')
cur.execute(sql_stmt, [self.nut_id])
unit = cur.fetchall()[0][0]
con.commit()
con.close()
return unit
def get_all_food_ids():
con = sql.connect(database.resource_path('sr_legacy/sr_legacy.db'))
cur = con.cursor()
cur.execute("SELECT id FROM food_des")
food_list = cur.fetchall()
food_ids = [f[0] for f in food_list]
return food_ids
def get_nutrition(person, food_ids, food_amounts):
# Sort input lists according to food ids in ascending order
if len(food_amounts) == 0:
return get_empty_nutrition(person)
food_tups = sorted(zip(food_ids, food_amounts))
food_ids, food_amounts = (list(food_tup) for food_tup in zip(*food_tups))
sorted_nuts = sorted(person.nuts, key=lambda n: int(n.nut_id))
nut_ids = [nut.nut_id for nut in sorted_nuts]
nut_names = [nut.name for nut in sorted_nuts]
units = database.get_nutrition_units(nut_ids)
con = sql.connect(database.resource_path('sr_legacy/sr_legacy.db'))
cur = con.cursor()
sql_stmt = ('SELECT amount '
'FROM nut_data WHERE food_id IN (' +
(len(food_ids) - 1) * '?, ' + '?) '
'AND nut_id IN (' + (len(nut_ids) - 1) * '?, ' + '?) '
'ORDER BY food_id, nut_id')
cur.execute(sql_stmt, food_ids + nut_ids)
nut_amounts = np.array(cur.fetchall())
# Reshape array into matrix, where each row is a food and each column a nutrient
nut_amounts = nut_amounts.reshape(len(food_ids), len(nut_ids))
# Check if database lacks a value for each nutrient for all foods and record for later.
# If every selected food lacks a value for a given nutrient, we display No data.
# If one or more selected foods have a value, even if the rest have no value in the database,
# we treat the NULL value in the database as 0 and add them.
nut_has_data = check_if_sparse_nutrient(nut_amounts)
nut_amounts[nut_amounts == None] = 0
# Sum amounts for each respective nutrient given each amount of food
food_amounts = np.reshape(food_amounts, (len(food_amounts), 1))
nut_amounts = sum(food_amounts * (nut_amounts / DB_SCALER))
nutrition = []
for nut_name, nut_amount, has_data, unit in zip(nut_names, nut_amounts,
nut_has_data, units):
if has_data:
dv = calculate_dv(person, nut_name, nut_amount)
nutrition.append({
'name': nut_name,
'amount': nut_amount,
'unit': unit[0],
'percent': dv
})
else:
nutrition.append({
'name': nut_name,
'amount': None,
'unit': unit[0],
'percent': None
})
return sort_nutrition(nutrition)
def populate_nuts_from_db(self):
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
sql_stmt = ('SELECT * ' 'FROM nuts ' 'ORDER BY rowid')
for row in cur.execute(sql_stmt):
self.nuts.append(
Nutrient(name=row[0], min=row[1], max=row[2], target=row[3]))
con.commit()
con.close()
def remove_foods_from_db(self, food_ids):
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
foods_tuple = [(food_id, ) for food_id in food_ids]
sql_stmt = ('DELETE FROM foods ' 'WHERE food_id = ?')
cur.executemany(sql_stmt, foods_tuple)
con.commit()
con.close()
def populate_foods_from_db(self):
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
sql_stmt = ('SELECT * ' 'FROM foods ' 'ORDER BY rowid ')
for row in cur.execute(sql_stmt):
self.foods.append(Food(*row))
con.commit()
con.close()
def update_sex_bd_in_db(sex, year, mon, day):
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
sql_stmt = ('UPDATE person '
'SET (sex, bd_year, bd_mon, bd_day) = '
'(?,?,?,?)')
parameters = [sex, year, mon, day]
cur.execute(sql_stmt, parameters)
con.commit()
con.close()
def insert_default_person():
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
sql_stmt = (
'INSERT INTO person '
'VALUES (?,?,?,?) '
)
cur.execute(sql_stmt, [None, None, None, None])
con.commit()
con.close()
def insert_nuts():
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
sql_stmt = (
'INSERT INTO nuts '
'VALUES (?,?,?,?)'
)
for nut_name in req.nut_names:
parameters = [nut_name, None, None, None]
cur.execute(sql_stmt, parameters)
con.commit()
con.close()
def make_nutrition_matrix(self):
nut_ids = [nut.nut_id for nut in self.nuts]
food_ids = [food.food_id for food in self.foods]
# Due to sqlite3 limitations, we must batch large queries
food_batch_size = SQL_VARIABLE_LIMIT - len(nut_ids)
q, final_batch_size = divmod(len(food_ids), food_batch_size)
n_batches = q + bool(final_batch_size)
con = sql.connect(database.resource_path('sr_legacy/sr_legacy.db'))
cur = con.cursor()
nut_data = []
# Get nutritional values for each of the nutrients for each user's food.
# We must programmatically generate a SQL statement with a variable length number of parameters
# since food_ids and nut_ids vary depending on user settings
if n_batches > 1:
for batch in range(n_batches - 1):
sql_stmnt = ('SELECT amount '
'FROM nut_data '
'WHERE food_id IN (?' +
(food_batch_size - 1) * ',?' + ') '
'AND nut_id IN (?' + (len(nut_ids) - 1) * ',?' +
')'
'ORDER BY food_id, nut_id')
batch_start = batch * food_batch_size
batch_end = batch * food_batch_size + food_batch_size
cur.execute(sql_stmnt,
food_ids[batch_start:batch_end] + nut_ids)
nut_data = nut_data + cur.fetchall()
sql_stmnt = ('SELECT amount '
'FROM nut_data '
'WHERE food_id IN (?' + (final_batch_size - 1) * ',?' +
') '
'AND nut_id IN (?' + (len(nut_ids) - 1) * ',?' + ')'
'ORDER BY food_id, nut_id')
if n_batches > 1:
batch_start = batch_end
else:
batch_start = 0
cur.execute(sql_stmnt, food_ids[batch_start:] + nut_ids)
nut_data = nut_data + cur.fetchall()
self.nutrition_matrix = self.format_nutrition_matrix(nut_data)
def update_nuts_in_db(nutrients):
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
for nutrient in nutrients:
sql_stmt = ('UPDATE nuts '
'SET (min, max, target) = '
'(?,?,?) '
'WHERE name = ?')
parameters = [
nutrient.min, nutrient.max, nutrient.target, nutrient.name
]
cur.execute(sql_stmt, parameters)
con.commit()
con.close()
def add_food_to_db(self, food):
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
# Store everything but selectable units, as this is a dynamic list
food_vars = list(vars(food).keys())
food_vars.remove('selectable_units')
food_tuple = str(tuple(food_vars))
food_values = [getattr(food, var) for var in food_vars]
sql_stmt = ('INSERT INTO foods' + food_tuple + ''
'VALUES (' + (len(food_vars) - 1) * '?,' + '?)')
cur.execute(sql_stmt, food_values)
con.commit()
con.close()
def update_food_in_user_db(self, food):
con = sql.connect(database.resource_path('spartan.db'))
cur = con.cursor()
food_vars = list(vars(food).keys())
food_vars.remove('selectable_units')
food_tuple = str(tuple(food_vars))
food_values = [getattr(food, var) for var in food_vars]
# Tuple comes from our dict of attr strings, so no need to sanitize
sql_stmt = ("UPDATE foods "
"SET " + food_tuple + " = "
"(" + (len(food_values) - 1) * "?," + "?)"
"WHERE food_id = ?")
cur.execute(sql_stmt, food_values + [food.food_id])
con.commit()
con.close()
def filter_foods(self, foods, fd_res):
food_ids = [food.food_id for food in foods]
food_id_batches, fd_grps = [], []
for i in range(0, len(food_ids), SQL_VARIABLE_LIMIT):
food_id_batches.append(food_ids[i:i + SQL_VARIABLE_LIMIT])
con = sql.connect(database.resource_path('sr_legacy/sr_legacy.db'))
cur = con.cursor()
for batch in food_id_batches:
sql_stmnt = ('SELECT food_group_id '
'FROM food_des '
'WHERE id IN (?' + (len(batch) - 1) * ',?' + ') '
'ORDER BY id ')
cur.execute(sql_stmnt, batch)
fd_grps = fd_grps + cur.fetchall()
filtered_foods = [
food for fd_grp, food in zip(fd_grps, foods)
if fd_grp[0] not in fd_res
]
return filtered_foods