Enhanced variable substitution for databases

[rhettinger]

BPO	14619
Nosy	@malemburg, @rhettinger, @amauryfa

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2019-08-25.03:20:23.507>
created_at = <Date 2012-04-19.05:14:54.716>
labels = ['extension-modules', 'type-feature']
title = 'Enhanced variable substitution for databases'
updated_at = <Date 2019-08-25.03:20:23.507>
user = 'https://github.com/rhettinger'

bugs.python.org fields:

activity = <Date 2019-08-25.03:20:23.507>
actor = 'rhettinger'
assignee = 'none'
closed = True
closed_date = <Date 2019-08-25.03:20:23.507>
closer = 'rhettinger'
components = ['Extension Modules']
creation = <Date 2012-04-19.05:14:54.716>
creator = 'rhettinger'
dependencies = []
files = []
hgrepos = []
issue_num = 14619
keywords = []
message_count = 3.0
messages = ['158698', '158703', '158707']
nosy_count = 3.0
nosy_names = ['lemburg', 'rhettinger', 'amaury.forgeotdarc']
pr_nums = []
priority = 'low'
resolution = 'third party'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue14619'
versions = ['Python 3.4']

[rhettinger]

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)

[amauryfa]

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...

[malemburg]

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.