如何在Python的MySQL LIKE查询中正确传入参数？

你遇到的问题很典型——MySQL的LIKE查询需要通配符%来实现模糊匹配，但直接用参数占位符时没带上它，导致只能精确匹配；而如果写法不对还会触发ProgrammingError，这是因为MySQLdb用%作为参数占位符，和LIKE的通配符冲突了。下面给你两种安全又有效的解决方式：

方法1：在Python参数中添加通配符% #

直接在你的字典d里，给需要模糊匹配的字段值前后加上%，SQL语句保持原样即可。这样既符合参数化查询的规范，又能避免SQL注入风险：

import MySQLdb
import MySQLdb.cursors

cnx = MySQLdb.connect(host = 'localhost', database = 'cb-dev', user = 'root', password = 'root', compress = 1, cursorclass=MySQLdb.cursors.DictCursor)

# 给需要模糊匹配的字段添加通配符%
d= {
    "Date": "2017-09-26",
    "Title": "%software%",  # 前后加%实现模糊匹配
    "Location1": "%USA%",
    "Location2": "%United States%",
    "Location3": "%United States of America%",
    "Competency_user": "App\\Job",
    "Comp1_ID": 482,
    "Comp2_ID": 483, 
    "Comp3_ID": 484,
    "Day_rate_lower": 300,
    "Day_rate_upper": 500,
    "hourly_rate_lower": 30,
    "hourly_rate_upper": 50 
}

# SQL语句保持原来的LIKE %(字段名)s写法
query3 = """SELECT id FROM jobs WHERE Date(jobs.closing_date) >= %(Date)s AND title LIKE %(Title)s AND ( location_name_list LIKE %(Location1)s OR location_name_list LIKE %(Location2)s OR location_name_list LIKE %(Location3)s) AND ( EXISTS (SELECT * FROM competencies INNER JOIN competency_maps ON competencies.id = competency_maps.competency_id WHERE jobs.id = competency_maps.competency_mappable_id AND competency_maps.competency_mappable_type = %(Competency_user)s AND competency_id IN ( %(Comp1_ID)s, %(Comp2_ID)s, %(Comp3_ID)s )) OR ( job_rate BETWEEN %(Day_rate_lower)s AND %(Day_rate_upper)s AND job_type = "day rate" ) OR ( job_rate BETWEEN %(hourly_rate_lower)s AND %(hourly_rate_upper)s AND job_type = "contract rate" ))"""

cursor = cnx.cursor()
cursor.execute(query3,d)
sorted_job_Id = cursor.fetchall()
print(sorted_job_Id)

方法2：在SQL中用CONCAT函数拼接通配符 #

如果不想修改Python的参数字典，可以直接在SQL语句里用MySQL的CONCAT函数把%和参数拼接起来，这样也能避开占位符和通配符的冲突：

# 字典d保持原样，不需要手动加%
d= {
    "Date": "2017-09-26",
    "Title": "software",
    "Location1": "USA",
    "Location2": "United States",
    "Location3": "United States of America",
    "Competency_user": "App\\Job",
    "Comp1_ID": 482,
    "Comp2_ID": 483, 
    "Comp3_ID": 484,
    "Day_rate_lower": 300,
    "Day_rate_upper": 500,
    "hourly_rate_lower": 30,
    "hourly_rate_upper": 50 
}

# 修改LIKE部分为CONCAT('%', %(字段名)s, '%')，用MySQL函数拼接通配符
query3 = """SELECT id FROM jobs WHERE Date(jobs.closing_date) >= %(Date)s AND title LIKE CONCAT('%%', %(Title)s, '%%') AND ( location_name_list LIKE CONCAT('%%', %(Location1)s, '%%') OR location_name_list LIKE CONCAT('%%', %(Location2)s, '%%') OR location_name_list LIKE CONCAT('%%', %(Location3)s, '%%')) AND ( EXISTS (SELECT * FROM competencies INNER JOIN competency_maps ON competencies.id = competency_maps.competency_id WHERE jobs.id = competency_maps.competency_mappable_id AND competency_maps.competency_mappable_type = %(Competency_user)s AND competency_id IN ( %(Comp1_ID)s, %(Comp2_ID)s, %(Comp3_ID)s )) OR ( job_rate BETWEEN %(Day_rate_lower)s AND %(Day_rate_upper)s AND job_type = "day rate" ) OR ( job_rate BETWEEN %(hourly_rate_lower)s AND %(hourly_rate_upper)s AND job_type = "contract rate" ))"""

cursor = cnx.cursor()
cursor.execute(query3,d)
sorted_job_Id = cursor.fetchall()
print(sorted_job_Id)

为什么之前会报错？ #

如果你尝试直接在SQL里写LIKE '%%(Title)s%%'，MySQLdb会把%%解析成单个%，但这种写法很容易导致参数数量和占位符不匹配，触发ProgrammingError: not enough arguments for format string。上面两种方法都能避免这个问题，而且全程用参数化查询，不会有SQL注入的风险。

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