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

Allows to use "named"-style params with drivers that support only "qmark"-style, as sqlite3.

Python, 10 lines
 1
 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])
>>>

1 comment

Steve Howe (author) 8 years, 11 months ago  # | flag

A typical use of the sqlite3.Connection.execute() api would be:

conn.execute(*named_to_qmark('select * from mytable where a=:a and b=:b and :a<>b', {'a': 1, 'b': 2}))