157 lines
4.7 KiB
Python
157 lines
4.7 KiB
Python
# -*- coding: utf-8 -*-
|
||
|
||
import json
|
||
import sqlite3
|
||
import os
|
||
from datetime import datetime, timedelta
|
||
|
||
def create_database():
|
||
conn = sqlite3.connect('./db/qianxin.db')
|
||
cursor = conn.cursor()
|
||
cursor.execute('''CREATE TABLE IF NOT EXISTS articles (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
title TEXT,
|
||
guid TEXT,
|
||
source TEXT,
|
||
description TEXT,
|
||
pubDate DATETIME
|
||
)''')
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def insert_data(data):
|
||
conn = sqlite3.connect('./db/qianxin.db')
|
||
cursor = conn.cursor()
|
||
for entry in data:
|
||
cursor.execute('''
|
||
INSERT INTO articles (title, guid, source, description, pubDate)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
''', (entry['title'], entry['guid'], entry['source'], entry['description'], entry['pubDate']))
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def get_qianxin_json():
|
||
# 检查文件是否存在
|
||
if not os.path.exists('./JSON/qianxin.json'):
|
||
raise FileNotFoundError(f"qianxin.json文件不存在，请检查程序是否运行正常！")
|
||
|
||
# 打开并读取JSON文件
|
||
with open('./JSON/qianxin.json', 'r', encoding='utf-8') as file:
|
||
data = json.load(file)
|
||
|
||
# 假设data是一个包含多个JSON对象的列表
|
||
if not isinstance(data, list):
|
||
raise ValueError("JSON文件格式错误，请检查common.py是否异常！")
|
||
|
||
# 提取所需字段并编号
|
||
total_data = []
|
||
for index, item in enumerate(data, start=1):
|
||
entry = {
|
||
"id": index,
|
||
"title": item.get("title", ""),
|
||
"guid": item.get("guid", ""),
|
||
"description": item.get("description", ""),
|
||
"pubDate": item.get("pubDate", ""),
|
||
"source": item.get("source", "")
|
||
}
|
||
total_data.append(entry)
|
||
|
||
return total_data
|
||
|
||
def select_articles(e_hour):
|
||
conn = sqlite3.connect('./db/qianxin.db')
|
||
cursor = conn.cursor()
|
||
|
||
# 获取当前日期和时间
|
||
now = datetime.now()
|
||
start_time = now - timedelta(hours=e_hour)
|
||
end_time = now
|
||
|
||
# 查询指定时间段内的数据
|
||
cursor.execute('''
|
||
SELECT * FROM articles
|
||
WHERE pubDate BETWEEN ? AND ?
|
||
''', (start_time.strftime('%Y-%m-%d %H:%M:%S'), end_time.strftime('%Y-%m-%d %H:%M:%S')))
|
||
|
||
results = cursor.fetchall()
|
||
conn.close()
|
||
return results
|
||
|
||
def clear_table():
|
||
conn = sqlite3.connect('./db/qianxin.db')
|
||
cursor = conn.cursor()
|
||
cursor.execute('DELETE FROM articles')
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def record_md(result, filename="./history/tech_passage.md"):
|
||
# 读取现有内容
|
||
if os.path.exists(filename):
|
||
with open(filename, 'r', encoding='utf-8') as file:
|
||
existing_content = file.read()
|
||
else:
|
||
existing_content = ""
|
||
|
||
# 将新内容插入到现有内容的开头
|
||
new_content = result + existing_content
|
||
|
||
# 写回文件
|
||
with open(filename, 'w', encoding='utf-8') as file:
|
||
file.write(new_content)
|
||
|
||
def get_filtered_articles(entries, Is_short):
|
||
result = ""
|
||
record = ""
|
||
for entry in entries:
|
||
if Is_short == False:
|
||
result += f"文章：[{entry[1]}]({entry[2]})\n来源：{entry[3]}\n"
|
||
result += f"上传时间：{entry[5]}\n"
|
||
result += f"描述：{entry[4]}\n"
|
||
result += "\n" + "-" * 40 + "\n" # 添加分隔线以便区分不同文章
|
||
if Is_short == False:
|
||
result += f"文章：[{entry[1]}]({entry[2]})\n"
|
||
result += f"上传时间：{entry[5]}\n"
|
||
result += "\n" + "-" * 3 + "\n" # 添加分隔线以便区分不同文章
|
||
|
||
record += f"#### 文章：[{entry[1]}]({entry[2]})\n"
|
||
record += f"**来源**：{entry[3]}\n"
|
||
record += f"**上传时间**：{entry[5]}\n"
|
||
record += f"**描述**：{entry[4]}\n"
|
||
record += "\n" + "-" * 40 + "\n" # 添加分隔线以便区分不同文章
|
||
record_md(record)
|
||
return result
|
||
|
||
|
||
def Src_qianxin(e_hour, Is_short):
|
||
if not os.path.exists('./db/qianxin.db'):
|
||
# 创建数据库和表
|
||
create_database()
|
||
|
||
# 清空表
|
||
clear_table()
|
||
|
||
# 获取 JSON 数据
|
||
M_qianxin_data = get_qianxin_json()
|
||
|
||
# 插入数据到数据库
|
||
insert_data(M_qianxin_data)
|
||
|
||
# 查询指定时间段内的数据
|
||
filtered_articles = select_articles(e_hour)
|
||
# print(filtered_articles)
|
||
|
||
if filtered_articles:
|
||
results = get_filtered_articles(filtered_articles, Is_short)
|
||
return results
|
||
else:
|
||
return False
|
||
|
||
if __name__ == "__main__":
|
||
reslts = Src_qianxin(4, False)
|
||
if reslts != False:
|
||
print(reslts)
|
||
else:
|
||
# 如果为空，则跳过执行
|
||
print("-" * 40)
|
||
print("奇安信攻防社区数据为空，跳过执行。")
|