Python DB-API: how to handle different paramstyles?

user_156771 0
Viewed 5072

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 make 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).

python-db-api sql python
4 Answers

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.

user_13490740
Related