[DB-SIG] Controlling return types for DB APIs

[Anthony Tuininga]

On 4/20/07, Chris Clark <Chris.Clark at ingres.com> wrote:
>
>  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.

Ok, that's good. :-)

>  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?

Mostly correct. Yes, it is for result sets only although the
possibilty of donig the same for output bind variables should be
considered as well -- but that would require a different method
signature or a change to setinputsizes(). The setting would remain so
long as the same statement was executed again. When a new statement is
prepared, this setting would revert to the default values (defined by
the setdefaulttype() calls).

>  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).

Well, if there was only one type, there wouldn't be much point in
calling setdefaulttype() would there? The default would of course be
the only one available and saying so wouldn't make it any more so...
:-) Unless I'm missing something and you meant something else?

>  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:
>
>
>
> for the database driver to do some sort of DESCRIBE INPUT and work out which
> adapter to use
>
> to raise an error when registeradapter() is called with conflicting types
>  Any comments? Should this be driver dependent?

Hmm, I was assuming that the adapters would be indexed by type and
that setting one would override the previous one. In other words, to
do what you were hoping to do would require a different Python type
for decimal and spatial. Having the database figure out which type to
use would be difficult at best and impossible in some situations. In
addition, the situation you give is a bind variable which is normally
controlled by setinputsizes(). I wasn't intending to change the
signature of this method but perhaps a new method should be added?

cursor.setbindtype(nameOrPosition, databaseType, [type])

which would allow you to specify the database type and optionally the
Python type (for output bind variables). At that point the same rules
as described above would apply. This is overlap with setinputsizes()
though so I'm not sure whether or not this is a good idea.

>  As for names, I've a few suggestions but I don't feel strongly about the
> names:
>
>
> setdefaulttype --> coercetype
>  settype --> coercecolumn

Hmm, I think I prefer the setXXX() type methods as they are similar in
content to the setinputsizes() and setoutputsizes() methods. But I can
see your point about coerceXXX() as that is in fact what is happening.

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

I think registeradapter() is clearer, too.

>  Chris
>
>