python - sqlite python - 通过将现有表中的值相乘来创建新表

Question

• 我需要创建一个新表“nutrition_consumed”
• 我想从现有的表 'nutritional_values' 和 'food_consumption' 创建这个表
• 新表 'nutrition_consumed' 的行数与表 'food_consumption' 的行数相同

• “nutrition_consumed”的行是通过将消耗的“数量”值乘以“nutritional_values”表中的卡路里、总脂肪和蛋白质的值来生成的。

  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)''')
  
  consumed = [    ('24/8/2019', 'Broccoli Chinese',    1.5),
                  ('24/8/2019', 'chia seeds',          0.35),
                  ('24/8/2019', 'blueberries',         0.4),
                  ('25/8/2019', 'blueberries',         0.8),]
  
  c.executemany('INSERT INTO food_consumption VALUES (?,?,?)',consumed)
  
  conn.commit()
  
  conn.close()
  

  创建新表

  c.execute('''CREATE TABLE IF NOT EXISTS nutrition_consumed
              (date, item, calories, total fat, protein)''')
  

看起来像

(u'24/8/2019', u'Broccoli Chinese', 33, 10.5, 1.65)
(u'24/8/2019',u'blueberries', 22.8, 1.3, 0.28)
(u'24/8/2019',u'chia seeds', 171.5, 10.75, 5.4)
(u'25/8/2019',u'blueberries', 45.6, 2.4, 0.56)

是某种 SQLite hack 来做到这一点吗？

作为开始，我尝试过

c.execute('''CREATE TABLE IF NOT EXISTS nutrition_consumed
                (date, item, calories, total fat, protein)''')

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

这带来了错误

item_nutritional_values = c.execute('SELECT calories, total fat, protein FROM nutritional_values WHERE item=?', row[1])
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 16 supplied.

标签： pythonsqlite

Answer 1

你需要提供你row作为一个元组：

item_nutritional_values = c.execute('SELECT calories, total fat, protein FROM nutritional_values WHERE item=?', (row[1],))