classification
Title: Enhanced variable substitution for databases
Type: enhancement Stage: resolved
Components: Extension Modules Versions: Python 3.4
process
Status: closed Resolution: third party
Dependencies: Superseder:
Assigned To: Nosy List: amaury.forgeotdarc, lemburg, rhettinger
Priority: low Keywords:

Created on 2012-04-19 05:14 by rhettinger, last changed 2019-08-25 03:20 by rhettinger. This issue is now closed.

Messages (3)
msg158698 - (view) Author: Raymond Hettinger (rhettinger) * (Python committer) Date: 2012-04-19 05:14
I suggest adding a ?? placeholder for variable length substitutions in SQL statements:

vars = 'Knight', ('Gwain', 'Gallahad', 'Lancelot'), 30
c.execute('''SELECT * FROM loyalsubjects
             WHERE rank = ? 
             AND name IN (??)
             AND age >= ?
          ''', vars)
msg158703 - (view) Author: Amaury Forgeot d'Arc (amaury.forgeotdarc) * (Python committer) Date: 2012-04-19 07:42
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...
msg158707 - (view) Author: Marc-Andre Lemburg (lemburg) * (Python committer) Date: 2012-04-19 08:54
Raymond, the variable substitution is normally done by the database and not the Python database modules, so you'd have to ask the database maintainers for assistance.

The qmark ('?') parameter style is part of the ODBC standard, so it's unlikely that this will get changed any time soon unless you have good contacts with Microsoft :-)

The ODBC standard also doesn't support multi-value substitutions in the API, so there's no way to pass the array to the database driver.

BTW: Such things are better discussed on the DB-SIG mailing list than the Python tracker.
History
Date User Action Args
2019-08-25 03:20:23rhettingersetstatus: open -> closed
resolution: third party
stage: resolved
2012-04-19 08:54:22lemburgsetnosy: + lemburg
messages: + msg158707
2012-04-19 07:42:07amaury.forgeotdarcsetnosy: + amaury.forgeotdarc
messages: + msg158703
2012-04-19 05:14:54rhettingercreate