[DB-SIG] Controlling return types for DB APIs
Chris.Clark at ingres.com
Tue Apr 17 20:43:30 CEST 2007
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,
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:
# Register the python2db adapter
# Register the db2python converter
con = mydbapi.connect(".......", use_type_mapping=TRUE)
cur = con.cursor()
cur.execute("select myvarchar from mytable")
cur.fetch() ## type would be Python type "mydbapi.varcharClass"
More information about the DB-SIG