python中sql安全的sql拼接

import datetime
from typing import Any, Dict

def build_order_query_sql(kwargs: Dict[str, Any]) -> tuple[str, list]:
    """
    安全构建订单查询SQL
    
    返回: (SQL字符串, 参数列表)
    """
    sql = "SELECT * FROM base_order WHERE 1=1"
    params = []
    
    # 定义允许查询的字段及其类型检查
    field_handlers = {
        'user_id': (str, "user_id = %s"),
        'account_number': (str, "account_number = %s"),
        'order_status': (int, "order_status = %s"),
        'start_time': (datetime.datetime, "order_createtime >= %s"),
        'end_time': (datetime.datetime, "order_createtime <= %s"),
        'order_source': (str, "order_source = %s"),
        'province_code': (str, "province_code = %s")
    }
    
    for key, value in kwargs.items():
        if key in field_handlers:
            expected_type, template = field_handlers[key]
            
            # 类型检查
            if not isinstance(value, expected_type):
                # 可以尝试转换，或者抛出错误
                try:
                    if expected_type == datetime.datetime:
                        # 如果传递的是字符串，尝试转换为datetime
                        if isinstance(value, str):
                            value = datetime.datetime.fromisoformat(value.replace('Z', '+00:00'))
                        elif isinstance(value, (int, float)):
                            value = datetime.datetime.fromtimestamp(value)
                    elif expected_type == int:
                        value = int(value)
                    elif expected_type == str:
                        value = str(value)
                except (ValueError, TypeError):
                    raise ValueError(f"参数 {key} 的类型应为 {expected_type.__name__}，实际为 {type(value).__name__}")
            
            sql += f" AND {template}"
            params.append(value)
    
    sql += " ORDER BY order_createtime DESC"
    
    return sql, params

# 使用示例
kwargs = {
    'user_id': '12345',
    'start_time': datetime.datetime(2024, 1, 1),
    'end_time': datetime.datetime(2024, 12, 31, 23, 59, 59),
    'order_status': 1
}

sql, params = build_order_query_sql(kwargs)
logger.debug(f"查询参数: {kwargs}，SQL: {sql}，参数值: {params}")

# 执行查询
cursor.execute(sql, params)