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