correct parameter usage for "select * where id in ..."

Steve Holden steve at holdenweb.com
Sat Oct 28 12:06:00 CEST 2006


saniac wrote:
> I am working on a little project using pysqlite. It's going to be
> exposed on the web, so I want to make sure I quote all incoming data
> correctly. However, I've run into a brick wall trying to use parameters
> to populate a query of the form "select * where col1 in ( ? )"
> 
> The naive approach doesn't work:
> 
>   values=['foo', 'bar', 'baz']
>   sql = """select  * where value in (?)"""
>   cu = cx.cursor()
>   cu.execute(sql, (values))
> 
> The code blows up because the cursor is expecting 1 arg and gets 3. I
> tried joining the array members with a comma, and that didn't work.
> I've also tried the equivalent with the named style, which pysqlite
> also supports, but that didn't work either.
> 
> I can't find any documentation that demonstrates this kind of query.
> 
> Is there a way to do this? It seems a bit odd not to have a way to
> escape this kind of query.
> 
Well, you could try using a tuple whose single element is that 
three-element tuple with your list if values:

   cu.execute(sql, (values, ))

which I repsume is shat you really meant to do. Note, though, that not 
all DB API modules will accept lists and/or tuples as data elements of 
that kind, so you may be disappointed.

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC/Ltd          http://www.holdenweb.com
Skype: holdenweb       http://holdenweb.blogspot.com
Recent Ramblings     http://del.icio.us/steve.holden




More information about the Python-list mailing list