Added create_table and various improvements

6 files changed, 181 insertions, 50 deletions

diff --git a/sqlplain/connection.py b/sqlplain/connection.py
index 566ea13..ec7e023 100644
--- a/sqlplain/connection.py
+++ b/sqlplain/connection.py
@@ -165,7 +165,10 @@ class LazyConnection(object):
         
         descr, res = self._execute(self._curs, templ, args)
         if scalar: # you expect a scalar result
-            if len(res) != 1 or len(res[0]) != 1:
+            if not res:
+                raise KeyError(
+                    "Missing record, QUERY WAS:%s%s\n" % (templ, args))
+            elif len(res) > 1:
                 raise ValueError(
                     "Expected to get a scalar result, got %s\nQUERY WAS:%s%s\n"
                     % (res, templ, args))
diff --git a/sqlplain/doc/doc.py b/sqlplain/doc/doc.py
index f21cc82..09d4f5b 100644
--- a/sqlplain/doc/doc.py
+++ b/sqlplain/doc/doc.py
@@ -349,13 +349,25 @@ very low in my list of priorities (I am in the camp of people who
 are against thread) and what it is there is the minimun I needed
 to do in order make my scripts work with the Paste server.
 
-Utilities
---------------------------------------------------------------
+sqlplain: utilities
+================================================================
 
 ``sqlplain`` is a very poor toolkit compared to other database toolkits;
 this is done on purpose. Nevertheless, it provides a few convenient
 functions to work with a database directly, collected in the ``util``
-module. They are the following::
+module. We can split these utilities in different classes:
+
+- introspection utilities, to extract information from the database;
+- management utilities, to create/drop database from scratch and to
+  create/drop/populate tables;
+
+Reflection facilities
+-----------------------------------------------------------------
+
+``exists_table``, ``get_descr``, ``get_fields``, ``get_kfields``
+and ``get_dfields``, 
+
+They are the following::
 
     openclose(uri, templ, *args, **kw):
 
@@ -372,6 +384,80 @@ features are likely to be enhanced in future versions). For the moment,
 the only things you can do is to introspect a table or a view and to
 return a named tuple with the names of the fields:
 
+Database management utilities
+--------------------------------------------------------------
+
+``sqlplain`` provides three utilities to create and to drop database in
+a database-independent way. Obviously, in order to take advantage of
+such facilities, you must connect to the database cluster as an user with
+sufficient permissions.
+
+``create_db(uri, force=False)`` creates the database specified by
+``uri`` and returns a (lazy) connection to it. If the database already
+exists, raises an error.  This behavior can be modified by passing the
+flag ``force=True``: in this case the pre-existing database (if any)
+is silently dropped and recreated.
+
+``drop_db(uri, force=False)`` drop an existing
+database; it raises an error if the database does not exists, unless
+the flag ``force=True`` is passed: in that case nothing is done if the
+database does not not exists.
+
+``make_db(uri, force=False, script_dir=None)`` is an extension of ``create_db``
+which also executes all the scripts contained in ``script_dir``. If no
+``script_dir`` is passed, it looks in the configuration file for a ``[dir]``
+section (if any) and executes the scripts in that directory (if any).
+
+
+Table management
+--------------------------------------------------------------
+
+- ``create_table(conn, tablename, field_descr, force=False)``
+
+>>> from sqlplain impor util
+>>> db = util.create_db('sqlite_test', force=True)
+>>> util.create_table(db, 'book',
+...                  ['title VARCHAR(128)', 'author VARCHAR(64)'])
+-1
+
+
+``sqlplain`` provides utilities to truncate (
+``truncate_table(conn, tablename)``) and to drop tables
+(``drop_table(conn, tablename)``), assuming you have the right
+permissions for those operations.
+
+Moreover it provides utilities to populate a table:
+
+- ``insert_rows(conn, tablename, rows)`` inserts a sequence of rows
+  (any iterable returning valid rows will do) into a table;
+
+- ``insert_file(conn, filename, tablename, sep=',')`` inserts the content
+  of a CSV file into a table.
+
+The difference between the two operations is that ``insert_file`` is orders
+of magnitude faster that ``insert_rows`` since it uses the underlying
+database mechanism for bulk inserting files, possibly disabling transactional
+safety. ``sqlplain`` comes with a test script in the tests directory
+(``test_million.py``) which tries to import a datafile with the two
+mechanism; if you run it, you may see how big is the performance
+difference on your platform. On my MacBook the difference is a factor
+of 60, i.e. an operation that would take 5 hours with ``insert_rows``
+is reduced to 5 minutes with ``insert_file``.
+
+Nevertheless, ``insert_rows`` is more convenient when you have small
+tables and when you are writing unit tests, therefore it makes sense
+for ``sqlplain`` to provide it.
+
+The problem of both ``insert_rows`` and ``insert_file`` is that you
+do not have line-by-line control of the operation, therefore a single
+ill-formed line in a million lines file will screw up the entire
+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))``)
+or by using the high level table framework discussed in the next section.
+
 sqlplain: extensions
 =================================================================
 
