Description

An extention to DBAPI-2.0 for more easily building SQL statements.

This extension allows you to call a DBAPI Cursor's execute method with a string that contains format specifiers for escaped and/or unescaped arguments. Escaped arguments are specified using `` %X `` or `` %S `` (capital X or capital S). You can also mix positional and keyword arguments in the call, and this takes advantage of the Python call syntax niceties. Also, lists passed in as parameters to be formatted are automatically detected and joined by commas (this works for both unescaped and escaped parameters-- lists to be escaped have their elements escaped individually). In addition, if you pass in a dictionary corresponding to an escaped formatting specifier, the dictionary is rendered as a list of comma-separated <key> = <value> pairs, such as are suitable for an INSERT statement.

For performance, the results of analysing and preparing the query is kept in a cache and reused on subsequence calls, similarly to the re or struct library.

(This is intended to become a reference implementation for a proposal for an extension to tbe DBAPI-2.0.)

For more details and motivation, see the accompanying explanation document at http://furius.ca/pubcode/pub/conf/lib/python/dbapiext.html

5-minute usage instructions:

Run execute_f() with a cursor object and appropriate arguments:

execute_f(cursor, ' SELECT %s FROM %(t)s WHERE id = %S ', cols, id, t=table)

Ideally, we should be able to monkey-patch this method onto the cursor class of the DBAPI library (this may not be possible if it is an extension module).

By default, the result of analyzing each query is cached automatically and reused on further invocations, to minimize the amount of analysis to be performed at runtime. If you want to do this explicitly, first compile your query, and execute it later with the resulting object, e.g.:

analq = qcompile(' SELECT %s FROM %(t)s WHERE id = %S ')
...
analq.execute(cursor, cols, id, t=table)

Note to developers: this module contains tests, if you make any changes, please make sure to run and fix the tests.

Also, a formatting specifier is provided for where clauses: %A, which joins its contained entries with AND. The only accepted data types are list of pairs or a dictionary. Maybe we could provide an OR version (%A and %O).

Future Work

• We could provide a reduce() method on the QueryAnalyzer, that will apply the given parameters and save the calculated arguments for later use; This would allow us to apply queries using multiple calls, to fill in only certain parameters at a time. This method would return a new QueryAnalyzer, albeit one that would contain some pre-cooked apply_kwds and delay_kwds to be accumulated to in the apply call.
• Provide a simple test function that would allow people to test their queries without having to create a TestCursor.

Please Donate!

Download

Download program here.