Re: About mogrify

On 11/20/08, Federico Di Gregorio <fog-NGVKUo/i/[email protected]> wrote:
> Il giorno mer, 19/11/2008 alle 18.27 +0100, paftek ha scritto:
>
>  > I also use mogrify() to add WHERE conditions to my queries according
>  > to what the user decides to filter on.
>  >
>  > SELECT * FROM item
>  > WHERE user_id = 1
>  >     AND price > 10 -- added using mogrify()
>  >     AND quantity > 50 -- added using mogrify()
>
>
> I don't understand why you need to use mogrify() here.
>
>
>  federico
>


Because we want our users to be able to filter their inventory against
the criterias they choose (from a list of allowed criterias).
In the following example, the user chooses to display his items with
price > 10, quantity > 50 and sku = 'foo' :

>>> # Test table
... curs.execute(
...     """
...     CREATE TEMP TABLE item (
...         user_id serial,
...         sku character varying,
...         price numeric(10, 2),
...         quantity integer,
...         weight numeric(10,2),
...         height numeric(10,2)
...     )
...     """
... )
>>> # Test query
... query = """
...     SELECT * FROM item
...     WHERE user_id = %(user_id)s
... """
>>> # Criterias to filter on, dynamically choosen by the user in the UI
... criterias = [
...     ('price', '>', 10),
...     ('quantity', '=', 50),
...     ('sku', '=', 'foo')
... ]
>>> # Add criterias to query
... if criterias:
...     query = query + ' AND ' + ' AND '.join([
...         '%s %s %s' % (col, op, curs.mogrify('%s', [v]))
...         for col, op, v in criterias
...     ])
...
>>> curs.execute(query, {'user_id': 1})
>>> print curs.query

    SELECT * FROM item
    WHERE user_id = 1
 AND price > 10 AND quantity = 50 AND sku = E'foo'


Julien