@@ -528,41 +614,6 @@ something more sophisticated on top of it, but for the moment it
 works well enough for my needs. Future versions of ``sqlplain``
 could offer additional functionality for generating SQL templates,
 or could not.
-
-Populating a table
---------------------------------------------------------------
-
-``sqlplain`` provides two utilities to populate a table:
-
-- ``insert_rows(conn, tablename, rows)`` inserts a sequence of rows
-  (any iterable returning valid rows will do) into a table;
-
-- ``insert_file(conn, filename, tablename, sep=',')`` inserts the content
-  of a CSV file into a table.
-
-The difference between the two operations is that ``insert_file`` is orders
-of magnitude faster that ``insert_rows`` since it uses the underlying
-database mechanism for bulk inserting files, possibly disabling transactional
-safety. ``sqlplain`` comes with a test script in the tests directory
-(``test_million.py``) which tries to import a datafile with the two
-mechanism; if you run it, you may see how big is the performance
-difference on your platform. On my MacBook the difference is a factor
-of 60, i.e. an operation that would take 5 hours with ``insert_rows``
-is reduced to 5 minutes with ``insert_file``.
-
-Nevertheless, ``insert_rows`` is more convenient when you have small
-tables and when you are writing unit tests, therefore it makes sense
-for ``sqlplain`` to provide it.
-
-The problem of both ``insert_rows`` and ``insert_file`` is that you
-do not have line-by-line control of the operation, therefore a single
-ill-formed line in a million lines file will screw up the entire
-operation.
-
-If you want to insert a line at the time, you can do so by using the
-low level mechanism (with a command like
-``conn.execute("INSERT INTO mytable VALUES (?, ?, ?)", (r1, r2, r3))``)
-or by using the high level table framework discussed in the next framework.
                                     
 The table framework
 ------------------------------------------------------------
diff --git a/sqlplain/sql_support.py b/sqlplain/sql_support.py
index cbe2234..b7bf82b 100644
--- a/sqlplain/sql_support.py
+++ b/sqlplain/sql_support.py
@@ -40,19 +40,23 @@ def extract_argnames(templ):
             qmarks += chunk.count('?')
     return ['arg%d' % i for i in range(1, qmarks + 1)]
             
-def do(templ, name='sqlquery', args=None, defaults=None, scalar=False):
+def do(templ, name='sqlquery', args=None, defaults=None, scalar=False,
+       ntuple=None):
     """
     Compile a SQL query template down to a Python function.
     """
     if args is None:
         args = ', '.join(extract_argnames(templ))
