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()
如果我跑
for row in c.execute('SELECT * FROM food_consumption'):
print(row[1])
正如所料,我得到了
Broccoli Chinese
chia seeds
blueberries
但如果我跑
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)
我明白了
Broccoli Chinese
(22,)
所以它不再遍历表'food_consumption'中的所有行,我如何让它循环遍历所有行。
分析解答
我不知道为什么你当前的方法不起作用,但无论如何它是一个糟糕的设计模式,你应该通过在两个表之间进行连接,处理数据库端的关系:
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])
请注意,这是处理问题的首选方法,因为它可以避免将整个结果集加载到Python的内存中。