[sqlgen] Allow to unprotect some parameters during the creation of an SQL INSERT query

authorVincent Michel <vincent.michel@logilab.fr>
changesetc6279447b722
branchdefault
phasedraft
hiddenyes
parent revision#87f26f15a66e merge with stable
child revision#e6842810e284 Add a SQL_CONVERTERS dict to allow possible conversions to SQL for specific types
files modified by this revision
sqlgen.py
# HG changeset patch
# User Vincent Michel <vincent.michel@logilab.fr>
# Date 1365083528 -7200
# Thu Apr 04 15:52:08 2013 +0200
# Node ID c6279447b7223f85ed64f186c66eb6ddbc17cfcd
# Parent 87f26f15a66eaa6caf9173a315f2b0a05c31b719
[sqlgen] Allow to unprotect some parameters during the creation of an SQL INSERT query

diff --git a/sqlgen.py b/sqlgen.py
@@ -54,23 +54,40 @@
1          >>> s.set(['nom','prenom'])
2          'nom = %(nom)s, prenom = %(prenom)s'
3          """
4          return ", ".join(["%s = %%(%s)s" % (x, x) for x in keys])
5 
6 -    def insert(self, table, params):
7 +    def insert(self, table, params, pre_params=None):
8          """
9          :param table: name of the table
10          :param params:  dictionary that will be used as in cursor.execute(sql,params)
11 +        :param pre_params:  additional dictionary for unprotected parameters,
12 +                            that will be already encoded in the returned INSERT string,
13 +                            and thus should not be included in the params passed to
14 +                            cursor.execute(sql, params)
15 +                            (used for insertion of specific sql types)
16 
17          >>> s = SQLGenerator()
18          >>> s.insert('test',{'nom':'dupont'})
19          'INSERT INTO test ( nom ) VALUES ( %(nom)s )'
20          >>> s.insert('test',{'nom':'dupont','prenom':'jean'})
21          'INSERT INTO test ( nom, prenom ) VALUES ( %(nom)s, %(prenom)s )'
22          """
23 +        pre_params = pre_params or {}
24 +        # Pre keys - Should not be protected in doexec
25 +        # Order is important
26 +        pre_items = pre_params.items()
27 +        pre_keys = ', '.join([i[0] for i in pre_items])
28 +        pre_values = ', '.join([i[1] for i in pre_items])
29 +        pre_keys = pre_keys.replace('%', '%%')
30 +        pre_values = pre_values.replace('%', '%%')
31 +        # Classical case
32          keys = ', '.join(params.keys())
33          values = ', '.join(["%%(%s)s" % x for x in params])
34 +        # Join keys and pre_keys
35 +        keys = keys + ((', ' + pre_keys) if pre_keys else '')
36 +        values = values + ((', ' + pre_values) if pre_values else '')
37          sql = 'INSERT INTO %s ( %s ) VALUES ( %s )' % (table, keys, values)
38          return sql
39 
40      def select(self, table, params=None, selection=None):
41          """