예제 #1
0
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
예제 #2
0
    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
예제 #3
0
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()
예제 #4
0
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
예제 #5
0
 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
예제 #6
0
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
예제 #7
0
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)
예제 #8
0
    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()
예제 #9
0
    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()
예제 #10
0
    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()
예제 #11
0
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()
예제 #12
0
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()
예제 #13
0
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()
예제 #14
0
    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)
예제 #15
0
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()
예제 #16
0
    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()
예제 #17
0
    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()
예제 #18
0
    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