How to Escape Percent Signs (%) in MySQL Queries Using Python?

How to Escape % Characters in MySQL Queries Using Python

When constructing MySQL queries in Python, it's necessary to escape certain characters, including the percent sign (%). Failure to do so can lead to errors.

Consider the following query:

query = """SELECT DATE_FORMAT(date_time,'%Y-%m') AS dd
FROM some_table
WHERE some_col = %s
AND other_col = %s;"""

cur.execute(query, (pram1, pram2))

Executing this query will result in a "ValueError: unsupported format character 'Y'" exception. This occurs because MySQL interprets the % character as a format specifier within the DATE_FORMAT function, instead of treating it as a literal character.

To resolve this issue, it's recommended to use literal escaping as suggested by the MySQL Connector/Python documentation:

literal% = '%%'  # Define a literal percent string
query = """SELECT DATE_FORMAT(date_time,'{}') AS dd
FROM some_table
WHERE some_col = %s
AND other_col = %s;""".format(literal%)

cur.execute(query, (pram1, pram2))

By escaping the percent character with another percent sign, the query will be executed correctly without being affected by the format specifier interpretation. This approach ensures that the percent sign is treated as a literal character within the query.

The above is the detailed content of How to Escape Percent Signs (%) in MySQL Queries Using Python?. For more information, please follow other related articles on the PHP Chinese website!