[issue14619] Enhanced variable substitution for databases

[Raymond Hettinger]

Change by Raymond Hettinger :


--
resolution:  -> third party
stage:  -> resolved
status: open -> closed

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue14619] Enhanced variable substitution for databases

[Amaury Forgeot d'Arc]

Amaury Forgeot d'Arc amaur...@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 rep...@bugs.python.org
http://bugs.python.org/issue14619
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue14619] Enhanced variable substitution for databases

[Marc-Andre Lemburg]

Marc-Andre Lemburg m...@egenix.com added the comment:

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.

--
nosy: +lemburg

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue14619
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

[issue14619] Enhanced variable substitution for databases

[Raymond Hettinger]

New submission from Raymond Hettinger raymond.hettin...@gmail.com:

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)

--
components: Extension Modules
messages: 158698
nosy: rhettinger
priority: low
severity: normal
status: open
title: Enhanced variable substitution for databases
type: enhancement
versions: Python 3.4

___
Python tracker rep...@bugs.python.org
http://bugs.python.org/issue14619
___
___
Python-bugs-list mailing list
Unsubscribe: 
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com