LIKE – Can't use the %%s% wildcard with MySQL connector

I’m new to mysql.connector. I’m trying to query my database using the LIKE wildcard. When saving users, we ask them for the languages they speak and make a string like this: "en+ru", users aren’t limited to 2 languages. When querying, I’m using 1 language i.e: "en" or "ru"

I would like to find users by location and what language they speak.

This is the function I use to query the database:

    def get_users_where_location_langs(self, location, langs):         get_users_formula = "SELECT * FROM users_cert WHERE location = %s AND langs LIKE %%s%"         self.cursor.execute(get_cert_formula, (location, langs))         return self.cursor.fetchall() 

When executing this function, I recieve the following error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to y our MySQL server version for the right syntax to use near '%'en'%' at line 1 

How would I solve this problem?

Here are some things that I’ve already tried:

1:

    def get_users_where_location_langs(self, location, langs):         get_cert_formula = "SELECT * FROM users_cert WHERE location = %s AND langs LIKE %{}%".format(langs)         self.cursor.execute(get_cert_formula, (location, langs))         return self.cursor.fetchall() 

I get the following error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to y our MySQL server version for the right syntax to use near '%en%' at line 1 

2: replacing the %%s% with *%s*. I got the same error as the first.

3:

get_cert_formula = "SELECT * FROM haverim_cert WHERE location = %s AND langs LIKE %'%s'%" 

Error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%''en''%' at line 1 

4:

get_cert_formula = "SELECT * FROM haverim_cert WHERE location = %s AND langs LIKE %(%s)%" 

Error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%('en')%' at line 1 

5:

get_cert_formula = "SELECT * FROM haverim_cert WHERE location = %s AND langs LIKE ('%',%s,'%')" 

Error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 

6:

get_cert_formula = "SELECT * FROM haverim_cert WHERE location = %s AND langs LIKE '%%s%'" 

Error:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'en'%'' at line 1 

Thank you for your help.

Add Comment
1 Answer(s)

Use ? or %s placeholders and concatenate the % wildcards:

get_users_formula = "SELECT * FROM users_cert WHERE location = ? AND langs LIKE CONCAT('%', ?, '%')" 

or:

get_users_formula = "SELECT * FROM users_cert WHERE location = %s AND langs LIKE CONCAT('%', %s, '%')" 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.