tianxiaqingqi/CSRC

1import sqlite3
2import json
3import os
4
5class FeedbackDatabase:
6    def __init__(self, db_name='feedback.db'):
7        self.db_name = db_name
8        self.conn = None
9        self.cursor = None
10        self.init_db()
11    
12    def init_db(self):
13        """初始化数据库，创建表结构"""
14        try:
15            self.conn = sqlite3.connect(self.db_name)
16            self.cursor = self.conn.cursor()
17            
18            # 创建反馈意见表
19            self.cursor.execute('''
20                CREATE TABLE IF NOT EXISTS feedbacks (
21                    id INTEGER PRIMARY KEY AUTOINCREMENT,
22                    title TEXT NOT NULL,
23                    link TEXT NOT NULL,
24                    date TEXT,
25                    category TEXT NOT NULL,
26                    content TEXT,
27                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
28                )
29            ''')
30            
31            # 创建索引，提高查询速度
32            self.cursor.execute('CREATE INDEX IF NOT EXISTS idx_category ON feedbacks(category)')
33            self.cursor.execute('CREATE INDEX IF NOT EXISTS idx_date ON feedbacks(date)')
34            self.cursor.execute('CREATE INDEX IF NOT EXISTS idx_title ON feedbacks(title)')
35            
36            self.conn.commit()
37            print(f'数据库初始化成功: {self.db_name}')
38        except Exception as e:
39            print(f'数据库初始化失败: {e}')
40    
41    def insert_feedback(self, feedback):
42        """插入一条反馈意见"""
43        try:
44            # 检查是否已存在相同链接的记录
45            self.cursor.execute('SELECT id FROM feedbacks WHERE link = ?', (feedback['link'],))
46            existing = self.cursor.fetchone()
47            if existing:
48                # 更新现有记录
49                self.cursor.execute('''
50                    UPDATE feedbacks SET 
51                        title = ?, 
52                        date = ?, 
53                        category = ?, 
54                        content = ? 
55                    WHERE link = ?
56                ''', (feedback['title'], feedback['date'], feedback['category'], 
57                      feedback.get('content', ''), feedback['link']))
58            else:
59                # 插入新记录
60                self.cursor.execute('''
61                    INSERT INTO feedbacks (title, link, date, category, content)
62                    VALUES (?, ?, ?, ?, ?)
63                ''', (feedback['title'], feedback['link'], feedback['date'], 
64                      feedback['category'], feedback.get('content', '')))
65            self.conn.commit()
66            return True
67        except Exception as e:
68            print(f'插入反馈意见失败: {e}')
69            self.conn.rollback()
70            return False
71    
72    def batch_insert(self, feedbacks):
73        """批量插入反馈意见"""
74        try:
75            count = 0
76            for feedback in feedbacks:
77                if self.insert_feedback(feedback):
78                    count += 1
79            print(f'批量插入完成，成功 {count} 条，失败 {len(feedbacks) - count} 条')
80            return count
81        except Exception as e:
82            print(f'批量插入失败: {e}')
83            return 0
84    
85    def get_feedbacks_by_category(self, category):
86        """根据类别获取反馈意见"""
87        try:
88            self.cursor.execute('SELECT * FROM feedbacks WHERE category = ? ORDER BY date DESC', (category,))
89            return self.cursor.fetchall()
90        except Exception as e:
91            print(f'查询失败: {e}')
92            return []
93    
94    def search_feedbacks(self, keyword):
95        """根据关键词搜索反馈意见"""
96        try:
97            self.cursor.execute('''
98                SELECT * FROM feedbacks 
99                WHERE title LIKE ? OR content LIKE ? 
100                ORDER BY date DESC
101            ''', (f'%{keyword}%', f'%{keyword}%'))
102            return self.cursor.fetchall()
103        except Exception as e:
104            print(f'搜索失败: {e}')
105            return []
106    
107    def get_all_feedbacks(self):
108        """获取所有反馈意见"""
109        try:
110            self.cursor.execute('SELECT * FROM feedbacks ORDER BY date DESC')
111            return self.cursor.fetchall()
112        except Exception as e:
113            print(f'查询失败: {e}')
114            return []
115    
116    def get_feedback_count(self):
117        """获取反馈意见总数"""
118        try:
119            self.cursor.execute('SELECT COUNT(*) FROM feedbacks')
120            result = self.cursor.fetchone()
121            return result[0] if result else 0
122        except Exception as e:
123            print(f'查询失败: {e}')
124            return 0
125    
126    def close(self):
127        """关闭数据库连接"""
128        if self.conn:
129            self.conn.close()
130            print('数据库连接已关闭')
131
132    def import_from_json(self, json_file):
133        """从JSON文件导入数据"""
134        try:
135            if not os.path.exists(json_file):
136                print(f'文件不存在: {json_file}')
137                return 0
138            
139            with open(json_file, 'r', encoding='utf-8') as f:
140                data = json.load(f)
141            
142            return self.batch_insert(data)
143        except Exception as e:
144            print(f'导入失败: {e}')
145            return 0
146
147if __name__ == '__main__':
148    # 测试数据库功能
149    db = FeedbackDatabase()
150    
151    # 导入证券类反馈意见
152    db.import_from_json('securities_feedbacks.json')
153    
154    # 导入基金类反馈意见
155    db.import_from_json('fund_feedbacks.json')
156    
157    # 导入所有反馈意见
158    db.import_from_json('all_feedbacks.json')
159    
160    # 获取反馈意见总数
161    count = db.get_feedback_count()
162    print(f'反馈意见总数: {count}')
163    
164    # 测试搜索功能
165    search_result = db.search_feedbacks('监管')
166    print(f'搜索 "监管" 结果: {len(search_result)} 条')
167    if search_result:
168        print('前3条结果:')
169        for item in search_result[:3]:
170            print(f'标题: {item[1]}, 日期: {item[3]}, 类别: {item[4]}')
171    
172    db.close()
173