Major addition: implemented named arguments in sqlplain

5 files changed, 84 insertions, 65 deletions

diff --git a/sqlplain/connection.py b/sqlplain/connection.py
index 08e6ebc..ccfa116 100644
--- a/sqlplain/connection.py
+++ b/sqlplain/connection.py
@@ -5,7 +5,7 @@ try:
 except ImportError:
     from sqlplain.namedtuple import namedtuple
 from sqlplain.uri import URI
-from sqlplain.sql_support import qmark2pyformat
+from sqlplain.sql_support import get_args_templ
 from decorator import decorator
 
 @decorator
@@ -166,12 +166,13 @@ class LazyConnection(object):
                     lst.append(a)
             args = tuple(lst)
                               
-        if self.driver.paramstyle == 'pyformat':
-            qmarks, templ = qmark2pyformat(templ) # cached
-            if qmarks != len(args): # especially useful for mssql
-                raise TypeError("Expected %d arguments, got %d: %s" % (
-                    qmarks, len(args), args))
-        
+        if self.driver.placeholder: # the template has to be interpolated
+            argnames, templ = get_args_templ(templ, self.placeholder) # cached
+            if len(argnames) != len(args): # especially useful for mssql
+                raise TypeError(
+                    "Expected %d arguments (%s), got %d (%s)" %
+                    (len(argnames), ', '.join(argnames), len(args), args))
+ 
         descr, res = self._raw_execute(templ, args)
         if scalar: # you expect a scalar result
             if not res:
diff --git a/sqlplain/doc/doc.py b/sqlplain/doc/doc.py
index b091697..67e3a43 100644
--- a/sqlplain/doc/doc.py
+++ b/sqlplain/doc/doc.py
@@ -97,7 +97,8 @@ Asimov. That can be done with the following code:
 
  >> from sqlplain import lazyconnect
  >> bookdb = lazyconnect('mssql://pyadmin:secret@localhost/bookdb')
- >> bookdb.execute("SELECT * FROM book WHERE author LIKE ?", ('%Asimov%',))
+ >> bookdb.execute("SELECT * FROM book WHERE author LIKE :author",
+                   ('%Asimov%',))
 
 Here is the explanation. The first line import the ``LazyConnection``
 class from ``sqlplain``: instances of ``LazyConnection`` are *lazy
