using like and % in MySQLdb
 

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

Re: using like and % in MySQLdb
 

Dave Harrison schrieb:
> 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.
> however this :
>
> self.curs.execute(
> """SELECT * FROM user WHERE login LIKE '%dave%'"""
> )
>
> does work
>
> so what's the go ?
> cheers
> Dave
>

Re: using like and % in MySQLdb
 

Torsten Marek wrote:
> Dave Harrison schrieb:
>
>> 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

Re: using like and % in MySQLdb
 

> >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

> >however this :
> >
> >self.curs.execute(
> > """SELECT * FROM user WHERE login LIKE '%dave%'"""
> > )
> >
> >does work
> >
> >so what's the go ?
> >cheers
> >Dave
> >
>
> --
> http://mail.python.org/mailman/listinfo/python-list

Re: using like and % in MySQLdb
 

Dave Harrison <dave@nullcube.com> wrote in message news:<mailman.1060232532.23485.python-list@python.org>...
> 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?

Re: using like and % in MySQLdb
 

Dave Harrison <dave@nullcube.com> wrote in message news:<mailman.1060232532.23485.python-list@python.org>...
> 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