[Tutor] Building an SQL query

[Lee Harr]

>data = {}
>data['start_date'] = '2005-6-2'
>data['last_name'] = 'Johnson'
>
>query = '''
>    SELECT *
>      FROM my_table
>     WHERE date >= '%(start_date)s'
>       AND last_name = '%(last_name)s'
>''' % data
>results = my_database.Execute(query)


First up. This is a "bad idea".

It may be ok now, as long as you have absolute control
over what start_date and last_name are, but what about
next week when you decide ... "let's allow the user to put
in the dates for start_date" and they make start_date
"'6-2-05'; DELETE FROM my_table; SELECT * FROM my_table
WHERE date='6-2-05' "

Instead, use the arg quoting mechanism from the db
interface you are using. You don't say which one that
is, but it should look something like ...

data = {}
data['start_date'] = '2005-6-2'
data['last_name'] = 'Johnson'

query = '''
    SELECT *
      FROM my_table
     WHERE date >= '%(start_date)s'
       AND last_name = '%(last_name)s'
'''
results = my_database.execute(query, data)



>ids_to_process = ('1','2','3','5','7','11')
>
>I would like to get something akin to:
>
>query = '''
>   UPDATE my_table
>      SET state = 'processed'
>    WHERE id IN ids_to_process
>'''

You can use an array type in postgres. I use something
like this:

data = {}
data['ids_to_process'] = ['1','2','3','5','7','11']

query = '''
   UPDATE my_table
      SET state = 'processed'
    WHERE id IN ARRAY%(ids_to_process)s
'''
db.execute(query, data)


Notice that I changed the data tuple to a list. The postgres
syntax for an array is ARRAY[1, 2, 3, 5, 7, 11] so you need
the square brackets that a list will give you.

It's kind of a hack, but as far as I know none of the other
databases have arrays, so the db-api people are not into
creating a special array access method.

_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar - get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/