sqlobject-discuss

[SQLObject] DB API

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

Helo, Ian!

   Why use sqlStringReplace and _[lL]ikeQuoted? Why not import and use
quoting/escaping functions from low-level DB API modules? At least where
they are avalable at all...

   Why not import DB API exceptions from those modules and reexport them
as SQLObject exceptions?

   Or use format/pyformat/etc paramstyle from DB API 2.

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

[SQLObject] Re: DB API

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

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.

   Let's me elaborate. Currently SQLObject generates
SELECT/INSERT/UPDATE/etc queries as strings. There is a number of
problems with this approach.

   Problem number one. SQLObject tries to quote/escape strings using its
internal knowledge of quoting rules. This is very fragile as SQLObject
has to know quoting rules for all supported database.
   It is possible to releave the pain, at least partially, by using
quote/escape functions from low-level modules. These functions are not
part of DB API (more on this later), but most modules provide them. Just
import them and use them.
   from psycopg import QuotedString
or
   from MySQLdb import escape_string
Now instead of encapsulating knowledge of each database SQLObject will
encapsulate knowledge of each DB API driver.

   But still this is not a complete solution. Some databases do not
allow to quote special characters at all. Just do not allow. There is no
way one can generate a correct query string that incorporates a special
character. I heard that Sybase/MSSQL and Oracle are of this class. Their
DB API drivers do not have quoting/escaping functions.
   There is only one way a program can send a special character to these
databases - using query parameters:
   cursor.execute("INSERT INTO atable (afield) VALUES (:1)", ("astring",))

   This is the best way for SQLObject 'cause after implementing this
SQLObject will not need to know any quoting rules, and will be ready to
support more databases.
   Different paramstyles will be handled by any paramstyle conversion
modules - there is a number of them on the Net.

   But this, probably, requires some architectural changes in the
SQLObject.
   Well, what do you think of this?

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

Re: [SQLObject] Re: DB API

[Max I. <ma...@uc...>]

Oleg Broytmann wrote:

>    But this, probably, requires some architectural changes in the
> SQLObject.
>    Well, what do you think of this?

I'd say it's a good idea.

If Ian won't object to the idea per se, you could support your words 
with a patch. ;-)

[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.

[SQLObject] Re: DB API

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

On Wed, Nov 24, 2004 at 02:37:46PM +0300, Oleg Broytmann wrote:
>    a module from Dennis Otkidach - here
> http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/278612

   Dennis' module does not use any style at all - instead, it just
construct templates from a list of strings and parameters.

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

   Of course I meant

return cursor.execute(self.convert_to_dbapi_style(query.template), query.paramlist)

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

Re: [SQLObject] Re: DB API

[Ian B. <ia...@co...>]

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.
> 
>    But this, probably, requires some architectural changes in the
> SQLObject.
>    Well, what do you think of this?

I generally thing this is the right thing to do.  There are a couple 
reasons it wasn't that way originally:

* I wanted to be able to override SQL representation, with what is now 
__sqlrepr__.
* Constructing SQL from several sources is difficult when you have to 
worry about the order of parameters.
* There's no standard paramstyle.

Going backwards through the list...

The standard paramstyle probably isn't that big a deal.  %s is an easy 
implementation (just do query % ('?', '?', ...) or whatever); pyformat 
doesn't imply SQL parsing in the way other formats (like ?) do; you have 
to escape any %'s.  The other way would be the split strings.  I think 
named and numeric both imply SQL parsing, and as such are too difficult. 
  When this last came up on DB-SIG, I gave several examples where 
parsing would be necessary:
   http://mail.python.org/pipermail/db-sig/2004-August/004165.html

And it is even worse than that, because aspects of the parsing are 
database specific (like string quoting), and we're back to where we 
started, but with more code.


The first two problems could be solved with a nice abstraction.  I'd 
like something like __sqlrepr__, but it could return the abstraction 
instead of a text SQL literal.  Maybe this query abstraction would 
contain the list of parameters, and a list of strings (where parameters 
go inbetween the strings).  There'd be a method to convert the query 
object to a SQL string with the appropriate paramstyle, and a list of 
parameters.  The query objects would also support concatenation. 
__sqlrepr__ would take a bit of thought, but if the method was present 
it should allow a parameter to insert new text and new parameters. 
E.g., a Point object might look like:

class Point:
   def __init__(self, x, y):
       self.x, self.y = x, y
   def __sqlrepr__(self):
       return ('(', ', ', ')'), (self.x, self.y)
       # or...?
       return '(%s, %s)', (self.x, self.y)

The first is hard to read, the second requires a standard simplistic 
paramstyle (like pyformat).  Potentially both could be supported.

All of this could be SQLObject-neutral.  I don't think it would be quite 
as intuitive as string substitution, but hopefully it could get close. 
Since people are frequently proposing these sorts of libraries (often 
under the guise of DB API 3), maybe it's all much harder than I realize. 
    But maybe

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

Re: [SQLObject] Re: DB API

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

On Mon, Nov 29, 2004 at 10:24:26AM -0600, Ian Bicking wrote:
> I generally thing this is the right thing to do.

   Now, when I've commited almost all patches related to inheritance, I
am going to work on it in another private branch.

> The first two problems could be solved with a nice abstraction.  I'd 
> like something like __sqlrepr__, but it could return the abstraction 
> instead of a text SQL literal.

   This is exactly what I've proposed. We agree on it. Fine!

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