Recipe7.16.Using a Single Parameter-Passing Style Across Various DB API Modules

Recipe 7.16. Using a Single Parameter-Passing Style Across Various DB API Modules

Credit: Denis S. Otkidach

Problem

You want to write Python code that runs under any DB API compliant module, but such modules can use different styles to allow parameter passing.

Solution

We need a set of supporting functions to convert SQL queries and parameters to any of the five possible parameter-passing styles:

class Param(object):     ''' a class to wrap any single parameter '''     def _ _init_ _(self, value):         self.value = value     def _ _repr_ _(self):         return 'Param(%r)' % (self.value,) def to_qmark(chunks):     ''' prepare SQL query in '?' style '''     query_parts = [  ]     params = [  ]     for chunk in chunks:         if isinstance(chunk, Param):             params.append(chunk.value)             query_parts.append('?')         else:             query_parts.append(chunk)     return ''.join(query_parts), params def to_numeric(chunks):     ''' prepare SQL query in ':1' style '''     query_parts = [  ]     params = [  ]     for chunk in chunks:         if isinstance(chunk, Param):             params.append(chunk.value)             query_parts.append(':%d' % len(params))         else:             query_parts.append(chunk)     # DCOracle2 needs, specifically, a _tuple_ of parameters:     return ''.join(query_parts), tuple(params) def to_named(chunks):     ''' prepare SQL query in ':name' style '''     query_parts = [  ]     params = {  }     for chunk in chunks:         if isinstance(chunk, Param):             name = 'p%d' % len(params)             params[name] = chunk.value             query_parts.append(':%s' % name)         else:             query_parts.append(chunk)     return ''.join(query_parts), params def to_format(chunks):     ''' prepare SQL query in '%s' style '''     query_parts = [  ]     params = [  ]     for chunk in chunks:         if isinstance(chunk, Param):             params.append(chunk.value)             query_parts.append('%s')         else:             query_parts.append(chunk.replace('%', '%%'))     return ''.join(query_parts), params def to_pyformat(chunks):     ''' prepare SQL query in '%(name)s' style '''     query_parts = [  ]     params = {  }     for chunk in chunks:         if isinstance(chunk, Param):             name = 'p%d' % len(params)             params[name] = chunk.value             query_parts.append('%%(%s)s' % name)         else:             query_parts.append(chunk.replace('%', '%%'))     return ''.join(query_parts), params converter = {  } for paramstyle in ('qmark', 'numeric', 'named', 'format', 'pyformat'):     converter[paramstyle] = globals['to_' + param_style] def execute(cursor, converter, chunked_query):     query, params = converter(chunked_query)     return cursor.execute(query, params) if _ _name_ _=='_ _main_ _':     query = ('SELECT * FROM test WHERE field1>', Param(10),              ' AND field2 LIKE ', Param('%value%'))     print 'Query:', query     for paramstyle in ('qmark', 'numeric', 'named', 'format', 'pyformat'):         print '%s: %r' % (paramstyle, converter[param_style](query))

Discussion

The DB API specification is quite handy, but it has one most annoying problem: it allows compliant modules to use any of five parameter styles. So you cannot necessarily switch to another database just by changing the database module: if the parameter-passing styles of two such modules differ, you need to rewrite all SQL queries that use parameter substitution. Using this recipe, you can improve this situation a little. Pick the appropriate converter from the converter dictionary (indexing it with the paramstyle attribute of your current DB API module), write your queries as mixed chunks of SQL strings and instances of the provided Param class (as exemplified in the if _ _name_ _=='_ _main_ _' part of the recipe), and execute your queries through the execute function in this recipe. Not a perfectly satisfactory solution, by any means, but way better than nothing!

Recipe7.16.Using a Single Parameter-Passing Style Across Various DB API Modules