Generating User Search Queries

Discussion in 'Python' started by Lawrence D'Oliveiro, Apr 10, 2009.

  1. I implemented a form in a Web-based database-management application that
    lets the user search on a whole lot of different fields. I hate writing
    repetitive code. But here's part of the sequence I came up with
    for translating entered field values into MySQL query phrases:

    condition = \
    (
    list
    ( # free-text fields
    "%(name)s like %(value)s"
    %
    {
    "name" : field[0],
    "value" :
    SQLString("%" + EscapeSQLWild(Params.getvalue(field[1])) + "%"
    ),
    }
    for field in
    (
    ("make", "search_make"),
    ("model", "search_model"),
    ...
    )
    if Params.getvalue(field[1]) != ""
    )
    +
    list
    ( # exact-match fields
    "%(name)s = %(value)s"
    %
    {
    "name" : field[0],
    "value" : SQLString(Params.getvalue(field[1])),
    }
    for field in
    (
    ("class_name", "search_class"),
    ...

    )
    if Params.getvalue(field[1]) != ""
    )
    +
    list
    ( # date fields
    "("
    +
    " or ".join
    (
    "%(name)s %(op)s %(value)s"
    %
    {
    "name" : field[0],
    "op" : op[0],
    "value" : SQLString(Params.getvalue(field[1])),
    }
    for op in
    (
    ("<", "lt"),
    ("=", "eq"),
    (">", "gt"),
    )
    if GetCheckbox("%(name)s[%(op)s]" % {"name" : field[1], "op" : op[1]})
    )
    +
    ")"
    for field in
    (
    ("when_purchased", "search_when_purchased"),
    ...
    )
    if reduce
    (
    operator.__or__,
    (
    GetCheckbox("%(name)s[%(op)s]" % {"name" : field[1], "op" : op})
    for op in ("lt", "eq", "gt")
    )
    )
    )
    )

    And then you can build the whole thing into a where-clause just with

    " and ".join(condition)
     
    Lawrence D'Oliveiro, Apr 10, 2009
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,530
    Crutcher
    Feb 26, 2006
  2. Alec Taylor
    Replies:
    0
    Views:
    136
    Alec Taylor
    Oct 11, 2011
  3. Alec Taylor
    Replies:
    5
    Views:
    203
    alex23
    Oct 15, 2011
  4. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    367
    Aaron [SQL Server MVP]
    Aug 6, 2004
  5. Replies:
    2
    Views:
    95
Loading...

Share This Page