[DB-SIG] Controlling return types for DB APIs

[Chris Clark]

Anthony Tuininga wrote:
> I've been following this thread and it would appear that no real
> consensus has been reached as of yet. I've looked at the api used by
> sqlite and knowing its storage and type definition system it makes
> good sense. I am considering adding the following to cx_Oracle,
> following some of the examples given so far with modifications needed
> for Oracle, and I'd appreciate any input you might have.
>
> cursor.setdefaulttype(databaseType, type)
> connection.setdefaulttype(databaseType, type)
>
> What this method would do is specify that whenever an item that is
> represented on the database by the given database type is to be
> retrieved, the specified type should be used instead of the default.
> This would allow for a global or local specification that numbers are
> to be returned as strings or decimal.Decimal objects or that strings
> are to be returned as unicode objects, for example.
>
> cursor.settype(position, type)
>
> This would allow specification of the type to use for a particular
> column being fetched.
>
> registeradapter(type, databaseType, fromPythonMethod, toPythonMethod)
>
> This would specify that whenver an object of the given type is bound
> to a cursor, that the fromPythonMethod method would be invoked with
> the value and would expect a return value that can be directly bound
> to the databaseType. The toPythonMethod method would be invoked when
> columns are retrieved and would accept the databaseType value and
> expect back a value of the given type.
>
> Some help on the names would be appreciated as well -- its the worst
> part of programming. :-) I've tried to use the DB API style of naming
> -- all lower case without any underscores even though it isn't my
> personal favorite.
>
> Any comments?

My initial reaction is that I like it! I like registeradapter() and that 
it is easy to set at the connection or cursor level.

I'm guessing that the cursor.settype() call is for a result set only and 
that the adapter would be reset on a new cursor.execute() call?

I'm also wondering if setdefaulttype() should have param 2 as an 
optional param (i.e. if there is only one registered adapter the driver 
can work out the 2nd param).

It is probably worth defining a conflict resolution approach, even if 
the approach is in documentation and says, "the behavior of conflicting 
types in adapters is undefined"! E.g. sending to db conflict (note 
fairly artificial):

    registeradapter(str, DECIMAL, pyStr2dbDec, dbDec2pyStr)
    registeradapter(str, SPATIAL, pyStr2dbSpa, dbSpa2pyStr)
    cursor.setdefaulttype(DECIMAL, str)
    cursor.setdefaulttype(SPATIAL, str)
    cursor.execute('select x from mytable where mytable.col1 = ?',
    ('12.34',))
    ## is the input supposed to be decimal or a spatial type?


The alternatives are:

   1. for the database driver to do some sort of DESCRIBE INPUT and work
      out which adapter to use
   2. to raise an error when registeradapter() is called with
      conflicting types

Any comments? Should this be driver dependent?


As for names, I've a few suggestions but I don't feel strongly about the 
names:

    setdefaulttype --> coercetype
    settype --> coercecolumn


registeradapter() is clear, I wondered about setadapter() instead, but 
registeradapter() is probably the most clear.

Chris

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/db-sig/attachments/20070420/dd4f0d1a/attachment.htm