Project Home


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

Table of Contents


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 now the transformation only works with DBAPIs that supports parametric arguments in the form of Python's syntax for now (e.g. psycopg2). It could easily be extended to support other DBAPI syntaxes.

For more details and motivation, see the accompanying explanation document at

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

Please Donate!


This computer program or library is provided for free. I am aware that some of the programs that I provide for free allow people to get their work done faster or better, save them time and money. If you are using this program for benefit, especially if you are using it within a commercial environment and it saves you time or work, please consider making a donation by sending me a book from my Amazon Wishlist or by a direct donation to my company's PayPal account by clicking on the link below.


Download program here.