Ubuntu
35 lines
1.3 KiB
Python
35 lines
1.3 KiB
Python
import sqlite3
|
|
|
|
conn = sqlite3.connect('cms.db')
|
|
c = conn.cursor()
|
|
|
|
# 查更新前时间范围
|
|
c.execute('SELECT MIN(created_at), MAX(created_at), COUNT(*) FROM news_posts WHERE id >= 2')
|
|
row = c.fetchone()
|
|
print(f'更新前时间范围: {row[0]} ~ {row[1]}，共 {row[2]} 篇')
|
|
|
|
# 一次性将所有采集文章的 created_at 和 updated_at 加2年
|
|
# 使用 SQLite 的 datetime 函数，一条语句全部搞定，不存在顺序问题
|
|
c.execute("UPDATE news_posts SET created_at = datetime(created_at, '+2 years'), updated_at = datetime(updated_at, '+2 years') WHERE id >= 2")
|
|
print(f'已更新 {c.rowcount} 条记录')
|
|
conn.commit()
|
|
|
|
# 验证更新后的范围
|
|
c.execute('SELECT MIN(created_at), MAX(created_at) FROM news_posts WHERE id >= 2')
|
|
row = c.fetchone()
|
|
print(f'更新后时间范围: {row[0]} ~ {row[1]}')
|
|
|
|
# 抽样几条查看
|
|
c.execute('SELECT id, title, created_at FROM news_posts WHERE id >= 2 ORDER BY created_at DESC LIMIT 5')
|
|
print('\n最新5篇:')
|
|
for r in c.fetchall():
|
|
print(f' id={r[0]} created_at={r[2]} title={r[1][:20]}')
|
|
|
|
c.execute('SELECT id, title, created_at FROM news_posts WHERE id >= 2 ORDER BY created_at ASC LIMIT 5')
|
|
print('\n最早5篇:')
|
|
for r in c.fetchall():
|
|
print(f' id={r[0]} created_at={r[2]} title={r[1][:20]}')
|
|
|
|
conn.close()
|
|
print('\n完成！')
|