pymysql查询

import pymysql

class DatabaseChecker:
    def __init__(self, host, user, password, database, port=3306, charset='utf8mb4'):
        """
        初始化数据库连接信息。
        """
        self.db_config = {
            "host": host,
            "user": user,
            "password": password,
            "database": database,
            "port": port,
            "charset": charset,
        }
    
    def _connect(self):
        """
        创建数据库连接。
        """
        return pymysql.connect(**self.db_config)
    
    def check_elements(self, table_name, column_name, elements):
        """
        检查给定的元组元素是否存在于指定表的指定字段中。
        
        :param table_name: 数据库表名
        :param column_name: 要检查的字段名
        :param elements: 要检查的元素元组
        :return: 一个包含存在元素和不存在元素的字典
        """
        query = f"SELECT {column_name} FROM {table_name} WHERE {column_name} IN %s"

        with self._connect() as connection:
            with connection.cursor() as cursor:
                cursor.execute(query, (elements,))
                existing_elements = {row[0] for row in cursor.fetchall()}

        non_existing_elements = set(elements) - existing_elements

        return {
            "存在的元素": existing_elements,
            "不存在的元素": non_existing_elements
        }

# 使用示例
if __name__ == "__main__":
    db_checker = DatabaseChecker(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database'
    )

    my_tuple = ('黄艳', '方雷', '明刚', '张强')
    result = db_checker.check_elements('book_author_boss', 'name', my_tuple)
    
    print("存在的元素:", result["存在的元素"])
    print("不存在的元素:", result["不存在的元素"])

import pymysql

# 配置数据库连接信息
connection = pymysql.connect(
    host= '',  # 数据库主机名或IP地址
    user='',  # 数据库用户名
    password='',  # 数据库密码
    database= '',  # 数据库名称
    port=3306,  # 数据库端口，默认MySQL为3306
    charset='utf8mb4',  # 可选参数，设置字符集
)

# 定义要检查的元组
my_tuple = ('黄艳', '方雷', '明刚', '张z')

try:
    # 创建一个游标对象
    with connection.cursor() as cursor:
        # 构建 SQL 查询，使用 IN 关键字来查找存在的元素
        sql_exist = "SELECT name FROM book_author_boss WHERE name IN %s AND industry='EDU'"
        cursor.execute(sql_exist, (my_tuple,))

        # 获取存在的元素
        existing_elements = cursor.fetchall()
        existing_elements = {row[0] for row in existing_elements}  # 转换为集合以便后续比较

        # 找出不存在的元素
        non_existing_elements = set(my_tuple) - existing_elements

        print("存在的元素:", existing_elements)
        print("不存在的元素:", non_existing_elements)

finally:
    # 关闭数据库连接
    connection.close()

import pymysql

class DatabaseChecker:
    def __init__(self, host, user, password, database, port=3306, charset='utf8mb4'):
        """
        初始化数据库连接信息。
        """
        self.connection = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            port=port,
            charset=charset
        )
    
    def check_elements(self, table_name, column_name, elements):
        """
        检查给定的元组元素是否存在于指定表的指定字段中。
        
        :param table_name: 数据库表名
        :param column_name: 要检查的字段名
        :param elements: 要检查的元素元组
        :return: 一个包含存在元素和不存在元素的字典
        """
        try:
            with self.connection.cursor() as cursor:
                # 构建 SQL 查询
                sql_query = f"SELECT {column_name} FROM {table_name} WHERE {column_name} IN %s"
                cursor.execute(sql_query, (elements,))

                # 获取存在的元素
                existing_elements = cursor.fetchall()
                existing_elements = {row[0] for row in existing_elements}

                # 找出不存在的元素
                non_existing_elements = set(elements) - existing_elements

                return {
                    "存在的元素": existing_elements,
                    "不存在的元素": non_existing_elements
                }
        finally:
            self.connection.close()

# 使用示例
if __name__ == "__main__":
    db_checker = DatabaseChecker(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database'
    )

    my_tuple = ('黄艳', '方雷', '明刚', '张强')
    result = db_checker.check_elements('book_author_boss', 'name', my_tuple)
    
    print("存在的元素:", result["存在的元素"])
    print("不存在的元素:", result["不存在的元素"])

SELECT {column_name} FROM {self.db_table} WHERE qqType = 2 and endTime >= CURRENT_DATE and {column_name} IN %s

import pymysql

class DatabaseChecker:
    def __init__(self, host, user, password, database, port=3306, charset='utf8mb4'):
        self.db_config = {
            "host": host,
            "user": user,
            "password": password,
            "database": database,
            "port": port,
            "charset": charset,
        }
    
    def _connect(self):
        return pymysql.connect(**self.db_config)
    
    def check_elements(self, table_name, column_name, elements):
        # 查询大于等于当前日期的记录
        query_greater_equal = (
            f"SELECT {column_name} FROM {table_name} "
            f"WHERE qqType = 2 AND endTime >= CURRENT_DATE AND {column_name} IN %s"
        )
        
        # 查询小于当前日期的记录
        query_less = (
            f"SELECT {column_name} FROM {table_name} "
            f"WHERE qqType = 2 AND endTime < CURRENT_DATE AND {column_name} IN %s"
        )

        with self._connect() as connection:
            with connection.cursor() as cursor:
                # 获取大于等于当前日期的记录
                cursor.execute(query_greater_equal, (elements,))
                greater_equal_elements = {row[0] for row in cursor.fetchall()}
                
                # 获取小于当前日期的记录
                cursor.execute(query_less, (elements,))
                less_elements = {row[0] for row in cursor.fetchall()}

        non_existing_elements = set(elements) - (greater_equal_elements | less_elements)

        return {
            "大于等于结束时间的元素": greater_equal_elements,
            "小于结束时间的元素": less_elements,
            "不存在的元素": non_existing_elements
        }

# 使用示例
if __name__ == "__main__":
    db_checker = DatabaseChecker(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database'
    )

    my_tuple = ('黄艳', '方雷', '明刚', '张强')
    result = db_checker.check_elements('book_author_boss', 'name', my_tuple)
    
    print("大于等于结束时间的元素:", result["大于等于结束时间的元素"])
    print("小于结束时间的元素:", result["小于结束时间的元素"])
    print("不存在的元素:", result["不存在的元素"])