select * where some_column in statement

[Ronald Lew]

I want to use the "in" keyword for sql queries. If I want to do like this:
"SELECT * FROM names WHERE first_name IN ('Bob', 'John') AND last_name='Doe'"

cursor.execute("SELECT * FROM names WHERE first_name IN %s AND last_name=%s",
(['Bob', 'John'], 'Doe'))

The converted queries looks like (but I don't want the sinqle quotes in it):
SELECT * FROM names WHERE first_name IN ("'Bob'", "'John'") AND last_name='Doe'

I tried switching the list into a tuple but I get the same results. I don't do anything special with the connection converter except having LONG use int as a callable.

[Leo Hourvitz]

There's nothing wrong with using an IN expression in that way. However, the database API won't automatically expand your list for you -- you have to get the right number of %s strings into the query.

The manual way would be:

cursor.execute("SELECT * FROM names WHERE first_name IN ( %s, %s ) AND last_name=%s",('Bob', 'John', 'Doe'))

If you don't know in advance how many items will be in your list, you have to do something slightly more complicated (and uglier). There's lots of ways to do that, but I usually use a trick of string expansion:

names = [ 'Bob', 'John', 'Bill' ]
% Note double %%s for non-in-clause arguments
query = "SELECT * FROM names " \ "WHERE first_name IN ( %s ) AND last_name=%%s"
query = query % ('%s,'*len(names)).rstrip(',')
cursor.execute(query,names+['Doe'])

YMMV. Notice that this still protects all your arguments: your program isn't interpolating the arguments into the query, it just interpolates the right number of %s strings.

Leo

[Mark]

I think what you want is more like this:

SELECT * FROM names WHERE first_name REGEXP '^(Bob|John)$' AND last_name='Doe'

I haven't used the IN clause but it looks like you could use it if say you had two fields, misc1 and misc2 in which text is entered. Then to select a record where 'Bob Doe' was either in misc1 or misc2 you could use:

SELECT * FROM names WHERE 'Bob Doe' IN (misc1,misc2)