Improved the introspection support for sqlplain

6 files changed, 37 insertions, 6 deletions

diff --git a/sqlplain/connection.py b/sqlplain/connection.py
index 81c2f28..857beeb 100644
--- a/sqlplain/connection.py
+++ b/sqlplain/connection.py
@@ -173,8 +173,8 @@ class LazyConnection(object):
             argnames, templ = get_args_templ(templ, self.driver.placeholder)
             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))
+         "TypeError when executing %s\nExpected %d arguments (%s), got %d %s" %
+         (templ, len(argnames), ', '.join(argnames), len(args), args))
         descr, res = self._raw_execute(templ, args)
         cursor = self._storage.curs # needed to make the reset work
         if self.chatty:
diff --git a/sqlplain/mssql_support.py b/sqlplain/mssql_support.py
index 805246d..650b773 100644
--- a/sqlplain/mssql_support.py
+++ b/sqlplain/mssql_support.py
@@ -9,6 +9,8 @@ ISOLATION_LEVELS = (
 
 placeholder = '%s'
 
+CODEMAP = {}
+
 class Connection(object):
 
     def __init__(self, cnx, isolation_level=None):
diff --git a/sqlplain/postgres_support.py b/sqlplain/postgres_support.py
index 507fc29..527203a 100644
--- a/sqlplain/postgres_support.py
+++ b/sqlplain/postgres_support.py
@@ -8,6 +8,13 @@ placeholder = '%s'
 # READ_COMMITTED = READ_UNCOMMITTED = 1
 # REPEATABLE_READ = SERIALIZABLE = 2
 
+CODEMAP = {
+    1082: 'DATE',
+    23: 'INTEGER',
+    701: 'FLOAT',
+    1043: 'VARCHAR'
+    }
+
 def connect(params, isolation_level=None, **kw):
     user, pwd, host, port, db = params
     port = port or 5432
diff --git a/sqlplain/sqlite_support.py b/sqlplain/sqlite_support.py
index d556456..912bccf 100644
--- a/sqlplain/sqlite_support.py
+++ b/sqlplain/sqlite_support.py
@@ -7,6 +7,8 @@ ISOLATION_LEVELS = (None, "", "DEFERRED", "IMMEDIATE", "EXCLUSIVE")
 
 placeholder = '?'
 
+CODEMAP = {}
+
 def connect(fname, isolation_level=None, **kw):
     dbapi2.register_converter('datetime', dbapi2.converters['TIMESTAMP'])
     return dbapi2.connect(
diff --git a/sqlplain/uri.py b/sqlplain/uri.py
index 3a2fedb..731ae8f 100644
--- a/sqlplain/uri.py
+++ b/sqlplain/uri.py
@@ -7,6 +7,7 @@ import os
 from sqlplain.configurator import configurator
 
 SUPPORTED_DBTYPES = 'mssql', 'postgres', 'sqlite'
+CODEMAP = {} # will be filled with the codemaps for the various drivers
 
 def imp(mod):
     return __import__(mod, globals(), locals(), [''])
@@ -86,6 +87,7 @@ class URI(object):
         from sqlplain import util
         dbtype = self.dbtype
         driver = imp('sqlplain.%s_support' % dbtype)
+        CODEMAP[dbtype] = driver.CODEMAP
         driver_util = imp('sqlplain.%s_util' % dbtype)
         # dynamically populate the 'util' module with the driver-specific func
         for name, value in vars(driver_util).iteritems():
diff --git a/sqlplain/util.py b/sqlplain/util.py
index 00d6309..72129ec 100644
--- a/sqlplain/util.py
+++ b/sqlplain/util.py
@@ -3,7 +3,7 @@ Notice: create_db and drop_db are not transactional.
 """
 
 import os, sys, re, subprocess, tempfile
-from sqlplain.uri import URI
+from sqlplain.uri import URI, CODEMAP
 from sqlplain import connect, do
 from sqlplain.connection import Transaction
 from sqlplain.namedtuple import namedtuple
@@ -187,7 +187,8 @@ def insert_rows(conn, tname, rows):
         row = it.next()
     except StopIteration: # nothing to insert
         return n
-    templ = 'INSERT INTO %s VALUES (%s)' % (tname, ', '.join('?'*len(row)))
+    dummies = [':%s' % (i + 1) for i in range(len(row))]
+    templ = 'INSERT INTO %s VALUES (%s)' % (tname, ', '.join(dummies))
     n = conn.execute(templ, row)
     for row in it:
         n += conn.execute(templ, row)
@@ -225,7 +226,24 @@ def exists_table(conn, tname, schema=None):
 def get_descr(conn, tname):
     "Return the DB API 2 description as a list of rows"
     return conn.execute('SELECT * FROM %s WHERE 1=0;' % tname).descr
-    
+
+def inspect_columns(conn, tname, tuples=False):
+    """
+    Return a list of strings "fieldname fieldtype(size)" or of tuples
+    (fieldname, fieldtype, size).
+    """
+    codemap = CODEMAP[conn.dbtype]
+    ls = []
+    for x in get_descr(conn, tname):
+        fname, ftype, fsize = x.name, codemap[x.type_code], x.internal_size 
+        if tuples:
+            ls.append((fname, ftype, fsize))
+        else:
+            ls.append('%s %s%s' %
+                      (fname, ftype, '(%s)' % fsize
+                       if ftype=='VARCHAR' and fsize>0 else ''))
+    return ls
+
 def get_fields(conn, tname):
     """
     Return the names of the columns of a table (must be ASCII).
@@ -260,7 +278,7 @@ def create_schema(db, schema, force=False, schema_dir=None):
     """
     Create the specified schema. If the schema exists already
     an error is raised, unless force is True: in that case the schema
-    is dropped and recreated.
+    is dropped and recreated. We are left in the created schema.
     """
     if force and exists_schema(db, schema):        
         drop_schema(db, schema)