117 lines
4.7 KiB
Python
117 lines
4.7 KiB
Python
import sqlite3
|
|
import os
|
|
from datetime import datetime, timedelta
|
|
|
|
class DatabaseManager:
|
|
def __init__(self, db_path='articles.db'):
|
|
self.db_path = db_path
|
|
self._init_db()
|
|
|
|
def _init_db(self):
|
|
"""初始化数据库，创建必要的表"""
|
|
with sqlite3.connect(self.db_path) as conn:
|
|
cursor = conn.cursor()
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS processed_articles (
|
|
url TEXT PRIMARY KEY,
|
|
title TEXT,
|
|
processed_time TIMESTAMP,
|
|
content_type TEXT,
|
|
optimized_title TEXT,
|
|
published BOOLEAN DEFAULT 0
|
|
)
|
|
""")
|
|
conn.commit()
|
|
|
|
async def is_article_processed(self, url):
|
|
"""检查文章是否已经处理过"""
|
|
with sqlite3.connect(self.db_path) as conn:
|
|
cursor = conn.cursor()
|
|
cursor.execute(
|
|
"SELECT url FROM processed_articles WHERE url = ?",
|
|
(url,)
|
|
)
|
|
return cursor.fetchone() is not None
|
|
|
|
async def add_processed_article(self, url, title, content_type=None, optimized_title=None):
|
|
"""添加已处理的文章记录，但未发布状态"""
|
|
with sqlite3.connect(self.db_path) as conn:
|
|
cursor = conn.cursor()
|
|
cursor.execute(
|
|
"""INSERT INTO processed_articles
|
|
(url, title, processed_time, content_type, optimized_title, published)
|
|
VALUES (?, ?, ?, ?, ?, ?)""",
|
|
(url, title, datetime.now(), content_type, optimized_title, False)
|
|
)
|
|
conn.commit()
|
|
|
|
async def mark_article_published(self, url):
|
|
"""将文章标记为已发布状态"""
|
|
with sqlite3.connect(self.db_path) as conn:
|
|
cursor = conn.cursor()
|
|
cursor.execute(
|
|
"UPDATE processed_articles SET published = ? WHERE url = ?",
|
|
(True, url)
|
|
)
|
|
conn.commit()
|
|
|
|
async def cleanup_old_records(self, days=7):
|
|
"""清理指定天数之前的记录"""
|
|
cleanup_date = datetime.now() - timedelta(days=days)
|
|
with sqlite3.connect(self.db_path) as conn:
|
|
cursor = conn.cursor()
|
|
cursor.execute(
|
|
"DELETE FROM processed_articles WHERE processed_time < ?",
|
|
(cleanup_date,)
|
|
)
|
|
conn.commit()
|
|
print(f"已清理 {days} 天前的数据库记录")
|
|
|
|
async def get_recent_articles(self, limit=10):
|
|
"""获取最近处理的文章列表"""
|
|
with sqlite3.connect(self.db_path) as conn:
|
|
cursor = conn.cursor()
|
|
cursor.execute(
|
|
"""SELECT url, title, processed_time, content_type, optimized_title
|
|
FROM processed_articles
|
|
ORDER BY processed_time DESC
|
|
LIMIT ?""",
|
|
(limit,)
|
|
)
|
|
return cursor.fetchall()
|
|
|
|
async def cleanup_article_images(self, url, image_dir='images'):
|
|
"""清理已处理文章的图片"""
|
|
try:
|
|
# 检查文章是否存在于数据库中
|
|
with sqlite3.connect(self.db_path) as conn:
|
|
cursor = conn.cursor()
|
|
cursor.execute(
|
|
"SELECT 1 FROM processed_articles WHERE url = ?",
|
|
(url,)
|
|
)
|
|
result = cursor.fetchone()
|
|
|
|
if result:
|
|
# 文章存在于数据库中，清理相关图片
|
|
article_hash = str(hash(url))
|
|
image_path = os.path.join(image_dir, f"{article_hash}*")
|
|
|
|
# 使用glob模块查找匹配的文件
|
|
import glob
|
|
matching_files = glob.glob(image_path)
|
|
|
|
# 删除找到的所有匹配文件
|
|
for file_path in matching_files:
|
|
try:
|
|
if os.path.exists(file_path):
|
|
os.remove(file_path)
|
|
print(f"已删除图片：{file_path}")
|
|
except Exception as e:
|
|
print(f"删除图片文件时出错：{file_path} - {str(e)}")
|
|
|
|
if not matching_files:
|
|
print(f"未找到与URL对应的图片文件：{url}")
|
|
|
|
except Exception as e:
|
|
print(f"清理图片时出错：{str(e)}") |