[Tutor] Building an SQL query

[Bob Gailer]

At 06:48 AM 6/2/2005, Greg Lindstrom wrote:
>Hello-
>
>I am building a query to hit a Postgres (8.0.1) database from Python 
>(4.2.1) on Linux.  Here's how I've been doing it for the past year or so:
>
>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)
>
>and life has been good.  What I would like to do now is use the Postgres 
>"IN" operator.  For example:
>
>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
>'''

Do you want, in this case, the where clause to be : WHERE id 
IN  ('1','2','3','5','7','11')?
If so, how about:
query = '''
   UPDATE my_table
     SET state = 'processed'
     WHERE id IN ''' + str(ids_to_process)

>This would, of course, set the 'state' column to 'processed' for all of 
>the ids in the list, but can not figure out how to get this into a query 
>to pass to the database.  Have any of you smart cookies out there dealt 
>with this?  There are other ways to get the job done, worst case being 
>writing a look and issuing an UPDATE for each id, but that is not too 
>elegant, IMHO.
>
>Any help or pointers would be greatly appreciated,
>
>--greg
>_______________________________________________
>Tutor maillist  -  Tutor at python.org
>http://mail.python.org/mailman/listinfo/tutor

Bob Gailer
mailto:bgailer at alum.rpi.edu
510 558 3275 home
720 938 2625 cell  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/tutor/attachments/20050602/af98cae2/attachment.html