473,570 Members | 2,967 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

using like and % in MySQLdb

Im sure this is a really obvious problem but :

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

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

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

however this :

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

does work

so what's the go ?
cheers
Dave

Jul 18 '05 #1
5 19752
Dave Harrison schrieb:
Im sure this is a really obvious problem but :

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

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

escaping the %'s with % as the doco recommends wont work either.
Why don't you do
self.curs.execu te(
"""SELECT * FROM user WHERE login LIKE %s""", ("%"+login+" %", ))

The problem with escaping the % characters is, that MySQLdb converts
self.curs.execu te("""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.execu te(
"""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.execu te(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

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

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

Why don't you do
self.curs.execu te(
"""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.exec ute(
"""SELECT * FROM user WHERE login LIKE '%%s%'""", [login]
)

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

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

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

The problem with escaping the % characters is, that MySQLdb converts
self.curs.execu te("""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.exec ute(
"""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******* *************** ************@py thon.org>...
Im sure this is a really obvious problem but :

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

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

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

however this :

self.curs.execu te(
"""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******* *************** ************@py thon.org>...
Im sure this is a really obvious problem but :

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

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

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

however this :

self.curs.execu te(
"""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.execu te("select * from user where login like %s", ('%%%s%%' % login))

That should work.

-Chris
Jul 18 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2578
by: Peter Nikolaidis | last post by:
Greetings, I am attempting to get MySQLdb 0.9.2 installed on Mac OS 10.2 with a Fink distribution of Python 2.2.2. I have seen only a few posts on the subject, some of them relate to "conflicting header files," but I don't know what to do about conflicting header files, or where I would find them, and once I found them, which ones to...
1
2991
by: Derek Fountain | last post by:
I was trying to use MySQLdb to connect to a database. All is OK, except I can't figure out how to get the details of an error. Suppose I try to connect to a non existant server, or with the wrong password - how do I get a meaningful error message which I can present to my user?
0
1479
by: Stephen Roderick | last post by:
Getting weird error, and I was wondering if anyone had any suggestions. My system appears to satisfy all MySQLdb prerequisities in terms of python, mysql, etc. Also, phpMyAdmin works fine with mysql, leading me to believe that my installation of MySQLdb is at fault. Any ideas? TIA For file test1.py (for appropriate values of user and...
21
5228
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All works as expected. But >>> numb=10 >>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb) Traceback (innermost last): File...
2
2182
by: ws Wang | last post by:
MySQLdb is working fine at command line, however when I tried to use it with mod_python, it give me a "server not initialized" error. This is working fine: ----------------------- testmy.py ------------------------------- #!/usr/bin/python import MySQLdb db = MySQLdb.connect(host="localhost", user="root", passwd="mypass", db="my_db")...
4
4359
by: fedor | last post by:
Hi all, I have a problem with mysql connections. After about 28000-29000 connections, I get a "Can't connect to MySQL server on '127.0.0.1'" error. I have made a small program which generates the error """ import MySQLdb
1
702
by: Yi Xing | last post by:
Hi, I met the following error when I tried to install MySQLdb. I had no problem installing numarray, Numeric, Rpy, etc. Does anyone know what's the problem? Thanks! running install running build running build_py creating build
0
1919
by: jgarber | last post by:
Hello, I just upgraded MySQLdb to the 1.2.0 version provided by Redhat Enterprise Linux ES4. At that point I began to get segfaults when importing twisted after MySQLdb, but not before. -- RedHat Enterprise Linux ES 4 (fully updated) Python 2.3.4 mysql-python (MySQLdb) version 1.2.0
0
314
by: Steve Holden | last post by:
Vaibhav.bhawsar wrote: imported The point here is that MySQLdb is a package, not a module. Some packages have their top-level __init__.py import the package's sub-modules or sub-packages to make them immediately available within the package namespace (which is why, for example, you can access os.path.* when you have imported os) and others...
2
4312
by: Nikhil | last post by:
I am using the MySQLdb python module. I have a table named 'testing' with few columns, under the 'test' database, what is hosted on a remote mysql server. I want to run the following query to get a comma-seperated information from the table LOCK TABLES foo READ; SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
0
7724
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7944
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8149
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7699
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5523
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5247
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.