SQLite从列列表中获取总计？

Question

我想把我列中的总价值加起来。我有5列，我可以通过以下方式得到1乘1的总值：

import sqlite3
list2 = ['Food', 'Transport', 'Insurance', 'Installments', 'Others']

def expensedata():
    con = sqlite3.connect("expense.db")
    c = con.cursor()
    c.execute("CREATE TABLE IF NOT EXISTS expense (id INTEGER PRIMARY KEY,\
     food real, transport real, insurance real, installments real, others real)")
    con.commit()
    con.close()

expensedata()



con = sqlite3.connect("expense.db")
    c = con.cursor()
    c.execute("SELECT TOTAL(food) FROM expense")
    print(c.fetchall()[0])
    con.close()

我可以得到这个列的总数26.0

但我没有对n列重复n次此代码，而是尝试：

con = sqlite3.connect("expense.db")
    c = con.cursor()
    for i in range(len(list2)):
        c.execute("SELECT TOTAL(?) FROM expense", (list2[i],))
        print(c.fetchall()[0])
    con.close()

但是输出变为(0.0,) (0.0,) (0.0,) (0.0,) (0.0,)

我该怎么解决这个问题？谢谢

Answer 1

相反，要通过单个SQL查询为所有列选择一个总计，您可以适当地组合一个查询：

list2 = ['Food', 'Transport', 'Insurance', 'Installments', 'Others']
...
con = sqlite3.connect("expense.db")
c = con.cursor()

# just for testing
sql = ''' INSERT INTO expense(food, transport, insurance, installments, others)
             VALUES(?,?,?,?,?) '''

# sample records inserted into empty DB
c.execute(sql, range(0,5))
c.execute(sql, range(5,10))
c.execute(sql, range(10,15))

c.execute("SELECT {} FROM expense"
          .format(', '.join('TOTAL({})'.format(col.lower()) for col in list2)))
print(c.fetchall()[0])
con.close()

输出：

(15.0, 18.0, 21.0, 24.0, 27.0)

Answer 2

只需使用字符串格式：

c.execute("SELECT TOTAL(%s) FROM expense" % list2[i])