r5567 - trunk/gnue-common/src/datasources/drivers/DBSIG2

[reinhard]

Author: reinhard
Date: 2004-03-29 15:23:32 -0600 (Mon, 29 Mar 2004)
New Revision: 5567

Modified:
   trunk/gnue-common/src/datasources/drivers/DBSIG2/Connection.py
Log:
Added an option to call SQL statements with parameters.


Modified: trunk/gnue-common/src/datasources/drivers/DBSIG2/Connection.py
===================================================================
--- trunk/gnue-common/src/datasources/drivers/DBSIG2/Connection.py      
2004-03-29 21:17:00 UTC (rev 5566)
+++ trunk/gnue-common/src/datasources/drivers/DBSIG2/Connection.py      
2004-03-29 21:23:32 UTC (rev 5567)
@@ -40,9 +40,28 @@
 from gnue.common.apps import GDebug
 import string
 import sys
+import mx.DateTime
 
-class Connection(BaseConnection):
+class Connection (BaseConnection):
+  """
+  The base class for all drivers that use DBSIG2 compatible modules. All
+  these drivers I{must} subclass this class.
 
+  Descendants I{must} override the following class variables:
+
+  @param _driver: the loaded Python module of the DBSIG2 driver
+
+  Descendants I{may} override the following class variables:
+
+  @param _boolean_false: Value to post to the database for boolean FALSE
+      (defaults to '0')
+  @param _boolean_true: Value to post to the database for boolean TRUE
+      (defaults to '1')
+  """
+  _driver = None                        # DBSIG2 compatible driver module
+  _boolean_false = '0'                  # value to pass for boolean FALSE
+  _boolean_true  = '1'                  # value to pass for boolean TRUE
+
   # This should be over-ridden only if driver needs more than user/pass
   def getLoginFields(self):
     return [['_username', _('User Name'),0],['_password', _('Password'),1]]
@@ -83,20 +102,173 @@
     pass
 
 
-  #
-  # Extensions
-  #
+  # ===========================================================================
+  # SQL statement handling
+  # ===========================================================================
 
+  # ---------------------------------------------------------------------------
+  # Convert type into what the DBSIG2 driver wants as parameter
+  # ---------------------------------------------------------------------------
+
+  def _makeparam (self, value):
+    """
+    Convert any given value into the datatype that must be passed as parameter
+    to the DBSIG2 cursor.execute() function.
+
+    Descendants may override this function to to different type conversions.
+    """
+    if isinstance (value, UnicodeType):
+      # Unicode: return encoded string
+      return value.encode (self._encoding)
+
+    elif sys.hexversion < 0x02030000 and isinstance (value, IntType):
+      # For Python < 2.3, IntType is used for booleans. Some datbases (e.g.
+      # postgres) want boolean 0 and 1 values as strings.
+      # Can be removed as soon as we require Python 2.3
+      return str (value)
+
+    elif isinstance (value, FloatType):
+      # Some databases (especially postgres) want floating point numbers
+      # passed as strings
+      return str (value)
+
+    elif sys.hexversion >= 0x02030000 and isinstance (value, BooleanType):
+      # Booleans (Python 2.3 and later)
+      if value:
+        return self._boolean_true
+      else:
+        return self._boolean_false
+
+    elif isinstance (value, mx.DateTime.DateTimeType):
+      # mx.DateTime
+      return self._driver.Timestamp (value.year, value.month, value.day,
+                                     value.hour, value.minute, value.second)
+    else:
+      # Strings, Integers
+      return value
+
+  # ---------------------------------------------------------------------------
+  # Change SQL statement and parameters to different paramstyles
+  # ---------------------------------------------------------------------------
+
+  # All these functions receive the statement and the params in pyformat style
+  # and convert to whatever style is needed (according to function name).
+  # One of these functions get called in makecursor.
+
+  def __param_qmark (self, statement, parameters):
+    s = statement
+    l = []
+    while True:
+      start = string.find (s, '%(')
+      if start == -1:
+        break
+      end = string.find (s, ')s', start)
+      if end == -1:
+        break
+      key = s [start+2:end]
+      s = s [:start] + '?' + s [end+2:]
+      l.append (parameters [key])
+    return (s, l)
+
+  def __param_numeric (self, statement, parameters):
+    s = statement
+    l = []
+    i = 0
+    while True:
+      start = string.find (s, '%(')
+      if start == -1:
+        break
+      end = string.find (s, ')s', start)
+      if end == -1:
+        break
+      i += 1
+      key = s [start+2:end]
+      s = s [:start] + (':%d' % i) + s [end+2:]
+      l.append (parameters [key])
+    return (s, l)
+
+  def __param_named (self, statement, parameters):
+    s = statement
+    while True:
+      start = string.find (s, '%(')
+      if start == -1:
+        break
+      end = string.find (s, ')s', start)
+      if end == -1:
+        break
+      key = s [start+2:end]
+      s = s [:start] + (':%s' % key) + s [end+2:]
+    return (s, parameters)
+
+  def __param_format (self, statement, parameters):
+    s = statement
+    l = []
+    while True:
+      start = string.find (s, '%(')
+      if start == -1:
+        break
+      end = string.find (s, ')s', start)
+      if end == -1:
+        break
+      key = s [start+2:end]
+      s = s [:start] + '%s' + s [end+2:]
+      l.append (parameters [key])
+    return (s, l)
+
+  # ---------------------------------------------------------------------------
   # Create a new DBSIG2 cursor object and execute the given SQL statement
