import sqlite3


class CLDatabase:
    def __init__(self, db_name='cl.db'):
        self.db_name = db_name
        self.conn = None
        self.cursor = None

    def connect(self):
        """连接到 SQLite 数据库"""
        self.conn = sqlite3.connect(self.db_name)
        self.cursor = self.conn.cursor()

    def fetch_all_data(self):
        """查询表中的所有数据"""
        self.cursor.execute("SELECT * FROM CL")
        rows = self.cursor.fetchall()
        print(f'\n\n数据库共有{len(rows)}条数据')
        return rows

    def filter_by_title(self, filter_list):
        """
        根据 filter_list 中的元素模糊匹配 title 字段，并打印匹配的结果
        :param filter_list: 包含需要匹配的关键词的列表
        """
        if not filter_list:
            print("filter_list 为空，未进行匹配。")
            return

        # 构建 SQL 的 WHERE 条件
        like_conditions = " OR ".join(["Title LIKE ?"] * len(filter_list))
        query = f"SELECT * FROM CL WHERE {like_conditions}"

        # 构建参数列表，每个关键词前后加上 % 用于模糊匹配
        params = [f'%{keyword}%' for keyword in filter_list]

        # 执行查询
        self.cursor.execute(query, params)
        matched_rows = self.cursor.fetchall()

        # 打印匹配的结果
        if matched_rows:
            print("\n匹配到的结果：")
            for row in matched_rows:
                print(
                    f"ID: {row[0]}, Tag: {row[5]}, Full_URL: {row[2]}, Title: {row[4]}")
            print(f"匹配到的总行数: {len(matched_rows)}")
        else:
            print("没有匹配到任何结果。")

    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()


# 使用示例
if __name__ == "__main__":
    filter_list = ['']
    db = CLDatabase()
    db.connect()
    db.filter_by_title(filter_list)
    db.fetch_all_data()
    db.close()