Python案例怎么实现多条件查询？

wen python案例 2026-06-08 279

本文目录导读：

Python案例怎么实现多条件查询？

方法一：使用列表推导式（适合小数据集）
方法二：使用类封装（适合面向对象场景）
方法三：使用SQLite数据库（适合大量数据）
方法四：使用Pandas DataFrame（适合数据分析）
方法五：使用函数式编程（灵活组合）
总结与选择建议

我来介绍几种Python实现多条件查询的方法,从简单到复杂：

使用列表推导式（适合小数据集）

# 模拟数据
students = [
    {"name": "张三", "age": 20, "score": 85, "gender": "男"},
    {"name": "李四", "age": 22, "score": 92, "gender": "女"},
    {"name": "王五", "age": 19, "score": 78, "gender": "男"},
    {"name": "赵六", "age": 21, "score": 95, "gender": "女"},
    {"name": "孙七", "age": 23, "score": 88, "gender": "男"}
]
def query_students(students_data, **conditions):
    """
    多条件查询
    conditions: 条件字典，{"age__gt": 20, "score__gte": 85}
    """
    result = students_data.copy()
    for key, value in conditions.items():
        # 解析条件运算符
        if "__" in key:
            field, operator = key.split("__")
        else:
            field = key
            operator = "eq"
        # 根据运算符过滤
        if operator == "eq":
            result = [s for s in result if s.get(field) == value]
        elif operator == "gt":
            result = [s for s in result if s.get(field) > value]
        elif operator == "gte":
            result = [s for s in result if s.get(field) >= value]
        elif operator == "lt":
            result = [s for s in result if s.get(field) < value]
        elif operator == "lte":
            result = [s for s in result if s.get(field) <= value]
        elif operator == "ne":
            result = [s for s in result if s.get(field) != value]
        elif operator == "in":
            result = [s for s in result if s.get(field) in value]
        elif operator == "like":
            result = [s for s in result if value in str(s.get(field))]
    return result
# 使用示例
# 查询年龄大于20且分数大于等于85的学生
result = query_students(students, age__gt=20, score__gte=85)
print("查询结果:", result)
# 查询男生且分数在[80, 90]之间的学生
result = query_students(students, gender="男", score__in=range(80, 91))
print("查询结果:", result)

使用类封装（适合面向对象场景）

class QueryBuilder:
    def __init__(self, data):
        self.data = data
    def filter(self, **conditions):
        """链式过滤方法"""
        result = self.data.copy()
        for field, condition in conditions.items():
            if callable(condition):
                # 支持自定义过滤函数
                result = [item for item in result if condition(item.get(field))]
            elif isinstance(condition, dict):
                # 字典条件：{'>': 20, '<': 30}
                result = self._apply_dict_condition(result, field, condition)
            else:
                # 等值条件
                result = [item for item in result if item.get(field) == condition]
        return QueryBuilder(result)
    def _apply_dict_condition(self, data, field, conditions):
        result = data.copy()
        for operator, value in conditions.items():
            if operator == '>':
                result = [item for item in result if item.get(field, 0) > value]
            elif operator == '>=':
                result = [item for item in result if item.get(field, 0) >= value]
            elif operator == '<':
                result = [item for item in result if item.get(field, 0) < value]
            elif operator == '<=':
                result = [item for item in result if item.get(field, 0) <= value]
            elif operator == '!=':
                result = [item for item in result if item.get(field) != value]
            elif operator == 'in':
                result = [item for item in result if item.get(field) in value]
        return result
    def get(self):
        return self.data
    def __repr__(self):
        return f"QueryBuilder(data={self.data})"
# 使用示例
data = [
    {"id": 1, "name": "iPhone 13", "price": 5999, "stock": 100},
    {"id": 2, "name": "MacBook Pro", "price": 14999, "stock": 50},
    {"id": 3, "name": "iPad Pro", "price": 7999, "stock": 0},
    {"id": 4, "name": "AirPods Pro", "price": 1999, "stock": 200}
]
# 链式查询
qb = QueryBuilder(data)
result = qb.filter(
    price={"<": 10000, ">": 1000},
    stock=lambda x: x > 0
).get()
print("链式查询结果:", result)

使用SQLite数据库（适合大量数据）

