This recipe allows to code SQL queries the same way independent on paramstyle of used DB module.
Python, 71 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | class Param:
def __init__(self, value):
self.value = value
def __repr__(self):
return 'Param(%r)' % (self.value,)
def to_qmark(chunks):
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):
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)
return ''.join(query_parts), tuple(params) # DCOracle2 has broken support
# for sequences of other types
def to_named(chunks):
query_parts = []
params = {}
for chunk in chunks:
if isinstance(chunk, Param):
name = 'p%d' % len(params) # Are numbers in name allowed?
params[name] = chunk.value
query_parts.append(':%s' % name)
else:
query_parts.append(chunk)
return ''.join(query_parts), params
def to_format(chunks):
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):
query_parts = []
params = {}
for chunk in chunks:
if isinstance(chunk, Param):
name = '%d' % len(params)
params[name] = chunk.value
query_parts.append('%%(%s)s' % name)
else:
query_parts.append(chunk.replace('%', '%%'))
return ''.join(query_parts), params
if __name__=='__main__':
query = ('SELECT * FROM test WHERE field1>', Param(10),
' AND field2 LIKE ', Param('%value%'))
print 'Query:', query
for param_style in ('qmark', 'numeric', 'named', 'format', 'pyformat'):
print '%s: %r' % (param_style, vars()['to_'+param_style](query))
|
The most annoying problem of DB API specification is that it allows modules to use any of 5 parameter styles. So you cannot switch to another database just by changing database module if their paramstyles differ, but need to rewrite all SQL queries. Although wrapping all parameters is not handy, the higher level interface for common queries can do it automatically, e.g. by wrapping all values in dictionary for "insert(table, {field1_name: field1_value, ...})" etc.
Tags: database
