[DB-SIG] Controlling return types for DB APIs

[Chris Clark]

Michael Bayer wrote:
> ........However i might suggest that this whole thread, "controlling return  
> types", perhaps be expanded to include "controlling *input* types and  
> return types"........

The pysqlite register_adapter and register_converter do this (in that 
order). I've not used it in anger but I like the simplicity of the API, 
see 
http://initd.org/pub/software/pysqlite/doc/usage-guide.html#converting-sqlite-values-to-custom-python-types 
for an example. pysqlite approach does have implications for the SQL 
that is then used but that isn't relevant to other databases so the 
api/approach could be appropriated :-)

I'm not familiar with psycopg's approach to be able to compare with 
pysqlite without research.

The big question is when should mapping take place? On the connection 
level, statement level, or column level.

E.g. mapping varchar to a python varchar type that contains length and data

    connection level - all varchars in DBMS get returned get returned as
    varchar python type
    statement level - all varchars in specific .execute() get returned
    as varchar python type
    column level - only specific varchars in specific .execute() get
    returned as varchar python type, i.e. some varchars could come back
    as regular python strings


The pysqlite approach is to use strings as type identifiers for the 
converter, I get the impression Art is not in favor if this approach but 
the dbms type param is likely to be DBMS specific so whether it is a 
string, a tuple of major/minor, etc. may not matter. If the driver 
offers constants for basic types that would be fine.

Here is an example following the pysqlite approach, using connection 
level mapping. It is usually easy to discuss what is good/bad about an 
idea if there is an example to critic:

    import mydbapi

    # Register the python2db adapter
    mydbapi.register_adapter(mydbapi.varcharClass, mydbapi.adapt_varchar)

    # Register the db2python converter
    mydbapi.register_converter(mydbapi.varcharType, mydbapi.convert_varchar)


    con = mydbapi.connect(".......", use_type_mapping=TRUE)
    cur = con.cursor()
    cur.execute("select myvarchar from mytable")
    cur.fetch()[0] ## type would be Python type "mydbapi.varcharClass"


Chris