Parameter substitution and "IN" operator

Has there been any discussion of supporting lists or tuples
as 
parameters to the "IN" operator?

I'd like to be able to do something to the effect of:

curs.execute( "select * from mytable where col1 = %s
and col2 in (%s)",
              ( 'firstval', ('list', 'of', 'vals') )


Or perhaps:

curs.execute( "select * from mytable where col1 = %s
and col2 in %s",
              ( 'firstval', ('list', 'of', 'vals') )


And have the module include the parens.

It seems to almost work sometimes:

c.execute( 'select * from gateway where code in %s', ( ( 1,
2 ), ) )
print c._executed

Yields:

select * from gateway where code in ('1', '2')


but

c.execute( 'select * from gateway where code in %s', ( (
'cys', 'syr' ), ) )
print c._executed

yields:

select * from gateway where code in ("'cys'",
"'syr'")


Besides, I think it's just a side effect of the string
representations 
of the tuple and not a planned feature.

I know how to build a string with the correct number of
placeholders for 
my "IN" list, but I think it would be a nice
feature of the API to 
handle it for the the user.

Thanks,
e.

_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Eric Brunson wrote:
> Has there been any discussion of supporting lists or
tuples as 
> parameters to the "IN" operator? [...]

I didn't search the archives, either 

> I'd like to be able to do something to the effect of:
> 
> curs.execute( "select * from mytable where col1 =
%s and col2 in (%s)",
>               ( 'firstval', ('list', 'of', 'vals') )
> 
> 
> Or perhaps:
> 
> curs.execute( "select * from mytable where col1 =
%s and col2 in %s",
>               ( 'firstval', ('list', 'of', 'vals') )
> 
> 
> And have the module include the parens. [...]

This is not the way SQL parameter binding works. SQL
parameter binding
only works with scalars (*). If tuples seem to work, then
that's just
a side-effect of the implementation of your particular
DB-API module.

Certain DB-API modules like pyPgSQL (and IIRC psycopg and
MySQLdb) try
to make your requested feature work. That's only possible
because the
database involved (MySQL and PostgreSQL, at least older
versions)
didn't really support parameter binding. And if the database
doesn't
support it, it is then "faked" on the Python side
using escaping
values and string substitution.

If the database, however, supports parameter binding
natively, like
most major ones do, and even small ones like SQLite, then
your feature
request cannot be implemented.

HTH,

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org


iD8DBQFH3vtfdIO4ozGCH14RApQrAKDBZ+dm6rO0nYkDfiLD4hpwKipycwCf
R/l7
pUUMxm//EkvHUrEHAokO65c=
=YOe1
-----END PGP SIGNATURE-----

_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Alex Willmer wrote:
> On Tue, 2008-03-18 at 00:14 +0100, Gerhard Häring
wrote:
>> This is not the way SQL parameter binding works.
SQL parameter binding
>> only works with scalars (*). If tuples seem to
work, then that's just
>> a side-effect of the implementation of your
particular DB-API module.
> 
>> >From here, it seems like native SQL parameter
binding for tuples is a
> good thing. Do relational databases not implement it
merely because no
> one has written the code yet, or is it missing from the
SQL standard/a
> bad idea in principle?

It's missing from the SQL standard. And it's not implemented
because
it would make the main idea of parameter binding useless:
reusing
query plans.

I agree that in theory the "SQL-to-virtual-machine
compiler" on the
database backends could be smarter like this, but it would
probably
add too much complexity.

If you really really need the functionality with
"IN" and want native
parameter binding, you can fake it using temporary tables.

Pseudo-code:

<fill table tmp_table>
select ... from table1 where col1 in (select col2 from
tmp_table).

or

<fill table tmp_table>
select ... from table1 where exists (select 1 from tmp_table
where
col2=table1.col1)

The EXISTS version is better with some databases (certainly
it was for
Oracle 8, which just "cut off" values in
"IN" clauses if you had more
than 255 or something).

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org


iD8DBQFH3wpzdIO4ozGCH14RAmPuAJ9QNEgiBU4pcKqTc+k3/MBTe5pFBgCf
S5oA
VRizXimLdIj6hExBIvaOObI=
=Cj32
-----END PGP SIGNATURE-----
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig