[SQLObject] Re: DB API

[Oleg B. <ph...@ph...>]

On Tue, Nov 23, 2004 at 06:36:28PM +0300, Oleg Broytmann wrote:
> On Mon, Nov 22, 2004 at 03:14:41PM +0300, Oleg Broytmann wrote:
> >    Why use sqlStringReplace and _[lL]ikeQuoted?...
> >    ...use format/pyformat/etc paramstyle from DB API 2.
> 
>    cursor.execute("INSERT INTO atable (afield) VALUES (:1)", ("astring",))

   So, my plan is as follows. First, SQLObject should adopt a module to
convert between different paramstyles, or grow its own. Available modules
are:
   a module from Dennis Otkidach - here
http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/278612
   or there:
http://cvs.sourceforge.net/viewcvs.py/ppa/misc/DBSingleStyle.py?rev=HEAD&content-type=text/vnd.viewcvs-markup
   The license is pythonic. Another module is paramstyles.py from PyDAL
(pydal.sf.net). The licesne is BSD-like.

   Then we are to choose a single paramstyle for the entire SQLObject. I
personnaly prefer named and numeric.

   And finally SQLObject will be rewritten to generate templates (using
our chosen paramstyle) instead of query strings. I am not sure yet how
to do it right. I think about the following path. Stop generating
strings. Queries have to be objects that contains a template and a list
of parameters:

class SQLQuery:
   def __init__(self, template, paramlist=None):
      self.template = template
      self.paramlist = paramlist

   A query can be executed by a DB connection: dbConn.query(SQLQuery(t, p)).
The _executeRetry() method will be implemented as follows:

   def _executeRetry(self, conn, cursor, query):
      return cursor.execute(query.template, query.paramlist)

   All __sqlrepr__ ethods should return SQLQuery instances, not strings.
Something like this (very simplified):

class Insert:
   def __init__(self, table, valueDict):
      self.table = table
      self.valueDict = valueDict

   def __sqlrepr__(self): # no db parameter required
      fields = self.valueDict.items()
      names = ", ".join([f[0] for f in fields])
      names_template = ", ".join([":%s" % f for f in names])
      template = "INSERT INTO %s (%s) VALUES (%s)" % (self.table, names, names_template)
      return SQLQuery(template, self.valueDict)

   self.valueDict is used for named paramstyle. For numeric paramstyle
it is

   def __sqlrepr__(self):
      fields = self.valueDict.items()
      names = ", ".join([f[0] for f in fields])
      names_template = ", ".join([":%d" % i for i in range(len(names))])
      values = [f[1] for f in fields]
      template = "INSERT INTO %s (%s) VALUES (%s)" % (self.table, names, names_template)
      return SQLQuery(template, values)

   Any words on this? Ian? Anyone?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.