[DB-SIG] Controlling return types for DB APIs

Michael Bayer mike_mp at zzzcomputing.com
Tue Apr 17 19:53:31 CEST 2007

On Apr 17, 2007, at 10:51 AM, Art Protin wrote:

> Yes, something nice and simple, like a dict using the string name  
> of the DBMS native
> datatype as the index.  However, this might not work out after  
> all.  Our database
> system has an nearly unbounded set of types.  The types have two  
> components, say a
> major and minor, or a main type and subtype.  The main type  
> "STRING" alone
> has 65535 subtypes (one for each allowable size).  Other main types  
> may a few
> subtypes or even no subtypes  Some of the subtypes make a major  
> difference in
> the conversion function behavior (like those for DATE) and some  
> make nearly none.
> My conversion routines are called based on the main type but need  
> both the data
> value and the subtype as arguments.  Do any of the other systems have
> such a multi-level type scheme as this?

im not following this thread so closely, but SQLAlchemy does have a  
configurable type system which can represent both the "major" type as  
you call it, plus any number of arguments for each type (which youd  
call the "minor" type), for any given result set column.  The "major"  
part is represented by the particular subclass of TypeEngine used,  
such as SLDateTime ( a date time type as represented in SQLite), and  
the "minor" part by the state of that particular TypeEngine instance  
(such as the length of a string column, or its encoding).

Of course SQLAlchemy is a significant layer on top of DBAPI, so as  
far as the "registry"-like functionality of what types map to what  
columns, its achieved via the presence of Table objects which are  
comprised of collections of Columns each with their own TypeEngine  
instance.   if DBAPI contained its own type-registry like system  
(which would likely be per-connection, since thats the highest-level  
object DBAPI provides which is still stateful with regards to a  
particular database connection), SA could probably modify TypeEngine  
to move its type-conversion code into this layer, instead of having  
to piggyback the translation onto result set objects.

However i might suggest that this whole thread, "controlling return  
types", perhaps be expanded to include "controlling *input* types and  
return types", since to me (as well as to SQLAlchemy) being able to  
send an arbitrarily-typed python object into a bind parameter is just  
the mirror image of receiving a result column as an arbitrarily-typed  
python object.  I think it would be unfortunate if only one half of  
the coin were addressed.

More information about the DB-SIG mailing list