Issue 14619: Enhanced variable substitution for databases (original) (raw)

Issue14619

Created on 2012-04-19 05:14 by rhettinger, last changed 2022-04-11 14:57 by admin. This issue is now closed.

Messages (3)
msg158698 - (view)	Author: Raymond Hettinger (rhettinger) *	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) *	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) *	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.