Allows to use "named"-style params with drivers that support only "qmark"-style, as sqlite3.
Python, 10 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 | import re
_named_to_qmark_re = re.compile('\:([a-zA-Z_][a-zA-Z_0-9]*)')
def named_to_qmark(query, d):
''' Converts a query from qmark to named style using dict d '''
params = []
def f(mo):
params.append(d[mo.group(1)])
return '?'
query = _named_to_qmark_re.sub(f, query)
return query, params
|
It's very annoying when you want to use "named" params (DBAPI's "paramstyle") in a query but your Python driver offers only "qmark" style; this is what happens for instance with the built-in sqlite3 driver. This is a simple function that allows calling in "named" style, i.e. Connection.execute(query, dict).
Example usage:
Python 3.3.0 (default, Dec 12 2012, 07:38:12)
[GCC 4.7.2] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import re
>>> _named_to_qmark_re = re.compile('\:([a-zA-Z_][a-zA-Z_0-9]*)')
>>> def named_to_qmark(query, d):
... ''' Converts a query from qmark to named style using dict d '''
... params = []
... def f(mo):
... params.append(d[mo.group(1)])
... return '?'
... query = _named_to_qmark_re.sub(f, query)
... return query, params
...
>>> named_to_qmark('select * from mytable where a=:a and b=:b and :a<>b', {'a': 1, 'b': 2})
('select * from mytable where a=? and b=? and ?<>b', [1, 2, 1])
>>>
A typical use of the sqlite3.Connection.execute() api would be: