Snowflake SQL在Python SQLAlchemy中LIKE操作符执行异常，如何传%？

错误原因分析 #

• HTML转义字符错误：Python脚本中length(RESPONSE_VALUE)<10里的<是HTML的小于号转义符，SQL无法识别，应直接使用<。
• 字符串拼接SQL的风险：用"""+变量+"""的方式拼接SQL，极易因引号、特殊字符导致语法错误，还存在严重的SQL注入风险，这是语句无法被识别的核心诱因之一。
• 注：%作为SQL LIKE的通配符，在Python字符串中直接书写本身无语法错误，你的脚本里这部分写法没问题，问题出在其他环节。

正确实现方式 #

推荐：使用参数化查询（最佳实践） #

无论使用snowflake-connector-python还是其他Snowflake Python驱动，都支持参数化查询，通过占位符传递变量，彻底避免拼接错误和注入风险：

import snowflake.connector

try:
    start_date = input("Start_date_epoch:")
    end_date = input("End_date_epoch:")
    type_service = '["sb-dsp"]'
    # 定义带占位符的SQL，用%s作为参数占位符（snowflake-connector标准写法）
    query_sb_dsp_positive = """
        SELECT COUNTRY, RESPONSE_VALUE, LATITUDE, STOREID, ZIPCODE, REQUESTED_SERVICE,
               TENANTID, LONGITUDE, CITY, STATE, RESPONSE_ERRORS, CREATEDAT
        FROM HEVOPROD_DB.HEVOPROD_SCH.PRDNEW_METERING
        WHERE latitude IS NOT NULL 
          AND longitude IS NOT NULL 
          AND storeid IS NOT NULL 
          AND TENANTID IS NOT NULL 
          AND COUNTRY IS NOT NULL 
          AND CITY IS NOT NULL 
          AND STATE IS NOT NULL 
          AND CREATEDAT BETWEEN %s AND %s
          AND REQUESTED_SERVICE = %s
          AND LENGTH(RESPONSE_VALUE) < 10
          AND RESPONSE_ERRORS LIKE '%"message":%'
          AND RESPONSE_ERRORS LIKE '%"storeExternalId":%'
          AND RESPONSE_ERRORS LIKE '%"provider":%'
          AND RESPONSE_ERRORS LIKE '%"type":%'
        LIMIT 50;
    """
    # 建立连接后执行查询，传递参数元组
    conn = snowflake.connector.connect(
        user='你的用户名',
        password='你的密码',
        account='你的账号信息'
    )
    cursor = conn.cursor()
    cursor.execute(query_sb_dsp_positive, (start_date, end_date, type_service))
    results = cursor.fetchall()
finally:
    # 关闭连接
    if 'conn' in locals():
        conn.close()

不推荐：修正字符串拼接写法（仅作参考） #

如果一定要用字符串拼接，需先修正转义错误，同时确保变量内容不会破坏SQL语法：

try:
    start_date = input("Start_date_epoch:")
    end_date = input("End_date_epoch:")
    type_service = '["sb-dsp"]'

    query_sb_dsp_positive = f"""
        SELECT COUNTRY, RESPONSE_VALUE, LATITUDE, STOREID, ZIPCODE, REQUESTED_SERVICE,
               TENANTID, LONGITUDE, CITY, STATE, RESPONSE_ERRORS, CREATEDAT
        FROM HEVOPROD_DB.HEVOPROD_SCH.PRDNEW_METERING
        WHERE latitude IS NOT NULL 
          AND longitude IS NOT NULL 
          AND storeid IS NOT NULL 
          AND TENANTID IS NOT NULL 
          AND COUNTRY IS NOT NULL 
          AND CITY IS NOT NULL 
          AND STATE IS NOT NULL 
          AND CREATEDAT BETWEEN '{start_date}' AND '{end_date}'
          AND REQUESTED_SERVICE='{type_service}'
          AND LENGTH(RESPONSE_VALUE) < 10
          AND RESPONSE_ERRORS LIKE '%"message":%'
          AND RESPONSE_ERRORS LIKE '%"storeExternalId":%'
          AND RESPONSE_ERRORS LIKE '%"provider":%'
          AND RESPONSE_ERRORS LIKE '%"type":%'
        LIMIT 50;
    """
    # 后续执行逻辑...

内容的提问来源于stack exchange，提问作者lifo