CODEWITHSUNDEEP
pythonmysqlsqlmysql-python
Mario
Mario

Reputation: 1488

Use %%s% in a SQL statement of a MySQLdb cursor

I want to select data from my database where the COLUMN_NAME value contains a string, an example is below:

SELECT * FROM mytable WHERE LOWER(COLUMN_NAME) LIKE LOWER('%foobar%')

The above SQL statement should select from the table mytable where COLUMN_NAME contains foobar (incase sensitive)

However foobar was an example and I'm not sure how to implement this in a Python MySQLdb query while escaping the value which will be put instead of foobar

I have tried the following but it seems their syntax is incorrect:

value = "foobar"
cursor.execute("SELECT * FROM mytable WHERE LOWER(COLUMN_NAME) LIKE LOWER('%%s%')", (value,))

value = "foobar"
cursor.execute("SELECT * FROM mytable WHERE LOWER(COLUMN_NAME) LIKE LOWER(%%s%)", (value,))

value = "foobar"
cursor.execute("SELECT * FROM mytable WHERE LOWER(COLUMN_NAME) LIKE LOWER('% %s %')", (value,))

Any help on how to get it working?

Upvotes: 0

Views: 360

Answers (2)

Sven Eberth
Sven Eberth

Reputation: 3115

To be honest, I have not tested this with MySQLdb, but usually you can escape the `%` in format-strings by doubling it.

Therefor

value = "foobar"
cursor.execute("SELECT * FROM mytable WHERE LOWER(COLUMN_NAME) LIKE LOWER('%%%s%%')", (value,))

should work.

UPDATE:

My proposed solution doesn't work, because the query is prepared with mogrify() which uses literal().
literal always adds the single quotes '. So the query becomes LOWER('%'foobar'%') and is invalid.

Please Consider Parfait's solution.

Upvotes: 2

Parfait
Parfait

Reputation: 107587

For parameterized queries that use the percent symbol, % , parameter value should include the wildcards which you can interpolate with F-string:

# VALUE WITH WILDCARDS
value = "foobar"
param = f"%{value}%"

# PREPARED STATEMENT WITHOUT QUOTES
sql = "SELECT * FROM mytable WHERE LOWER(COLUMN_NAME) LIKE LOWER(%s)"

# EXECUTE AND BIND PARAM
cursor.execute(sql, (param,))

Upvotes: 2

Related Questions