On 4/22/07, Michael Bayer <[EMAIL PROTECTED]> 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
_______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
