Welcome, guest | Sign In | My Account | Store | Cart

This recipe allows to code SQL queries the same way independent on paramstyle of used DB module.

Python, 71 lines
 1
 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.

Created by Denis Otkidach on Fri, 16 Apr 2004 (PSF)
Python recipes (4591)
Denis Otkidach's recipes (2)
Python Cookbook Edition 2 (117)

Required Modules

  • (none specified)

Other Information and Tasks