MySQL Python LIKE wildcard

Question

I want to do a Python MySQL query such as:

cursor = connection.cursor()
sql = "select text \
       from steps \
       where text like '%start%'"
cursor.execute(sql)

But the % isn't seen as a wildcard, it's expecting a variable, I guess. I get an error:

TypeError: not enough arguments for format string

Nothing seems to work. It would also be nice if I could use a variable instead of '%start%'

soulcheck · Accepted Answer · 2012-01-08 09:59:37Z

25

I assume you're using python db-api.

You can try escaping % as %%.

As for passing the parameters there is a number of ways, for example:

cursor = connection.cursor()
sql = """select text 
   from steps 
   where text like %s"""
cursor.execute(sql, (('%' + 'start' + '%',))

You can see examples of the other ways of parameter passing on this blog in "Interface modułu" section. Not all of them work with all connectors.

edited Jan 8, 2012 at 9:59

answered Jan 8, 2012 at 5:20

soulcheck

36.9k6 gold badges95 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user984003 Over a year ago

Escaping with %% works, thanks :) Use of literal strings gave the same error.

soulcheck Over a year ago

@user984003 strange, but i can't even test it right now. glad it helped anyway.

Dietrich Epp Over a year ago

@soulcheck: String syntax has nothing to do with interpretation of % (unlike how Bourne Shell or Ruby interpret $, for example). So """%start%""" is the exact same as "%start%", which is the same as '%start%' and r'%start%', etc.

user984003 Over a year ago

Passing the parameters didn't work. I don't get an error, but it finds 0 lines, making me think that it's not seeing the wildcards. This worked: sql = "select text from cmt_steps where text like '%%"+start+"%%'"

user3072843 Over a year ago

The string syntax made the difference: """SELECT * FROM courses WHERE title like %s""" works.

Collectives™ on Stack Overflow

MySQL Python LIKE wildcard

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related