using like and % in MySQLdb

[Dave Harrison]

Im sure this is a really obvious problem but :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

will not work ... gives me an "unsupported format character ''' (0x27)"

escaping the %'s with % as the doco recommends wont work either.

however this :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%dave%'"""
)

does work

so what's the go ?
cheers
Dave

 

[Torsten Marek]

Im sure this is a really obvious problem but :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

will not work ... gives me an "unsupported format character ''' (0x27)"

escaping the %'s with % as the doco recommends wont work either.

Why don't you do
self.curs.execute(
"""SELECT * FROM user WHERE login LIKE %s""", ("%"+login+"%", ))

The problem with escaping the % characters is, that MySQLdb converts
self.curs.execute("""SELECT * FROM user WHERE login LIKE '%%%s%%'""" ,
(login,))
to
"SELECT * FROM user WHERE login LIKE '%'dave'%'"
and I don't know how to prevent this.

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

Im sure this is a really obvious problem but :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

will not work ... gives me an "unsupported format character ''' (0x27)"

escaping the %'s with % as the doco recommends wont work either.

Why don't you do
self.curs.execute(
"""SELECT * FROM user WHERE login LIKE %s""", ("%"+login+"%", ))
[...]

You're right. Please ignore my previous post in this thread. It
correctly shows how to escape percent signs, but won't work in the SQL
context.

-- Gerhard

 

[Dave Harrison]

Im sure this is a really obvious problem but :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

will not work ... gives me an "unsupported format character ''' (0x27)"

escaping the %'s with % as the doco recommends wont work either.

Why don't you do
self.curs.execute(
"""SELECT * FROM user WHERE login LIKE %s""", ("%"+login+"%", ))

The problem with escaping the % characters is, that MySQLdb converts
self.curs.execute("""SELECT * FROM user WHERE login LIKE '%%%s%%'""" ,
(login,))
to
"SELECT * FROM user WHERE login LIKE '%'dave'%'"
and I don't know how to prevent this.

hehe yeah Id been testing what I was doing against my mysql client and couldnt work out why the module kept adding the '', you're solution works a treat, thanks torsten

 

[Chris]

Im sure this is a really obvious problem but :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

will not work ... gives me an "unsupported format character ''' (0x27)"

escaping the %'s with % as the doco recommends wont work either.

however this :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%dave%'"""
)

does work

so what's the go ?

Try

"select * from user where login like %s" % ('%%%s%%' % login)

(same as "select * from user where login like %s" % ('%dave%'))

I think you want the % stuff for LIKE to be part of the substituted in
string, not part of the query string. Make sense?

 

[Chris]

Im sure this is a really obvious problem but :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

will not work ... gives me an "unsupported format character ''' (0x27)"

escaping the %'s with % as the doco recommends wont work either.

however this :

self.curs.execute(
"""SELECT * FROM user WHERE login LIKE '%dave%'"""
)

does work

so what's the go ?
cheers
Dave

I just posted a reply and realized I made one mistake. I said to use

"select * from user where login like %s" % ('%%%s%%' % login)

But it should be like

"select * from user where login like %s", ('%%%s%%' % login)

as in

self.curs.execute("select * from user where login like %s", ('%%%s%%' % login))

That should work.

-Chris