如何在Python中用命名参数占位符为变量加%通配符实现SQL LIKE查询

Got it, I see where the issue is—you're not adding the % wildcards to your search value before passing it to the parameterized query. Right now, your code is doing an exact match instead of the "contains" match you want (like %this%).

Here's the correct implementation:

variable = request.form.get("input")
# Wrap the input value with % wildcards for partial matching
search_term = f"%{variable}%"
results = db.execute("SELECT * FROM table WHERE column LIKE :placeholder", {"placeholder": search_term}).fetchall()

Why this works: #

• Parameterized queries (using :placeholder here) are the safe way to handle user input, as they automatically escape special characters to prevent SQL injection.
• By adding the % signs to the value instead of hardcoding them in the SQL string, you keep the query clean and secure.

Optional: Handling special wildcard characters #

If your users might input characters that have special meaning in SQL LIKE clauses (like % or _), you can escape them first to avoid unexpected matches. For example:

def escape_like_special_chars(s):
    # Escape % and _ by prefixing them with a backslash
    return s.replace("%", "\\%").replace("_", "\\_")

variable = request.form.get("input")
escaped_variable = escape_like_special_chars(variable)
search_term = f"%{escaped_variable}%"
results = db.execute("SELECT * FROM table WHERE column LIKE :placeholder ESCAPE '\\'", {"placeholder": search_term}).fetchall()

Note that you need to add the ESCAPE '\\' clause to your SQL statement so the database knows to treat the backslash as an escape character.

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