Python DB-API: how to handle different paramstyles?

I'm implementing a Python ontology class that uses a database backend to store and query the ontology. The database schema is fixed (specified in advance), but I don't know what type of database engine is being used. However, I can rely on the fact that the Python interface of the database engine uses the Python DB-API 2.0 (PEP 249). A straightforward idea is to let the user pass a PEP 249-compliant Connection object to the constructor of my ontology, which will then use various hardcoded SQL queries to query the database:

class Ontology(object):
    def __init__(self, connection):
        self.connection = connection

    def get_term(self, term_id):
        cursor = self.connection.cursor()
        query = "SELECT * FROM term WHERE id = %s"
        cursor.execute(query, (term_id, ))
        [...]

My problem is that different database backends are allowed to support different parameter markers in the queries, defined by the paramstyle attribute of the backend module. For instance, if paramstyle = 'qmark', the interface supports the question mark style (SELECT * FROM term WHERE id = ?); paramstyle = 'numeric' means the numeric, positional style (SELECT * FROM term WHERE id = :1); paramstyle = 'format' means the ANSI C format string style (SELECT * FROM term WHERE id = %s). If I want to ma开发者_JS百科ke my class be able to handle different database backends, it seems that I have to prepare for all the parameter marker styles. This seems to defeat the whole purpose of a common DB API for me as I can't use the same parameterised query with different database backends.

Is there a way around it, and if so, what is the best approach? The DB API does not specify the existence of a generic escaping function with which I can sanitize my values in the query, so doing the escaping manually is not an option. I don't want to add an extra dependency to the project either by using an even higher level of abstraction (SQLAlchemy, for instance).

• 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.

I don't want to add an extra dependency to the project either by using an even higher level of abstraction (SQLAlchemy, for instance).

That's too bad, because SQLAlchemy would be a perfect solution for this problem. In theory, DB-API 2.0 is built to offer this kind of flexibility. But that would require every driver developer (for Oracle, MySQLdb, Postgres, etc) to implement all the different paramstyles in their drivers. They don't. So you get stuck with the "preferred" paramstyle for each database engine.

If you refuse to use SQLAlchemy or any other higher abstraction layer or modern MVC class library, yes you have to write your own higher level of abstraction for this. I don't recommend that, despite that being your chosen solution here. You're facing some devilish details there, and will waste time figuring out bugs that others have already solved.

Don't view an external library dependency as a bad thing. If that's your approach to Python, you are going to be missing out on some of the most powerful features of the language.

Pick your poison.