sqlite python, nested loop stops main loop

Question

import  sqlite3
conn = sqlite3.connect('food.db')
c = conn.cursor()

c.execute('''CREATE TABLE IF NOT EXISTS nutritional_values
                (item, calories, total fat, protein)''')
items = [       ('Broccoli Chinese',    22,     0.7,    1.1),
                ('chia seeds',          490,    30.8,   15.6),
                ('blueberries',         57,     0.3,    0.7),]
c.executemany('INSERT INTO nutritional_values VALUES (?,?,?,?)',items)


c.execute('''CREATE TABLE IF NOT EXISTS food_consumption
                (date, item, amount)''')
c.execute('DELETE FROM food_consumption')
consumed = [    ('24/8/2019', 'Broccoli Chinese',    1.5),
                ('24/8/2019', 'chia seeds',          0.35),
                ('24/8/2019', 'blueberries',         0.4),]
c.executemany('INSERT INTO food_consumption VALUES (?,?,?)',consumed)

conn.commit()
conn.close()

If I run:

for row in c.execute('SELECT * FROM food_consumption'):
    print(row[1])

as expected I get:

Broccoli Chinese
chia seeds
blueberries

but if I run:

for row in c.execute('SELECT * FROM food_consumption'):
    print(row[1])
    for number in c.execute('SELECT calories FROM nutritional_values WHERE item=(?)', (row[1],)):
        print(number)

I get:

Broccoli Chinese
(22,)

So it no longer loops over all the rows in the table 'food_consumption', how do I get it to loop over all rows?

Tim Biegeleisen · Accepted Answer · 2019-08-30 05:41:19Z

1

I don't know exactly why your current approach is not working, but in any case it is a bad design pattern and you should instead by doing a join between the two tables, handling the relationship on the database side:

sql = """SELECT nv.item, nv.calories
         FROM nutritional_values nv
         INNER JOIN food_consumption fc
             ON nv.item = fc.item"""

for row in c.execute(sql):
    print(row[1])

Note that this is the preferred way to handle your problem, because it avoids loading an entire result set into Python's memory.

answered Aug 30, 2019 at 5:41

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Runner Bean Over a year ago

how could i also include 'amount' from food_consumption table in the printed results?

Tim Biegeleisen Over a year ago

Just use SELECT nv.item, nv.calories, fc.amount instead of the SELECT clause in my current answer.

Runner Bean Over a year ago

also print(row[1]) will print a numerical value, but how can i actually use that value in python code, like var = row[2]*row[3] does not put the actual numerical value in var

Tim Biegeleisen Over a year ago

You may have to cast the value from string to integer, e.g. using int().

ruohola · Accepted Answer · 2019-08-30 05:41:39Z

0

The cursor can only hold results of one query at a time, you need to save the results of the first query to a variable:

first = c.execute('SELECT * FROM food_consumption').fetchall()

for row in first:
    print(row[1])
    for number in c.execute('SELECT calories FROM nutritional_values WHERE item=(?)', (row[1],)):
        print(number)

answered Aug 30, 2019 at 5:41

ruohola

24.9k7 gold badges76 silver badges118 bronze badges

Collectives™ on Stack Overflow

sqlite python, nested loop stops main loop

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related