[Tutor] Building an SQL query
Lee Harr
missive at hotmail.com
Thu Jun 2 22:55:56 CEST 2005
>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/
More information about the Tutor
mailing list