Re: [SQLObject] ADO DB-API support?

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

On Wed, 2003-05-28 at 15:20, Magnus Lyck=E5 wrote:
> At 12:39 2003-05-28 -0500, Ian Bicking wrote:
> >I was thinking of changing the magic method I use to __sqlrepr__, whic=
h
> >seems like a good name to me.  Just having a single convention would b=
e
> >a start.
>=20
> Ok with me. It would be good if there was at least a name
> given as an option in the DB-API spec. This function obviously
> exist in a number of implementations, with different names.
>=20
> >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 ow=
n
> >anyway.
>=20
> Right.
>=20
> >* Anything with a __sqlrepr__ method has that called, with no
> >arguments.  The result is expected to be fully quoted.
>=20
> This is what I thought first, but there were some oppsition to
> the idea on the db-sig mailing list. Let's return to the date
> and Access. Let's say that you have your own date class, and you
> deliver the string "'1984-06-04'". This won't work on Access.
> If __sqlrepr__() had returned '1984-06-04' and another method
> __sqltype__ had returned 'DATE', then the driver could have
> known that it would do "'%s'" % __sqlrepr__() on sane platforms
> and "#%s#" % __sqlrepr__() on that warped MS platform.

Yes, I started thinking about this when I was partway through writing
down my thoughts.  Generally the place I am using this kind of
functionality is with various explicit literals, and SQLBuilder in
particular (e.g. SQLBuilder.func.NOW()).  In that case it wouldn't cause
too much of a problem that the constructed SQL was not
backend-specific.  I know I've this hook elsewhere, but I'm at a loss to
remember when.

If it's not considered the end-all of SQL construction and backend
abstraction, then I think it's still a useful hook.

> If the interface doesn't build the full SQL statement in the
> interface, but actually sends the parameters separately to
> the backend, you might end up with things like
>=20
> INSERT INTO T1 (C1) VALUES ('''that''''s''')
>=20
> That would be a bit sad... :(
>=20
> But still, it's a start. It's certainly reasonable that the
> result from __sqlrepr__ is passed in as is if there is no
> __sqltype__ attribute in the object. I think that __sqltype__
> is also a good idea though.

I think __sqltype__ seems a little awkward.  You have to agree on the
types (and type names) that the backend accepts, and that gets into a
whole discussion that seems rather endless ;)

But maybe it should be done in the default quote function, then it can
be overridden for weird databases.

> >* If both those fail, then there's a function which has one last chanc=
e
> >to return a SQL representation on the object.  This would be for quoti=
ng
> >types that you couldn't add a __sqlrepr__ method to -- for instance, i=
f
> >mxDateTime objects weren't automatically handled, you might handle the=
m
> >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)
>=20
> And then "dbdriver.quote =3D quote" or what?=20

Yes, I forgot to finish it with that.

> Do you register
> this? Why not just supply something like date.Format('%Y-%m-%d')
> instead of your plain date?

Date is a contrived example, since most drivers handle dates natively.=20
Maybe an arbitrary precision number would be a better example.

> But finally: "In case of doubt, refuse the temptation to guess."
> At least some drivers fall back on repr() in an else-statement.
> I only want a "raise TypeError" in the default case.

Yes, I definitely agree.  If you really want repr, you'd write something
like:

def quote(val):
    try:
        return old_quote(val)
    except TypeError:
        return repr(val)
dbdriver.quote =3D quote

But I definitely repr is bad by default.

> >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.
>=20
> But drivers that can talk to Access, such as mxODBC and adodbapi have
> no problem with this, since they just pass the unquoted date string to
> the backend and let the ODBC driver handle that. Remember? That's where
> we started. Generally, it's always possible to wrap object in a small
> class that just implements __init__(self, value) and __sqlrepr__(self),
> so I don't quite see the need for this quote function.

The quote function is potentially database specific.  After thinking
about it, this function should have the chance to get at the raw value
before __sqlrepr__ is called.

Hmmm... just looking at the psycopg type stuff.  It handles both SQL
generation and typecasting what comes from the database at the same
time, more or less.  Creation objects that come from the database should
also be handled, but I don't see any particular need to do those at
once.

Actually, maybe psycopg doesn't do this, but rather just calls
str()/__str__ (where we use __sqlrepr__) -- certainly a special method
is better, though.

> >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.
>=20
> Agreed. I think some drivers might be implemented entirely in
> C, and others want to be completely Python to be as portable as
> possible, so it might not be trivial to write a module that all
> will agree with...
>=20
> Well, if it's written as a .pyd/.so and becomes a Python standard
> module it should work... :)

Parts of it really need to be in C, I think, so if it's all in C then
probably everyone could be made happy.  Who has an all-Python driver?=20
Gadfly I imagine, maybe COM-based drivers...=20

Anyway, people talked serious about including Gadfly in the standard
distribution, so there's interest in supporting database stuff.  The
only weird part about the module is that it would be useless on its own.

> With standardized and uniform SQL access and the new datetime class
> I only think we need a fixed point / money data type, and Python
> will be ready to become the COBOL of the 21st century! :)
> (It does sound awful, doesn't it. I guess that's why I like it.)

w00t!