def test_AddFoodAndGetMacros(self):
# Test the functions AddFood() and GetMacros()
database.MakeDatabase()
# Add
database.AddFood("Potato", 100)
macroValues = (77, 0.09, 16.2, 2.2, 2, 79, 6)
# Get
self.assertEqual(
database.GetMacros(datehandler.GetToday())[datehandler.GetToday()],
macroValues)
database.RemoveDatabase()
def AddFood(food, weight, date=None):
# API function.
if food not in macros.data or weight < 0:
# Food doesn't exist or weight is less than 0.
return False
# Now try to add the macros
if not date:
# No date supplied, we default to today's date
date = datehandler.GetToday()
success = AddMacros(food, weight, date)
if not success:
# Database update failed
return
# Database update succeeded
# Now add the individual food stuff too.
conn = sqlite3.connect(DATABASE)
cursor = conn.cursor()
# First check if the food was added on the date. If it was, we update its weight
cursor.execute("SELECT ID, Weight FROM {} WHERE Name=? AND Date=?".format(TABLE_FOODS), (food, date,))
row = cursor.fetchone()
if not row:
# A food with this name was not yet logged on this date.
# First insertion
sqlStatement = "INSERT INTO {} (Name, Weight, Date) VALUES (?, ?, ?)".format(TABLE_FOODS)
cursor.execute(sqlStatement, (food, weight, date,))
else:
# A food was already logged, just update the weight.
foodId, currentWeight = row
sqlStatement = "UPDATE {} SET Weight=? WHERE ID=?".format(TABLE_FOODS)
cursor.execute(sqlStatement, (currentWeight+weight, foodId,))
# Commit the changes and close the connection
conn.commit()
conn.close()
return True # success
def GetMacros(start, end=None):
# Get the macros for all the dates from <start> to <end> (inclusive)
# returns a tuple of tuples, containing the results
if not end:
# If no end date is specified, default to today
end = datehandler.GetToday()
query = "SELECT {}, Date FROM {} WHERE Date >= ? AND Date <= ?;".format(", ".join(macros.Macros._fields), TABLE_MACROS)
conn = sqlite3.connect(DATABASE)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute(query, (start, end))
rows = cursor.fetchall()
conn.close()
if not rows:
# Empty
return None
results = {}
for row in rows:
date = row["Date"]
subresult = {}
for key in row.keys()[:-1]:
# all except date (which is the last key)
subresult[key] = row[key]
# Final result, sorted by date
results[date] = macros.Macros(**subresult)
return results
def UpdateUserSettings(macroValues):
# Update the user's macro target settings
# input: a Macros namedtuple
# First, let's build the query
if macroValues._fields != macros.Macros._fields:
# Incorrect data?
print("Incorrect data supplied to UpdateUserSettings(): {}".format(macroValues))
return
updateKeys, updateValues = [], []
for field in macroValues._fields:
value = getattr(macroValues, field)
if value is None:
# It's None, skip it
continue
# If we reached here, it's a value that needs to be updated
updateKeys.append(field)
updateValues.append(value)
if not updateKeys:
# All values were None. Nothing to update.
return
questionMarks = ", ".join(["{}=?".format(field) for field in updateKeys])
date = datehandler.GetToday()
# No WHERE because there's only one row at all times in this table, which is initialized to 0.
sqlStatement = "UPDATE {} SET {}, Date=?".format(TABLE_SETTINGS, questionMarks)
conn = sqlite3.connect(DATABASE)
cursor = conn.cursor()
cursor.execute(sqlStatement, (*updateValues, date,))
conn.commit()
conn.close()
# Return True to indicate success
return True
def GetMacros(start=None, end=None):
# Get the macros that were tracked on the days between <start> and <end>, inclusive
# start and end are tuples of (day, month, year) values
# if start and end are None, will default to today's date in the database function (GetMacros() database.by)
startTimestamp = datehandler.GetToday()
if start:
startTimestamp = datehandler.GetTimestampFromDate(
datehandler.GetDatetimeObject(*start))
if end:
end = datehandler.GetTimestampFromDate(
datehandler.GetDatetimeObject(*end))
results = database.GetMacros(startTimestamp, end)
return results
def GetFoods(date=None):
# Get the foods that were logged on <date>
# If date is None, default to today
if date:
timestamp = datehandler.GetTimestampFromDate(
datehandler.GetDatetimeObject(*date))
date = timestamp
else:
date = datehandler.GetToday()
foods = database.GetFoods(date)
# Stringify the date
dateString = datehandler.GetDateString(date)
return (foods, dateString)
def RemoveFood(food, weight, date=None):
# Calculate the macros and remove them from the database at <date>.
# If date is None, defaults to day
if food not in macros.data:
return False
foodLog = GetFoods(date)
if food not in foodLog:
# This food wasn't even logged on the date, we can't remove it
print("Tried to remove food that was never logged: {}".format(food))
return False
if weight > foodLog[food]:
# Weight is larger than was logged for this food, some error?
print("Weight is larger than logged for: {}".format(food))
return False
# Make the weight negative
weight = -(weight)
if not date:
# date is None, default to today
date = datehandler.GetToday()
macroValues = macros.CalculateMacros(food, weight)
updateSuccess = UpdateMacros(macroValues, date)
if not updateSuccess:
# Macro update failed
return False
# Now remove the amount from the food log table
# Get the ID for the food in question
conn = sqlite3.connect(DATABASE)
cursor = conn.cursor()
cursor.execute("SELECT ID FROM {} WHERE Name=? AND Date=?".format(TABLE_FOODS), (food, date))
row = cursor.fetchone()
if not row:
print("Database error: did not find {} in {}!".format(food, TABLE_FOODS))
return False
rowId, = row
# Now update the weight value
# weight is actually a negative number,
# so positive + -negative will result in a reduction
newWeight = foodLog[food] + weight
if newWeight == 0:
# Zero weight, remove row completely.
query = "DELETE FROM {} WHERE ID=?".format(TABLE_FOODS)
cursor.execute(query, (rowId,))
else:
# Simply update the weight
query = "UPDATE {} SET Weight=? WHERE ID=?".format(TABLE_FOODS)
cursor.execute(query, (newWeight, rowId,))
# Save changes and close the connection
conn.commit()
conn.close()
return True
def test_UpdateAndGetUserSettings(self):
# Test the functions UpdateUserSettings() and GetUserSettings()
database.MakeDatabase()
expectedDate = datehandler.GetToday()
settings = macros.Macros(Calories=2000,
Fat=10,
Carbs=360,
Fiber=60,
Protein=50,
Water=1000,
Sodium=7000)
database.UpdateUserSettings(settings)
# Now check that the settings were added
self.assertEqual(database.GetUserSettings(), (settings, expectedDate))
# Now test changing only some of the values.
# A None value means we don't want to update it
settings = macros.Macros(Calories=None,
Fat=12,
Carbs=300,
Fiber=None,
Protein=None,
Water=1200,
Sodium=None)
expectedSettings = macros.Macros(Calories=2000,
Fat=12,
Carbs=300,
Fiber=60,
Protein=50,
Water=1200,
Sodium=7000)
database.UpdateUserSettings(settings)
self.assertEqual(database.GetUserSettings(),
(expectedSettings, expectedDate))
# Now test supplying only None values, meaning that nothing should be changed
# and the UpdateUserSettings() should abort its operation without querying the database at all.
settings = macros.Macros(Calories=None,
Fat=None,
Carbs=None,
Fiber=None,
Protein=None,
Water=None,
Sodium=None)
database.UpdateUserSettings(settings)
# the "expected" tuple should still match
self.assertEqual(database.GetUserSettings(),
(expectedSettings, expectedDate))
database.RemoveDatabase()
def test_AddFoodAndRemoveFood(self):
# Test the function RemoveFood()
database.MakeDatabase()
database.AddFood("Potato", 100)
database.AddFood("Apple", 100)
foods = database.GetFoods(datehandler.GetToday())
self.assertIn("Potato", foods)
self.assertIn("Apple", foods)
self.assertEqual(foods["Potato"], 100)
self.assertEqual(foods["Apple"], 100)
# Now remove 50 grams
database.RemoveFood("Potato", 50)
database.RemoveFood("Apple", 50)
foods = database.GetFoods()
self.assertEqual(foods["Potato"], 50)
self.assertEqual(foods["Apple"], 50)
# remove database
database.RemoveDatabase()
def GetFoods(date=None):
# Show the foods logged in the Foods table on <date>
# If date is None, default to today
if not date:
date = datehandler.GetToday()
query = "SELECT Name, Weight FROM {} WHERE Date=?".format(TABLE_FOODS)
conn = sqlite3.connect(DATABASE)
cursor = conn.cursor()
cursor.execute(query, (date,))
rows = cursor.fetchall()
foods = {} # name: amount
for row in rows:
name, amount = row
try:
foods[name] += amount
except KeyError:
foods[name] = amount
return foods