469,568 Members | 1,501 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

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

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.

Oct 28 '06 #1
4 2144
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

Oct 28 '06 #2

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 assume you mean 'select * from table where...'

Try this -

values=['foo', 'bar', 'baz']
sql = """select * from table where value in (?,?,?)"""
cu = cx.cursor()
cu.execute(sql, values)

It works with odbc from pywin32. I have not tried pysqlite.

If you want it to handle a variable number of values, you will have to
programmatically construct the sql statement with the appropriate
number of parameters.

HTH

Frank Millman

Oct 28 '06 #3
Frank Millman schrieb:
If you want it to handle a variable number of values, you will have to
programmatically construct the sql statement with the appropriate
number of parameters.
>>vals = (1,2,3,4,5)
sql = "select * from table where value in ("+','.join("?"*len(vals))+")"
print sql
'select * from table where value in (?,?,?,?,?)'

cheers
Paul

Oct 28 '06 #4
paul wrote:
Frank Millman schrieb:
If you want it to handle a variable number of values, you will have to
programmatically construct the sql statement with the appropriate
number of parameters.
Yes, I should have made it clear it was the variable part that was
hard.
>vals = (1,2,3,4,5)
sql = "select * from table where value in ("+','.join("?"*len(vals))+")"
print sql
'select * from table where value in (?,?,?,?,?)'
Argh, I have a scripting language and I'm not building up strings
dynamically? What an idiot.

Thanks, that's just what I needed.

Oct 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mysooru | last post: by
3 posts views Thread by Ian Lazarus | last post: by
2 posts views Thread by John A Grandy | last post: by
9 posts views Thread by king kikapu | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.