Python DB-API: how to handle different paramstyles?

• This Python recipe might be able to help. It introduces an extra layer of abstraction to wrap parameters in its own Param class.

• The PyDal project may also be closer to what you're trying to achieve: "PyDal makes it possible to use the same paramstyle and datetime types with any module that conforms to DBAPI 2.0. In addition, paramstyles and datetime types are configurable."

Strictly speaking, the problem is not caused by the DB API allowing this, but by the different databases which use different SQL syntaxes. The DB API module passes the exact query string to the database, along with the parameters. "Resolving" the parameter markers is done by the database itself, not by the DB API module.

That means that if you want to solve this, you have to introduce some higher level of abstraction. If you do not want to add extra dependencies, you will have to do it yourself. But rather than manually escaping and substituting, you could try to dynamically replace parameter markers in the query string with the desired parameter markers, based on the paramstyle of the backend module. Then pass the string, WITH parameter markers to the db. For example, you could use '%s' everywhere, and use python string substitution to replace the '%s' with ':1', ':2' etc. if the db uses 'numeric' style, and so on....

The thing that tripped me up here was how to figure out what paramstyle is required if your code is just being passed a connection or cursor object. Here's what I came up with:

import importlib

def get_paramstyle(conn):
    name = conn.__class__.__module__.split('.')[0]
    mod = importlib.import_module(name)
    return mod.paramstyle

You should probably do more sanity checking of the conn object, or at least wrap this up in a try block, depending on what assumptions you're willing to make.