Thread: How do I use parameterized queries with LIKE?
This works just fine:
cursor.execute("""select email_address from customer where
email_address like '%matt%'""")
But when I move the "matt" part out and use a %s symbol instead, I get
this error:
ValueError: unsupported format character ''' (0x27) at index 73
What is the right solution here?
Thanks for the help.
Matt
--
W. Matthew Wilson
matt@tplus1.com
http://tplus1.com
On 05/28/2012 03:00 PM, W. Matthew Wilson wrote:
> This works just fine:
>
> cursor.execute("""select email_address from customer where
> email_address like '%matt%'""")
>
> But when I move the "matt" part out and use a %s symbol instead, I get
> this error:
>
> ValueError: unsupported format character ''' (0x27) at index 73
>
> What is the right solution here?
http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
So you need something like:
cursor.execute("""select email_address from customer where
email_address like %s""", ("matt",))
Note in particular the ("matt",). The parameters in this form need to be passed
as a tuple.
>
> Thanks for the help.
>
> Matt
>
--
Adrian Klaver
adrian.klaver@gmail.com
On Mon, May 28, 2012 at 11:00 PM, W. Matthew Wilson <matt@tplus1.com> wrote:
> But when I move the "matt" part out and use a %s symbol instead, I get
> this error:
>
> ValueError: unsupported format character ''' (0x27) at index 73
>
> What is the right solution here?
If you have parameters in the query, % is used as placeholder prefix.
You must use %% to include a literal % in the query:
In [14]: cur.execute("""select email_address from customer where
email_address like '%%' || %s || '%%'""", ('matt',))
or you can add the % to the value instead of the query:
In [17]: cur.execute("""select email_address from customer where
email_address like %s""", ('%matt%',))
Hope this helps,
-- Daniele
On Tuesday 29 May 2012, W. Matthew Wilson wrote:
> This works just fine:
>
> cursor.execute("""select email_address from customer where
> email_address like '%matt%'""")
You'd probably need to escape the percent signs like:
cursor.execute("""select email_address from customer where email_address like
'%%matt%%'""")
--
Say NO to spam and viruses. Stop using Microsoft Windows!
On Mon, May 28, 2012 at 6:42 PM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> If you have parameters in the query, % is used as placeholder prefix.
> You must use %% to include a literal % in the query:
>
> In [14]: cur.execute("""select email_address from customer where
> email_address like '%%' || %s || '%%'""", ('matt',))
>
> or you can add the % to the value instead of the query:
>
> In [17]: cur.execute("""select email_address from customer where
> email_address like %s""", ('%matt%',))
Thanks so much! I'm using the first solution, so that I can just
extract user-submitted data and pass it right in, rather than
prepending and appending '%' on the user data.
Matt
--
W. Matthew Wilson
matt@tplus1.com
http://tplus1.com
On 29/05/12 02:30, W. Matthew Wilson wrote:
> On Mon, May 28, 2012 at 6:42 PM, Daniele Varrazzo
> <daniele.varrazzo@gmail.com> wrote:
>> > If you have parameters in the query, % is used as placeholder prefix.
>> > You must use %% to include a literal % in the query:
>> >
>> > In [14]: cur.execute("""select email_address from customer where
>> > email_address like '%%' || %s || '%%'""", ('matt',))
>> >
>> > or you can add the % to the value instead of the query:
>> >
>> > In [17]: cur.execute("""select email_address from customer where
>> > email_address like %s""", ('%matt%',))
> Thanks so much! I'm using the first solution, so that I can just
> extract user-submitted data and pass it right in, rather than
> prepending and appending '%' on the user data.
If you're using user-submitted data remember to ALWAYS use bound
variables and never fallback to Python string interpolation: SQL
injection lurks.
federico
--
Federico Di Gregorio federico.digregorio@dndg.it
Studio Associato Di Nunzio e Di Gregorio http://dndg.it
Gli avvoltoi cinesi si nutrono di arte, ma possono anche mangiare
i `domani'. -- Haruki Murakami
Hi -
I have a Mac 10.6, and I just downloaded XCode 3.2 from Apple dev
site.
** there is no custom install option in the XCode installer ** but I
am looking
at the package with Pacifist now.. Apparently Apple saw fit to remove
ppc libs
pointing to pg_config is easy, and that is done
But how do I build psycopg2 without arch ppc ?
thanks in advance
-Brian
On May 30, 2012, at 8:51 PM, Brian Hamlin wrote:
>
> I have a Mac 10.6, and I just downloaded XCode 3.2 from Apple dev
> site.
> ** there is no custom install option in the XCode installer ** but
> I am looking
> at the package with Pacifist now.. Apparently Apple saw fit to
> remove ppc libs
>
> pointing to pg_config is easy, and that is done
>
> But how do I build psycopg2 without arch ppc ?
for the record..
(btw, searching briefly for archives of this mail list did find
anything obvious to me)
------------------------------------------------------------------------
-------------------------------------
Mac OS X 10.6 on intel - Postgres 9.1 + lots of goodies from
KyngChaos..
Install XCode 3.2 from Apple Dev Center
sudo easy_install pip ## picks python 2.6 without aid
sudo ln -s /usr/local/pgsql-9.1/bin/pg_config /usr/bin/pg_config
sudo pip install psycopg2 ## <- gives a warning about arch ppc,
but **succeeds**
------------------------------------------------------------------------
-------------------------------------
the psycopg2 version is very current too.. so I found a workaround..
but if someone knew how to build that would be handy..
thanks for an excellent tool
-Brian