[issue14619] Enhanced variable substitution for databases

[Amaury Forgeot d'Arc]

Amaury Forgeot d'Arc <amauryfa at gmail.com> added the comment:

I agree this would be very handy, but the database engines I know which accept "bind variables" (Oracle, MySQL, JDBC) only accept simple types.
So to handle ?? it would be necessary to modify the SQL statement passed to the database server: "name in (?, ?, ?)".

This has some drawbacks IMO:
- One advantage of bind variables is that the SQL server sees the same statement for different invocations of execute() and thus can reuse computed data (parsed query, execution plan, etc) .  The "??" placeholder would silently kill this optimization.
- cursor.executemany() would have to format and pass a different statement for each row, which would break the implementations that prepare the statement once and pass all the rows in a single call.
- cx_Oracle has a cursor.prepare(stmt) function which explicitly exposes the above mechanism; it could not work with "??".

Yes, the IN operator in SQL is difficult to address.  I've tried several approaches to this, one of them was to create a temporary table and joint it in the main query...

----------
nosy: +amaury.forgeotdarc

_______________________________________
Python tracker <report at bugs.python.org>
<http://bugs.python.org/issue14619>
_______________________________________