import sqlite3
from datetime import datetime
class DatabaseQuery:
    def __init__(self, db_path):
        self.conn = sqlite3.connect(db_path)
        self.cursor = self.conn.cursor()
        self._create_tables()
    def _create_tables(self):
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                category TEXT,
                price REAL,
                stock INTEGER,
                created_date TEXT
            )
        ''')
        self.conn.commit()
    def insert_sample_data(self):
        """插入示例数据"""
        sample_data = [
            ("iPhone 13", "手机", 5999, 100, "2024-01-15"),
            ("MacBook Pro", "电脑", 14999, 50, "2024-02-20"),
            ("iPad Pro", "平板", 7999, 0, "2024-03-10"),
            ("AirPods Pro", "耳机", 1999, 200, "2024-01-25"),
            ("Apple Watch", "手表", 3999, 150, "2024-02-28"),
        ]
        self.cursor.executemany(
            "INSERT INTO products (name, category, price, stock, created_date) VALUES (?, ?, ?, ?, ?)",
            sample_data
        )
        self.conn.commit()
    def query_with_conditions(self, **kwargs):
        """
        多条件查询
        支持的参数: name, category, min_price, max_price, min_stock, min_date, max_date
        """
        query = "SELECT * FROM products WHERE 1=1"
        params = []
        if 'name' in kwargs:
            query += " AND name LIKE ?"
            params.append(f"%{kwargs['name']}%")
        if 'category' in kwargs:
            query += " AND category = ?"
            params.append(kwargs['category'])
        if 'min_price' in kwargs:
            query += " AND price >= ?"
            params.append(kwargs['min_price'])
        if 'max_price' in kwargs:
            query += " AND price <= ?"
            params.append(kwargs['max_price'])
        if 'min_stock' in kwargs:
            query += " AND stock >= ?"
            params.append(kwargs['min_stock'])
        if 'min_date' in kwargs:
            query += " AND created_date >= ?"
            params.append(kwargs['min_date'])
        if 'max_date' in kwargs:
            query += " AND created_date <= ?"
            params.append(kwargs['max_date'])
        # 排序
        if 'order_by' in kwargs:
            query += f" ORDER BY {kwargs['order_by']}"
        if 'limit' in kwargs:
            query += f" LIMIT {kwargs['limit']}"
        self.cursor.execute(query, params)
        return self.cursor.fetchall()
    def close(self):
        self.conn.close()
# 使用示例
db = DatabaseQuery(":memory:")
db.insert_sample_data()
# 多条件查询示例
result = db.query_with_conditions(
    category="手机",
    min_price=5000,
    max_price=8000,
    min_stock=50,
    order_by="price DESC"
)
print("数据库查询结果:", result)
db.close()

使用Pandas DataFrame（适合数据分析）

import pandas as pd
# 创建示例数据
data = {
    'name': ['张三', '李四', '王五', '赵六', '孙七'],
    'age': [20, 22, 19, 21, 23],
    'score': [85, 92, 78, 95, 88],
    'gender': ['男', '女', '男', '女', '男'],
    'city': ['北京', '上海', '广州', '深圳', '北京']
}
df = pd.DataFrame(data)
def advanced_query(df, **conditions):
    """
    Pandas高级多条件查询
    """
    mask = pd.Series([True] * len(df))
    for field, condition in conditions.items():
        if callable(condition):
            # 自定义函数条件
            mask = mask & df[field].apply(condition)
        elif isinstance(condition, tuple):
            # 范围条件 (min, max)
            if len(condition) == 2:
                mask = mask & (df[field] >= condition[0]) & (df[field] <= condition[1])
        elif isinstance(condition, (list, set)):
            # 列表条件
            mask = mask & df[field].isin(condition)
        else:
            # 等值条件
            mask = mask & (df[field] == condition)
    return df[mask]
# 使用示例
result = advanced_query(
    df,
    age=(18, 25),  # 年龄在18-25之间
    score=lambda x: x >= 85,  # 分数大于等于85
    city=['北京', '上海'],  # 城市是北京或上海
    gender='男'  # 性别男
)
print("Pandas查询结果:")
print(result)

使用函数式编程（灵活组合）

from typing import List, Dict, Any, Callable
class Condition:
    """条件类，支持逻辑运算"""
    def __init__(self, field: str, operator: str, value: Any):
        self.field = field
        self.operator = operator
        self.value = value
    def __call__(self, item: Dict) -> bool:
        field_value = item.get(self.field)
        operators = {
            '=': lambda a, b: a == b,
            '!=': lambda a, b: a != b,
            '>': lambda a, b: a > b,
            '>=': lambda a, b: a >= b,
            '<': lambda a, b: a < b,
            '<=': lambda a, b: a <= b,
            'in': lambda a, b: a in b,
            'contains': lambda a, b: b in str(a) if a else False,
        }
        return operators.get(self.operator, lambda a, b: False)(field_value, self.value)
    def __and__(self, other):
        return AndCondition(self, other)
    def __or__(self, other):
        return OrCondition(self, other)
class AndCondition:
    def __init__(self, *conditions):
        self.conditions = conditions
    def __call__(self, item):
        return all(cond(item) for cond in self.conditions)
    def __and__(self, other):
        return AndCondition(*self.conditions, other)
    def __or__(self, other):
        return OrCondition(*self.conditions, other)
class OrCondition:
    def __init__(self, *conditions):
        self.conditions = conditions
    def __call__(self, item):
        return any(cond(item) for cond in self.conditions)
    def __and__(self, other):
        return AndCondition(*self.conditions, other)
    def __or__(self, other):
        return OrCondition(*self.conditions, other)
# 使用示例
data = [
    {"product": "笔记本", "category": "电子", "price": 5000, "stock": 100},
    {"product": "手机", "category": "电子", "price": 3000, "stock": 0},
    {"product": "书籍", "category": "文化", "price": 50, "stock": 500},
    {"product": "耳机", "category": "电子", "price": 200, "stock": 200},
    {"product": "平板", "category": "电子", "price": 4000, "stock": 50},
]
# 构建复杂查询条件
condition1 = Condition("category", "=", "电子")
condition2 = Condition("price", ">", 100)
condition3 = Condition("stock", ">", 0)
condition4 = Condition("price", "<", 5000)
# 组合条件：电子类 且 价格>100 且 有库存 或 价格<5000
complex_condition = condition1 & condition2 & condition3 | condition4
# 执行查询
result = [item for item in data if complex_condition(item)]
print("函数式查询结果:", result)
# 更简洁的写法
def query(data: List[Dict], condition: Callable) -> List[Dict]:
    return list(filter(condition, data))
# 链式写法
result = query(data, (
    Condition("category", "=", "电子") &
    Condition("price", "in", [100, 5000]) &
    Condition("stock", ">", 0)
))
print("链式查询结果:", result)