Обсуждение: 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