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