sql - 更新Sqlite3列，关键匹配问题

Question

我知道这是一个愚蠢的问题，但是你如何通过将列与 sqlite 中的键匹配来更新列，我正在尝试这种方法，但我一直没有匹配到，我知道键应该匹配当我从数据库中获取它们时。我做错了什么？

c.execute('''SELECT ROUND(AVG(ft.score), 2), st.subreddit_id
         FROM posts as ft JOIN subreddits as st ON st.subreddit_id == ft.subreddit_id
         GROUP BY 2
         ORDER BY 2 desc
         ''')

average = []
for rows in c.fetchall():
    average.append(rows)

for index in average:
    start = time.time()
    print(index)
    try:
         c.execute('''UPDATE subreddits SET average_score = ''' + str(index[0]) + ''' WHERE ''' + str(index[1]) + ''' == subreddit_id;''')
         print(str(index[1]))
    except sqlite3.OperationalError:
        print('{} not found'.format(index))

Answer 1

您应该将参数作为第二个参数传递给 .execute() 以允许 sqlite3 进行正确的转义。您还遇到了语法错误 ==。试试这个:

c.execute('''UPDATE subreddits 
             SET average_score = ? WHERE ? = subreddit_id;''', (index[0],index[1]))