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

[Luke Kenneth Casson Leighton]

On Sun, Feb 02, 2003 at 02:30:37PM -0600, Ian Bicking wrote:

> On Sun, 2003-02-02 at 09:51, Kevin Jacobs wrote: 
> > > also for some of the background please see the advogato article
> > > http://advogato.org/article/600.html particularly the link to
> > > jmg's code which contains some very simple to use but obscure 
> > > coding methods to wrap SQL databases in objects.
> > 
> > We (my company) and many others have their own SQL generation and OR mapping
> > implementations that are considerably more advanced.  Some (like mine) are
> > in the process of transitioning to an open source development model, while
> > some are already freely available (see http://colorstudy.com/software/SQLObject

oo!  exciting code!

funnily enough andy dustman (mysqldb) has done a similar bit 
of coding to SQLbuilder.

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.

  [... 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 like the per-class handling of database modules.

  it allows for a much cleaner way of doing "odd" things
  e.g. the get insert ID which is always weird.

  in MS-SQL you have to call:

  "SELECT IDENT_CURRENT('tablename') AS id"

  in a similar fashion to the sqlbuilder 0.2 PostgreSQL's
  "SELECT nextval('tablename')"

- 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 do have a confession to make: i got the above completely
  wrong in one revision of some MS-SQL / MySQL compatible
  application i was developing, and because the other DB developer
  (visual basic.  yukk!) was belgian it caused an absolute riot
  when i made a change to the above EOL replacements, due to
  language / miscommunications.

  what i had done was to substitute text "\n" for "\\\\n" which
  worked absolutely fine for me because i was correctly converting
  back again: it failed miserably for the windows VB/DB developer
  of course...

  BEWARE THE EOL SQL strings!  ROLL ON mime-encoding! :)

l.

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.