mysql 接口 不定数量参数的写法 **kwargs字典 for取值拼接sql

2023-12-20 17:50:53
@classmethod
def query_by_conditions(cls, cursor=None, table_name=None, limit=100, sort_by='id', sort_order='ASC', **kwargs):
    # 构建基础查询语句
    query = "SELECT * FROM `{}`".format(table_name)

    # 存储过滤条件
    conditions = []
    values = []

    # 遍历kwargs字典,添加过滤条件
    for key, value in kwargs.items():
        if value is not None:
            # 对于subType特殊处理,支持列表类型的值
            if key == 'subType' and isinstance(value, list):
                sub_conditions = []
                for val in value:
                    sub_conditions.append(f"{key} = %s")
                    values.append(val)
                conditions.append(f"({ ' OR '.join(sub_conditions) })")
            else:
                conditions.append(f"{key} = %s")
                values.append(value)

    # 如果有过滤条件,将它们添加到查询语句中
    if conditions:
        query += " WHERE " + " AND ".join(conditions)

    # 添加排序和限制条件
    query += f" ORDER BY {sort_by} {sort_order} LIMIT %s"
    values.append(limit)

    # 执行查询
    cursor.execute(query, values)
    results = cursor.fetchall()

    # 获取字段名
    fields = [field[0] for field in cursor.description]

    # 将查询结果转换为带有字段名的字典列表
    results_with_fields = [dict(zip(fields, row)) for row in results]

    return results_with_fields

1 指定字段多条件 or 判断取值 拼接成

(subType = %s OR subType = %s)?

2 整体 字符串 拼接成query

SELECT * FROM `ok_bills` WHERE type = %s AND (subType = %s OR subType = %s) ORDER BY id ASC LIMIT %s?

3?cursor.execute(query, values)?

query = SELECT * FROM `ok_bills` WHERE type = %s AND (subType = %s OR subType = %s) ORDER BY id ASC LIMIT %s

values = ['2', 1, 2, 6490]

文章来源:https://blog.csdn.net/tenc1239/article/details/135111037
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。