lists in cx_Oracle

[Andrew Dalke]

infidel wrote:

> Something like this might work for you:
> 
>>>> ids= ['D102', 'D103', 'D107', 'D108']
>>>> in_clause = ', '.join([':id%d' % x for x in xrange(len(ids))])
>>>> sql = "select * from tablename where id in (%s)" % in_clause
>>>> import cx_Oracle as ora
>>>> con = ora.connect('foo/bar at geewhiz')
>>>> cur = con.cursor()
>>>> cur.execute(sql, ids)

That's pretty much what I did but it seems inelegant.
I would rather do

  ids = ['D102', 'D103', 'D107', 'D108']
   .. connect and set up the cursor ..
  cursor.execute("select * from tablename where id in :ids", ids)

and if 'ids' is seen to be a list or tuple then it does
the appropriate conversion.  (I'm also fine with having
to use ()s in the SQL query, as in "id in (:ids)".)

The lack of a simple way to do this is error prone. I've seen
people do

  cursor.execute("select * from tablename where id in (%s)" % repr(ids))

because the repr of a string is close enough that it works
for expected string values.  But it opens up the possibility
of SQL injection problems.

				Andrew
				dalke at dalkescientific.com