Re: [SQLObject] ADO DB-API support?

[Magnus <ma...@th...>]

At 12:39 2003-05-28 -0500, Ian Bicking wrote:
>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.

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.

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

Right.

>* Anything with a __sqlrepr__ method has that called, with no
>arguments.  The result is expected to be fully quoted.

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.

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

INSERT INTO T1 (C1) VALUES ('''that''''s''')

That would be a bit sad... :(

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.

>* 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 = dbdriver.quote
>def quote(val):
>     if type(val) is DateTimeType:
>         return val.strftime("'%c'")
>     else:
>         return old_quote(val)

And then "dbdriver.quote = quote" or what? Do you register
this? Why not just supply something like date.Format('%Y-%m-%d')
instead of your plain date?

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.

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

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.

> > Both bookkeeping and paying customers need priority handling... :)
>
>Yeah, I hear that.  I have to cut myself off from this stuff every so
>often.

And I forget it... :( Back to the books...

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

I think that would be best, and I hope it would be possible to get
this done.

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

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

Well, if it's written as a .pyd/.so and becomes a Python standard
module it should work... :)

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


--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program