Re: [SQLObject] ADO DB-API support?

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

On Tue, 2003-05-27 at 18:21, Magnus Lyck=E5 wrote:
> I doubt it :). The sqlite driver and the related PostgreSQL driver both
> look for an attribute called '_quote', which I suppose they use for the=
ir
> internal wrappers for SQL types that don't quite match SQL. (MONEY etc)
> That's where I got the idea, and someone on the db-sig list suggested
> that adding type information was needed as well. Perhaps I can at least
> convince Gerard H=E4ring to rename _quote to _dbapi_string in his drive=
rs?

I was thinking of changing the magic method I use to __sqlrepr__, which
seems like a good name to me.  Just having a single convention would be
a start.

Right now I think quoting would probably best be done like:

* Integers, floats, strings are all automatically quoted.  Maybe
mxDateTime and datetime objects too.  No hooks for these -- the
underlying C driver may want to handle these data structures on its own
anyway.
* Anything with a __sqlrepr__ method has that called, with no
arguments.  The result is expected to be fully quoted.
* If both those fail, then there's a function which has one last chance
to return a SQL representation on the object.  This would be for quoting
types that you couldn't add a __sqlrepr__ method to -- for instance, if
mxDateTime objects weren't automatically handled, you might handle them
here.  Usage something like:

import dbdriver
old_quote =3D dbdriver.quote
def quote(val):
    if type(val) is DateTimeType:
        return val.strftime("'%c'")
    else:
        return old_quote(val)

Maybe there's a better way to phrase this hook, but this might be
sufficient.  The last quoting technique would probably be the only way
to add your own quoting that was database-specific (as would be
necessary with Access and mxDateTime objects).  So maybe __sqlrepr__
should actually just be part of the standard quote function.

> Hopefully *someone* will like the idea. Maybe with a few more champions=
... :)
>=20
> A PEP might be a good idea. Another idea I had was to write some short
> generic DB-API tutorial that clarifies all the required features of
> the current DB-API spec. Don't have time for either right now though.
> Both bookkeeping and paying customers need priority handling... :)

Yeah, I hear that.  I have to cut myself off from this stuff every so
often.

> >I would still disagree, at least for SQLObject.  Or rather, I would sa=
y
> >that separate parameters is a decision that in most places is delayed
> >quite a ways (until your connection is just about to send the SQL), so
> >it's not a big deal either way.
>=20
> My though was to allow .select("name=3D?", ("Guido's",)) as an alternat=
ive
> to .select("name=3D'Guido''s'")

Yes, if you didn't use literals (at least string literals) in the code
it should be okay. =20

> >Well, for instance, MySQL allows strings like 'that\'s it', which woul=
d
> >break this.  But it also allows 'that''s it', so maybe people will jus=
t
> >have to use the one.
>=20
> sql.replace(r"\'", "''") before the re would handle that otherwise.
> As a coder I think it's best to follow SQL given a choice, so I'd
> suggest using '' rather than \'.

The replace doesn't work for '\\', though. =20

> In the worst case I can just write a small C (or Pyrex?) routine to
> do this without re. After all, there isn't a lot of SQLism in this,
> is there. A ? should be replaced with ...something... unless it's
> in a quote. A quote started with " should end with " unless the
> preceeding character was \, and a quote started with ' should end with
> ' unless the preceeding character was \. 'That''s' can be seen as two
> quoted areas from a ? replacement perspective.
>=20
> Is there a flaw in that strategy?

Parsing string literals with regular expressions never really works, at
least when they use backslash quoting.  Python should have a standard
function to do this, written in C (maybe that's one of the things
kjbuckets does).  Or Pyrex.  Anyway, lacking that, writing such a thing
could be useful.

If the function works well enough, maybe it would be an incentive for
packages like MySQLdb to use ? placeholders as well.  Ultimately that's
the solution that would work best for DBAPI as a whole.

Maybe create a DBAPI module again (didn't DBAPI 1 have a common module?)
-- put this function in there, the quote function, some common
exceptions for everyone to use.  It'd be DBAPI 3.0, or 2.1... anyway,
that's a lot of the biggest problems people seem to have.

  Ian