469,352 Members | 2,163 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

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

Jul 18 '05 #1
5 19187
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


Jul 18 '05 #2
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

Jul 18 '05 #3
> >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


Jul 18 '05 #4
Dave Harrison <da**@nullcube.com> wrote in message news:<ma**********************************@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?
Jul 18 '05 #5
Dave Harrison <da**@nullcube.com> wrote in message news:<ma**********************************@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
Jul 18 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Peter Nikolaidis | last post: by
1 post views Thread by Derek Fountain | last post: by
reply views Thread by Stephen Roderick | last post: by
21 posts views Thread by John Fabiani | last post: by
2 posts views Thread by ws Wang | last post: by
4 posts views Thread by fedor | last post: by
1 post views Thread by Yi Xing | last post: by
reply views Thread by jgarber | last post: by
reply views Thread by Steve Holden | last post: by
2 posts views Thread by Nikhil | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.