[DB-SIG] Controlling return types for DB APIs

[Jim Patterson]

On 4/22/07, Michael Bayer <mike_mp at zzzcomputing.com> wrote:
>
> (assuming this is meant for on-list)


Yes, absolutely.  My mistake, I'm used to lists that place
themselves as the reply-to, and I did not check the to and
cc lists.

currently, you cant exactly write the "advanced type conversion
> library" in a totally dbapi-neutral way, because you cant always be
> sure that a DBAPI supports the native types used by a particular
> "advanced conversion" type.  in particular I mention dates because
> SQLite/pysqlite has no date type - you can *only* get a datetime
> object to/from a sqlite database using string formatting of some
> kind.  so if datestring parsing is part of a "layer on top of DBAPI",
> in the case of sqlite you need to use this layer, in the case of most
> other databases you dont.


I've never used SQLIte so I can only comment based on a
quick read of some of the docs, but my thinking is that
maybe the SQLite dbapi should be extended to provide
a DATETIME type for use by the Python programmer,
and them map in into some canonical string in the SQLite
apis.  It looks like some of that support already exists with
the PARSE_DECLTYPES and PARSE_COLTYPES, but
it seems to be missing the STRING, BINARY, NUMBER,
DATETIME, and ROWID types listed in PEP 249.  I'm
not fully sure that is needs them, but I know I've written
code to the DB API that will not work with SQLite since
it does not have them.

another example would be an "advanced" type that relies upon array
> values.  lots of folks seem to like using Postgres' array type, a
> type which is not available in other DBs.  so such a type which
> depends on underlying arrarys would also need to vary its
> implementation depending on DBAPI.


Given that arrays are not supported very well across
databases, I'm not sure that you can write portable code that
uses them.  Maybe we can define a set of types the must be
supported and set of types that are optional and then by
checking to see (at runtime) if the module exposes that type
this mythical "advanced" library could adjust itself.

Not that converting from binary->picklestream isnt something that
> should be performed externally to DBAPI...but because of the variance
> in available type support its hard to draw a crisp line between whats
> "on top" of DBAPI and whats not, which is why with dates in
> particular I put them in the "native" category, if for no other
> reason than sqlite's non-support of them (well, and also that dates
> are pretty darn important).


I look at them that way as well, but at least initially because
PEP 249 listed them as supported and because all the databases
I have used (a small set of the total that exist) all support it.

SQLAlchemy also expresses the "native type"/"advanced type" dichotomy
> explicitly.  For things like dates (which are non-standard to
> sqlite), binary objects (which return a specialized LOB object on
> oracle that is normalized to act like the other DBAPIs), numbers
> (which are returned as Decimal in postgres, floats in all others), SA
> implements whole modules of different TypeEngine implementations
> tailored to each supported DBAPI - these types form the "lower level"
> set of types.  The "translation on top of a type" operation is
> handled by subclasses of TypeDecorator, which references a TypeEngine
> (the lower level type base class) compositionally - currently
> PickleType is the only standard type within this second hierarchy.
> Other folks have also implemented Enums in this layer (which
> ironically is a native type in mysql).


I'm just hoping we can simplify some of this kind of stuff by
put more of it at the DBAPI level.  As you mentioned the real
question becomes where do you draw the line.  It is a tough
question.

I got started on this very topic since I wanted to draw the
line in a place other than where cx_Oracle had drawn
that line in the past.  It seemed to me that Unicode support
belonged in the DBAPI since it is somewhat hard to get
right with Oracle and the solution is VERY Oracle specific.
Setting the NLS_LANG environment variable wrong gets
you no or incorrect Unicode support.  I was also wanting
Decimal support since for me, I'm doing work with money
and floating point approximations of money is a really
scary thing.  I could have used the interface that cx_Oracle
supplied to always get numbers as strings and then done
the conversion myself, but I was nervous that someone
on my team would forget and it would cause problems.

Anthony was very willing to work with me to add support
for Unicode and Decimal so for me it was any easy redraw
of the line (Anthony was already planning the Unicode)

So I guess the reason i conflate the "native"/"advanced" types is
> because from DBAPI to DBAPI theres no clear line as to what category
> a particular kind of type falls into.


There seems to be as much confusion within the databases
themselves, so the best w may be able to do is broad support
for the common types and a way to tell if the module supports
the other types.

In order to write these advanced converters in a portable way
across which ever set of DBAPIs support the required type
we will still have to be able to tell the DBAPI that we need
the data in a standard python datatype so that it can be passed
around.  That might be a good starting point for above/below
the line.  If we look at the built-in type in the Python library
reference we can get a list of the python types that a developer
might want to use.  Some can be narrowed down to a single
option.  You most likely do not need iterators for example.

Jim Patterson
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/db-sig/attachments/20070422/0b7804a5/attachment-0001.html