[DB-SIG] Parameter substitution and "IN" operator

[Gerhard Häring]

-----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+dm6rO0nYkDfiLD4hpwKipycwCfR/l7
pUUMxm//EkvHUrEHAokO65c=
=YOe1
-----END PGP SIGNATURE-----