@@ -138,13 +139,13 @@ ships with its own version of namedtuples (I have just copied Raymond
 Hettinger's recipe from the Python Cookbook site) which is used if
 you are running an early version of Python.
 
-An important thing to notice is that ``sqlplain`` uses the so-called
-qmark param-style, i.e. the place holder for parametric queries is
-the question mark ``?`` for *all* supported database drivers, even
-if the underlying low level driver uses the pyformat param-style
-(both pymssql and psycogpg uses the pyformat param-style, which is
+An important thing to notice is that ``sqlplain`` uses named arguments
+for *all* supported database drivers, even
+if the underlying low level driver uses qmark paramstyle (like SQLite)
+or (py)format paramstyle
+(like pymssql and psycogpg). BTW, the (py)format paramstyle, is really
 a terrible choice, since it collides for the usage of ``%s`` in Python
-string templates).
+string templates :-(.
 
 The ``execute`` method is smart enough: if you run it again,
 the previously instantiated DB API2 connection and cursors are
@@ -169,7 +170,7 @@ a list of named tuples, it returns a number instead:
 
 .. code-block:: python
 
- >> bookdb.execute("UPDATE book SET author=? WHERE author like ?",
+>> bookdb.execute("UPDATE book SET author=:a WHERE author like :b",
      ('Isaac Asimov', '%Asimov%'))
  2
 
@@ -451,7 +452,7 @@ operation.
 
 If you want to insert a line at the time, you can do so by using the
 low level mechanism (
-``conn.execute("INSERT INTO mytable VALUES (?, ?, ?)", (r1, r2, r3))``)
+``conn.execute("INSERT INTO mytable VALUES (:1, :2, :3)", (r1, r2, r3))``)
 or by using the high level `table framework`_.
 
 .. _table framework: tables.html
@@ -492,14 +493,14 @@ Here are a few examples:
  
  >> help(get_titles)
  sqlquery(conn, arg1)
-     SELECT title FROM book WHERE author=?
+ SELECT title FROM book WHERE author=:a
  
  >> help(set_uppercase_titles)
  Help on function sqlquery in module queries:
  
- sqlquery(conn, arg1, arg2)
+ sqlquery(conn, author, pubdate)
      UPDATE book SET title=upper(title)
-     WHERE author like ? AND pubdate=?
+     WHERE author like :author AND pubdate=:pubdate
 
 By default all the functions generated by ``do`` have the name
 ``sqlquery``, but is possible to specify a different name; it
diff --git a/sqlplain/sql_support.py b/sqlplain/sql_support.py
index aa4f096..1f5cf04 100644
--- a/sqlplain/sql_support.py
+++ b/sqlplain/sql_support.py
@@ -1,56 +1,68 @@
 import re, inspect
 from decorator import FunctionMaker
 
-STRING_OR_COMMENT = re.compile(r"('[^']*'|--.*\n)")
+class _SymbolReplacer(object):
+    """
+    A small internal class to parse SQL templates with names arguments.
+    Returns the names of the arguments and the template interpolated with
+    a placeholder. Used by get_args_templ.
+    """
+    STRING_OR_COMMENT = re.compile(r"'[^']*'|--.*\n")
+    SYMBOL = re.compile(r":([a-zA-Z]\w*)")
+    
+    def __init__(self, placeholder):
+        self.placeholder = placeholder
+        self.replaced = []
+        self.found = set()
+
+    def get_args_templ(self, templ):
+        argnames = []
+        def repl(mo):
+            argname = mo.group(1)
+            if argname in argnames:
+                raise NameError('Duplicate argument %r in SQL template'
+                                % argname)
+                argnames.append(argname)
+            return self.placeholder or mo.group()
+        out = []
+        for i, chunk in enumerate(self.STRING_OR_COMMENT.split(templ)):
+            if i % 2 == 0: # real sql code
+                chunk = self.SYMBOL.sub(repl, chunk)
+            out.append(chunk)
+        return argnames, ''.join(out)
 
 templ_cache = {}
 
 # used in .execute
-def qmark2pyformat(templ):
+def get_args_templ(templ, repl=None):
     # this is small hack instead of a full featured SQL parser
     """
-    Take a SQL template and replace question marks with pyformat-style
-    placeholders (%s), except in strings and comments. Return the number
-    of replaced qmarks and the new template. The results are cached.
-    """
-    if templ in templ_cache:
-        return templ_cache[templ]
-    qmarks = 0
-    out = []
-    for i, chunk in enumerate(STRING_OR_COMMENT.split(templ)):
-        if i % 2 == 0: # real sql code
-            qmarks += chunk.count('?')
-            out.append(chunk.replace('?', '%s'))
-        else: # string or comment
-            out.append(chunk)
-    new_templ = ''.join(out)
-    templ_cache[templ] = qmarks, new_templ
-    return qmarks, new_templ
+    Take a SQL template and replace named arguments with the repl, except
+    in strings and comments. Return the replaced arguments and the new
+    template. The results are cached.
 
-# used in 'do' queries
-def extract_argnames(templ):
-    '''
-    Given a template with question marks placeholders, returns
-    a list of arguments of the form ['arg1', ..., 'argN'] where
-    N is the number of question marks.
-    '''
-    qmarks = 0
-    for i, chunk in enumerate(STRING_OR_COMMENT.split(templ)):
-        if i % 2 == 0: # real sql code
-            qmarks += chunk.count('?')
-    return ['arg%d' % i for i in range(1, qmarks + 1)]
+    >>> args, templ = get_args_templ('INSERT INTO book (:title, :author)')
+    >>> print args
+    ['title', 'author']
+    >>> print templ
+    INSERT INTO book (:title, :author)
+    >>> print get_args_templ('INSERT INTO book (:title, :author)', '?')[1]
+    INSERT INTO book (?, ?)
+    """
+    if (templ, repl) in templ_cache:
+        return templ_cache[templ, repl]
+    argnames, new_templ = _SymbolReplacer(repl).get_args_templ(templ)
+    templ_cache[templ, repl] = argnames, new_templ
+    return argnames, new_templ
             
-def do(templ, name='sqlquery', argnames=None, defaults=None, scalar=False,
-       ntuple=None):
+def do(templ, name='sqlquery', defaults=None, scalar=False, ntuple=None):
     """
     Compile a SQL query template down to a Python function with attributes
-    __source__, argnames defaults, scalar, ntuple. argnames is a comma
-    separated string of names, whereas defaults is a tuple.
+    __source__, argnames defaults, scalar, ntuple. defaults is a tuple.
     """
-    if argnames is None:
-        argnames = ', '.join(extract_argnames(templ))
-        if argnames:
-            argnames += ','
+    argnames = ', '.join(get_args_templ(templ)[0])
+    if argnames:
+        argnames += ','
     src = '''def %(name)s(conn, %(argnames)s):
     return conn.execute(templ, (%(argnames)s), scalar=scalar, ntuple=ntuple)
     ''' % locals()
@@ -73,3 +85,6 @@ def spec(fn, clause, argnames=None, defaults=None, ntuple=None):
               defaults=defaults or fn.defaults, 
               scalar=fn.scalar, ntuple=ntuple or fn.ntuple)
     
+
+if __name__ == '__main__':
+    import doctest; doctest.testmod()
diff --git a/sqlplain/uri.py b/sqlplain/uri.py
index 23712a8..46a6337 100644
--- a/sqlplain/uri.py
+++ b/sqlplain/uri.py
@@ -87,10 +87,17 @@ class URI(object):
         dbtype = self.dbtype
         driver = imp('sqlplain.%s_support' % dbtype)
         driver_util = imp('sqlplain.%s_util' % dbtype)
-        # dynamically populate thw 'util' module with the driver-specific func
+        # dynamically populate the 'util' module with the driver-specific func
         for name, value in vars(driver_util).iteritems():
             if name.endswith(dbtype):
                 setattr(util, name, value)
+        # set the placeholder according to the paramstyle
+        if driver.paramstyle == 'qmark':
+            driver.placeholder = '?'
+        elif driver.paramstyle in ('format', 'pyformat'):
+            driver.placeholder = '%s'
+        else:
+            driver.placeholder = None
         return driver
     
     def get_driver_connect_params(self):
diff --git a/sqlplain/util.py b/sqlplain/util.py
index f38d3c7..6a727e0 100644
--- a/sqlplain/util.py
+++ b/sqlplain/util.py
@@ -158,12 +158,7 @@ def copy_table(conn, src, dest, force=False):
 
 def truncate_table(conn, tname):
     if conn.dbtype == 'sqlite': # TRUNCATE is not supported right now
-        #conn.execute('PRAGMA synchronous = OFF')
-        try:
-            return conn.execute('DELETE FROM %s' % tname)
-        finally:
-            pass
-            #conn.execute('PRAGMA synchronous = ON')
+        return conn.execute('DELETE FROM %s' % tname)
     else:
         return conn.execute('TRUNCATE TABLE %s' % tname)