لطفا منتظر باشید ...
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!
See Also
The DB API docs at http://www.python.org/peps/pep-0249l; the
list of DB API-compliant modules at http://www.python.org/topics/database/modulesl.
