python中的SQLite3更新了多个可能的匹配项

Question

也许你可以帮我。我正在选择数据库中最早的#行，然后想更新我选择的每个项目的日期列。

from datetime import datetime
import sqlite3
conn = sqlite3.connect('testing.db')
cursor = conn.cursor()
# Create the table
cursor.execute('CREATE TABLE IF NOT EXISTS players (player_tag TEXT, update_date TEXT, UNIQUE(player_tag));')
max_player_tags = 2
# Insert some data with old dates
arr = [['tag1','20200123T05:06:07'], ['tag2','20200123T05:06:07'], ['tag3','20200123T05:06:07'], ['tag4', datetime.now().isoformat()]]
cursor.executemany('INSERT OR IGNORE INTO PLAYERS values (?, ?)', arr)
conn.commit()
#Select the oldest 2 items
old_tags = [i[0] for i in cursor.execute('SELECT player_tag FROM players ORDER BY update_date DESC LIMIT 2')]
print(old_tags)
#Now update the dates to now
cursor.execute('UPDATE players SET update_date = datetime("now") WHERE player_tag in %s' % old_tags)
print([i for i in 'SELECT * FROM players'])
cursor.close()
conn.close()

我得到的错误是

cursor.execute('UPDATE players SET update_date = datetime("now") WHERE player_tag in %s' % old_tags)
sqlite3.OperationalError: no such table: 'tag1', 'tag2'
['tag1', 'tag2']

我也尝试过：

cursor.executemany('INSERT OR IGNORE INTO PLAYERS values (?, ?) ON DUPLICATE KEY UPDATE', upd_arr)

有什么想法吗？

Answer 1

我在这里只使用一个查询：

sql = """UPDATE players
SET update_date = datetime("now")
WHERE player_tag IN (SELECT player_tag FROM players
ORDER BY update_date DESC LIMIT 2)"""
cursor.execute(sql)