[DB-SIG] Re: [Python-Dev] database APIs

[Ian Bicking]

On Monday, February 3, 2003, at 02:42 PM, Luke Kenneth Casson Leighton 
wrote:
> comments on sqlbuilder 0.2:
>
> - i like the try/except on locating database modules.
>
>   in pysqldb, the name of the database module (and the user/pass)
>   is specified in a per-application config file.
>
>   therefore the database application is expected to provide
>   certain functionality a la DB 2.0 API.  the problem comes
>   when that functionality doesn't exist: the pysqldb
>   classes have to deal with missing or different functionality
>   on a per-module basis.

I think the wrappers worked out quite well, and the resulting 
compatibility code can remain fairly short and isolated.  Well, there's 
only two databases supported, but that's at least 50% of the way to 
universal coverage :)

>   [... perhaps there should be a Python-SQL-DB 2.0 API,
>    or an ODBC-like converter API, to ensure that all SQL
>    databases look the same? ]

I don't know what that would look like.  I'm more confident of the 
utility and feasibility of creating a domain-specific wrapper to deal 
with database differences.  For instance, SQLObject's functionality (at 
least for now) requires only a small wrapper, mostly to handle the 
insert ID issue.  Layering abstractions can quickly become unwieldy, 
and I feel strongly that SQLObject (or any other similar system) is 
best written solely on the DB 2.0 API.

> - you may not be aware that MS-SQL has a different "escape"
>   system from MySQL (and probably PostgreSQL).
>
>   you might want to put in a per-class "string" handler
>   function.
>
>   the escape sequence for quotes within strings/text
>   is
>   	replace ("'", "\\'") in MySQL;
>   but is
>   	replace("'", "''") in MS-SQL (7.0 and 2000)
>
>   and end-of-line characters within strings/text is not
>   necessary (to my knowledge) in MySQL but
>   is
>   	replace("\n", "\\012") and replace("\r", "\\015")
> 	is needed in MS-SQL.

I've avoided database-specific SQL generation, but perhaps it will be 
necessary at one point.  On these counts MySQL supports '' and \012, so 
the more complete quoting that MS-SQL demands could be implemented 
globally.

> p.s. if you're interested in how i managed to get MS-SQL 2000
>      access from python on a unix system, the code's at
> 	 http://sf.net/projects/pyxsqmll.

Have you ever tried using Pyro (Python Remote Objects) or similar 
things for communicating over the wire?  I played around with similar 
stuff myself (though the database I was connecting too was ultimately 
too buggy), but later realized that I could have more easily exported 
those interfaces over the wire using a generic distributed-object 
system.

   Ian