sqlite python - create new table by multiplying values from existing tables

Question

• I need to create a new table 'nutrition_consumed'
• I want to create this table from existing tables 'nutritional_values' and 'food_consumption'
• Where the new table 'nutrition_consumed' has the same number of rows as the table 'food_consumption'

• And the rows of 'nutrition_consumed' are generated by multiplying the consumed 'amount' value by values for calories, total fat and protein in the 'nutritional_values' table.

  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()
  

  To create a new table

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

which would look like

(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)

Is the some sort of SQLite hack to do this?

as a start ive tried

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)

this brings up the error

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.

Answer 1

you need to supply you row as a tuple:

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