-  # Statement can be a normal string or a unicode string
-  def makecursor (self, statement):
+  # ---------------------------------------------------------------------------
+
+  def makecursor (self, statement, parameters = None):
+    """
+    Create a new DBSIG2 cursor object and execute the given SQL statement.
+
+    @param statement: The SQL statement as either 8-bit or unicode string. Can
+        contain %(name)s style placeholders for parameters.
+    @param parameters: A dictionary with the parameter values. The values of
+        the dictionary can be 8-bit strings, unicode strings, integer or
+        floating point numbers, booleans or mx.DateTime values.
+    """
+    checktype (statement, [StringType, UnicodeType])
+    checktype (parameters, [DictionaryType, NoneType])
+    if parameters:
+      for (parameters_key, parameters_value) in parameters.items ():
+        checktype (parameters_key, [StringType, UnicodeType])
+        # checktype (parameters_value, .....) -- too many valid types :-)
+
+    # Convert to encoded string for database
     if isinstance (statement, UnicodeType):
       s = statement.encode (self._encoding)
     else:
       s = statement
+
+    if parameters:
+      # convert parameter dictionary to encoded strings
+      p = {}
+      for (key, value) in parameters.items ():
+        if isinstance (key, UnicodeType):
+          k = key.encode (self._encoding)
+        else:
+          k = key
+        p [k] = self._makeparam (value)
+
+      # Convert parameters into correct style
+      paramstyle = self._driver.paramstyle
+      if paramstyle != 'pyformat':
+        (s, p) = self.getattr ('_Connection__param_' + paramstyle) (s, p)
+
+    else:
+      p = None
+
+    gDebug (1, "DBSIG2 Statement: %s" % s)
+    gDebug (1, "DBSIG2 Parameters: %s" % p)
+    # Create DBSIG2 cursor and execute statement
     try:
       cursor = self.native.cursor ()
-      cursor.execute (s)
+      if p:
+        cursor.execute (s, p)
+      else:
+        cursor.execute (s)
     except:
       (exc_type, exc_value, exc_traceback) = sys.exc_info ()
       try:
@@ -108,9 +280,9 @@
 
   # Execute the given SQL statement and return the result matrix
   # Statement can be a normal string or a unicode string
-  def sql (self, statement):
+  def sql (self, statement, parameters = None):
     result = None
-    cursor = self.makecursor (statement)
+    cursor = self.makecursor (statement, parameters)
     try:
       # This generates an exception if the statement didn't generate a
       # resultset
@@ -127,8 +299,8 @@
   # Execute the given SQL statement that is expected to return a single value
   # If the query returns nothing, None is returned
   # Statement can be a normal string or a unicode string
-  def sql1 (self, statement):
-    cursor = self.makecursor (statement)
+  def sql1 (self, statement, parameters = None):
+    cursor = self.makecursor (statement, parameters)
     try:
       result = cursor.fetchone ()
     finally: