tianxiaqingqi/CSRC

1+
2Xk�i��3��
�^R
It^R
I
t
^R
It!RR4t]R8Xd�]!4t]P
R4
]P
R4
]P
R4
]P4t]	!R]24
]PR	4
t]	!R4]!]4
R24
]'d?]	!R4
]R
,F)t
]	!R]
^
,R]
^,R]
^,24
K+	]P4R
#R
#)�Nc�`a�]t
Rt^toR
R
l
tRtRtRtRtRt	Rt5RtR	tR6t
RtVtR#)�FeedbackDatabasec�N�WnRVn
RVnVP4R#)
N)�db_name�conn�cursor�init_db)�selfrs&&�AC:\Users\hp\Documents\trae_projects\CSRC FMC feedback\database.py�__init__�FeedbackDatabase.__init__s������	��������c
��\P!VP4
VnVPP	4VnVPPR
4
VPPR4
VPPR4
VPPR4
VPP
4\RVP24
R# \dp
\RT
24
Rp
?
R#Rp
?
i
i;i
)u$初始化数据库，创建表结构a�
7                CREATE TABLE IF NOT EXISTS feedbacks (8                    id INTEGER PRIMARY KEY AUTOINCREMENT,9                    title TEXT NOT NULL,10                    link TEXT NOT NULL,11                    date TEXT,12                    category TEXT NOT NULL,13                    content TEXT,14                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP15                )16            z>CREATE INDEX IF NOT EXISTS idx_category ON feedbacks(category)z6CREATE INDEX IF NOT EXISTS idx_date ON feedbacks(date)z8CREATE INDEX IF NOT EXISTS idx_title ON feedbacks(title)u数据库初始化成功: u数据库初始化失败: N)	�sqlite3�connectrrr�execute�commit�print�	Exception�r17�
es& rr	�FeedbackDatabase.init_dbs���	4�������5�D�I��)�)�*�*�,�D�K�
�K�K���18!�19
�
�K�K��� `�a��K�K��� X�Y��K�K��� Z�[��I�I�����.�t�|�|�n�=�>���
	4��.�q�c�2�3�3��
	4�s�C"C&�&D20�1D�D21c��VPPR
V
R,3
4VPP4pV'dOVPPRV
R,V
R,V
R,V
PRR4V
R,34MMVPPR	V
R,V
R,V
R,V
R,V
PRR434VPP4R22# \d4p\RT24
TPP4Rp?R
#Rp?i
i;i
)u插入一条反馈意见z'SELECT id FROM feedbacks WHERE link = ?�linkz�23                    UPDATE feedbacks SET 24                        title = ?, 25                        date = ?, 26                        category = ?, 27                        content = ? 28                    WHERE link = ?29                �title�date�category�content�z�30                    INSERT INTO feedbacks (title, link, date, category, content)31                    VALUES (?, ?, ?, ?, ?)32                Tu插入反馈意见失败: NF)	rr�fetchone�getrrrr�rollback)r33�feedback�existingrs&&  r�insert_feedback� FeedbackDatabase.insert_feedback)s
��	��K�K��� I�H�U[�L\�K^�_��{�{�+�+�-�H�����#�#�%��w�'��&�)9�8�J�;O��l�l�9�b�1�8�F�3C�
E
�F
����#�#�%��w�'��&�)9�8�F�;K��z�*�H�L�L��B�,G�
I
�J
�34
�I�I�������	��.�q�c�2�3��I�I��� ���	�s�AC?�B5C?�?D=�35(D8�8D=c���^pV
F%pVP
V4
'gKV^
,
pK'	\R
VR\V
4
V,36R24
V# \dp\RT24
Rp?^#Rp?i
i;i
)u批量插入反馈意见u批量插入完成，成功 u 条，失败 � 条u批量插入失败: N)r%r�lenr)r37�	feedbacks�countr#rs&&   r�batch_insert�FeedbackDatabase.batch_insertHs~��		��E�%���'�'��1�1��Q�J�E�&�
�0���~�c�)�n�W\�F\�E]�]a�b�c��L���	��(���,�-���	�s�A�0A�A7�A2�2A7c���VPPR
V
3
4VPP4# \dp\	RT24
.uRp?#Rp?i
i;i
)u根据类别获取反馈意见z=SELECT * FROM feedbacks WHERE category = ? ORDER BY date DESC�查询失败: N�rr�fetchallrr)r38rrs&& r�get_feedbacks_by_category�*FeedbackDatabase.get_feedbacks_by_categoryUsW��	��K�K��� _�bj�al�m��;�;�'�'�)�)���	��N�1�#�&�'��I��	�s�69�A�A�
A�Ac���VPPR
RV
R2RV
R234VPP4# \dp\	RT24
.uRp?#Rp?i
i;i
)u!根据关键词搜索反馈意见z�39                SELECT * FROM feedbacks 40                WHERE title LIKE ? OR content LIKE ? 41                ORDER BY date DESC42            �
%u搜索失败: Nr0)r43�keywordrs&& r�search_feedbacks�!FeedbackDatabase.search_feedbacks^sq��		��K�K���!��g�Y�a�.�A�g�Y�a�.�1�	
3�44�;�;�'�'�)�)���	��N�1�#�&�'��I��	�s�?A�A'�
A"�
A'�"A'c
��VPPR
4
VPP4# \dp
\	RT
24
.uRp
?
#Rp
?
i
i;i
)u获取所有反馈意见z*SELECT * FROM feedbacks ORDER BY date DESCr/Nr0rs& r�get_all_feedbacks�"FeedbackDatabase.get_all_feedbacksksQ��	��K�K��� L�M��;�;�'�'�)�)���	��N�1�#�&�'��I��	�s�47�A�A�
A�Ac
���VPPR
4
VPP4p
V
'd45V
^,#^# \dp\	RT24
Rp?^#Rp?i
i;i
)u获取反馈意见总数zSELECT COUNT(*) FROM feedbacksr/N)rrr rr)r46�resultrs&  r�get_feedback_count�#FeedbackDatabase.get_feedback_countts_��	��K�K��� @�A��[�[�)�)�+�F� &�6�!�9�-�A�-���	��N�1�#�&�'���	�s"�<A47�A48�
A49�50A.�A)�)A.c
�x�VP'd(VPP4\R
4
R#R#)u关闭数据库连接u数据库连接已关闭N)r�closer)
r51s
&rrA�FeedbackDatabase.close~s'���9�9�9��I�I�O�O���,�-�rc�n
�\PPV
4
'g\R
V
24
^#\	V
RRR7;
_
uu_4p\52P!V4
pRRR4VPX4
# +'giL!;i
 \dp\RT24
Rp?^#Rp?i
i;i
)u从JSON文件导入数据u文件不存在: �
rzutf-8)
�encodingNu导入失败: )	�os�path�existsr�open�json�loadr,r)r53�	json_file�
f�datars&&   r�import_from_json�!FeedbackDatabase.import_from_json�s���	��7�7�>�>�)�,�,��)�)��5�6���i��w�7�7�1��y�y��|��8��$�$�T�*�*�8�7���	��N�1�#�&�'���	�s9�$B�B�B�
A=�$B�=B
	�B�B4�B/�/B4)rrrN)
zfeedback.db)�__name__�54__module__�__qualname__�__firstlineno__rr	r%r,r2r7r:r>rArO�__static_attributes__�__classdictcell__)
�
__classdict__s
@rrrs<�����4�:�>�����.�
�
rr�__main__zsecurities_feedbacks.jsonzfund_feedbacks.jsonzall_feedbacks.jsonu反馈意见总数: u监管u搜索 "监管" 结果: r(u前3条结果::N�Nu标题: u55, 日期: u56, 类别: )rrJrFrrQ�dbrOr>r+rr7�
search_resultr)�itemrA�rr�<module>r^
s��


���	�L
�L
�\�z��	�	�B����3�4����-�.����,�-�
�!�!�#�E�	� ���57(�)��'�'��1�M�	�$�S��%7�$8��58=�>��
���!�"�%�%�D��H�T�!�W�I�Z��Q��y�59�4�
�7�)�L�M�&��H�H�J�3r