+        if args: args += ','
     src = '''def %(name)s(conn, %(args)s):
-    return conn.execute(templ, (%(args)s), scalar=scalar)''' % locals()
+    return conn.execute(templ, (%(args)s), scalar=scalar, ntuple=ntuple)
+    ''' % locals()
     fun = FunctionMaker(name=name, signature=args, defaults=defaults,
                         doc=templ)
     fn = fun.make(src, dict(templ=templ, scalar=scalar), addsource=True,
                     templ=templ)
-    comment = '# scalar = %s\n# templ=\n%s\n' % (scalar, '\n'.join(
+    comment = '# ntuple = %s\n# scalar = %s\n# templ=\n%s\n' % (
+        ntuple, scalar, '\n'.join(
         '## ' + line for line in templ.splitlines()))
     fn.__source__ = '%s\n%s' % (comment, fn.__source__)
     return fn
diff --git a/sqlplain/table.py b/sqlplain/table.py
index 6648ce8..67aa9df 100644
--- a/sqlplain/table.py
+++ b/sqlplain/table.py
@@ -1,5 +1,5 @@
 import sys, string
-from sqlplain import util
+from sqlplain import util, do
 from sqlplain.namedtuple import namedtuple
 from sqlplain.connection import connmethod
 
@@ -153,6 +153,11 @@ class DTable(object):
         fields = util.get_fields(conn, name)
         return cls.type(name, fields)(conn)
 
+    @classmethod
+    def create(cls, conn, name, fields, force=False):
+        util.create_table(conn, name, fields, force)
+        return cls.reflect(conn, name)
+    
     @connmethod
     def insert_row(conn, row, **kw):
         "Dynamically replaced in subclasses"
@@ -172,16 +177,20 @@ class DTable(object):
         self.tt # raise an AttributeError if not set correctly
         self.conn = conn
 
-    def select(self, clause=''):
+    def select(self, clause='', *args):
         "Select rows from the table"
         fields = ', '.join(self.tt._fields)
-        return self.conn.execute(
-            'SELECT %s FROM %s %s' % (fields, self.name, clause),
-            ntuple=self.tt)
+        templ = 'SELECT %s FROM %s %s' % (fields, self.name, clause)
+        if args:
+            return do(templ, ntuple=self.tt)(self.conn, templ, *args)
+        return self.conn.execute(templ, ntuple=self.tt)
 
     def delete(self, clause=''):
         "Delete rows from the table"
-        return self.conn.execute('DELETE FROM ' + self.name + ' ' + clause)
+        templ = 'DELETE FROM %s %s' % (self.name, clause)
+        if args:
+            return do(templ)(self.conn, templ, *args)
+        return self.conn.execute(templ)
 
     def truncate(self):
         "Truncate the table"
@@ -189,8 +198,10 @@ class DTable(object):
 
     def count(self, clause=''):
         "Count the number of rows satisfying the given clause"
-        return self.conn.execute(
-            'SELECT count(*) FROM %s %s' % (self.name, clause), scalar=True)
+        templ = 'SELECT COUNT(*) FROM %s %s' % (self.name, clause)
+        if args:
+            return do(templ)(self.conn, templ, *args)
+        return self.conn.execute(templ, scalar=True)
 
     def __len__(self):
         "Return the total number of rows in the table"
diff --git a/sqlplain/tests/test_qmark2pyformat.py b/sqlplain/tests/test_qmark2pyformat.py
new file mode 100644
index 0000000..e535503
--- /dev/null
+++ b/sqlplain/tests/test_qmark2pyformat.py
@@ -0,0 +1,53 @@
+from __future__ import with_statement
+from sqlplain.sql_support import qmark2pyformat, STRING_OR_COMMENT
+from ms.time_utils import Clock
+
+TEMPL = '''
+-- insert into covered
+INSERT INTO rhp_covered_product 
+  SELECT p.refdate, c.client_srs, c.client_id, p.pricer, ep.productcode, 
+  ep.clientcode, rmtck.covered_date FROM rhp_exportedproduct AS ep
+  INNER JOIN customer AS c
+  ON lower(ep.client) = c.client_srs
+  INNER JOIN rhp_product AS p
+  ON ep.productcode = p.productcode 
+  AND ep.refdate = p.refdate
+  AND p.refdate = ?
+  INNER JOIN (
+    SELECT client_id, productcode, covered_date 
+    FROM covered_by_nonrequested
+    
+    UNION 
+    
+    SELECT client_id, productcode , covered_date FROM 
+    covered_by_rhp_tickerlookup 
+    
+    ) AS rmtck    
+  ON c.client_id = rmtck.client_id
+  AND rmtck.productcode = p.productcode
+'''
+
+#TEMPL = 'select * from client where client=?'
+
+def write(dt):
+    print "Spent %s ms" % dt
+
+with Clock(write):
+    for i in xrange(100000):
+        qmark2pyformat(TEMPL)
+
+def qmark2pyformat(templ): # version without cache
+    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)
+    return qmarks, new_templ
+
+with Clock(write):
+    for i in xrange(100000):
+        qmark2pyformat(TEMPL)
diff --git a/sqlplain/util.py b/sqlplain/util.py
index 13dc163..fb6a033 100644
--- a/sqlplain/util.py
+++ b/sqlplain/util.py
@@ -84,6 +84,15 @@ def create_db(uri, force=False, scriptdir=None, **kw):
                 exec chunk.code in {}
     return db
 
+def create_table(conn, tname, fields, force=False):
+    """
+    Create a table. If the table already exists, raise an error, unless
+    force is True.
+    """
+    if exists_table(conn, tname) and force:
+        drop_table(conn, tname) # do not raise an error
+    return conn.execute('CREATE TABLE %s(%s)' % (tname, '\n  ,'.join(fields)))
+
 def drop_table(conn, tname, force=False):
     """
     Drop a table. If the table does not exist, raise an error, unless