[DB-SIG] Controlling return types for DB APIs

[Anthony Tuininga]

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?

On 4/17/07, Michael Bayer <mike_mp at zzzcomputing.com> wrote:
>
> On Apr 17, 2007, at 4:12 PM, Federico Di Gregorio wrote:
>
> >
> > The Python->SQL part is perfect for adaptation and, for example,
> > psycopg
> > has a micro-protocols implementation to help with the adaptation of
> > any
> > Python object into a valid ISQLQuote one. Why two different systems?
> > Because transating Python to SQL and SQL to Python are two very
> > different operations, IMHO.
>
> they are different operations, but their semantics are mirror images
> of one another.   it follows very closely if you are binding a
> datetime object to a bind param which is in a SQL expression being
> compared against a particular column, that the rules which convert
> the datetime to a SQL value are the same rules in reverse that would
> convert the selection of that column in the result.  this is why i
> think the "type" of a column, both its bind param adaptation as well
> as its result row adaptation, can be expressed by the same rule
> object in most cases.  and I know it works since this is how
> sqlalchemy has been doing it for quite a while now.
>
>
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig
>