[DB-SIG] RE: column type casting

[brian zimmer]

> em... i was not talking about data input at all. you tell the driver
> to use a specific object for database output by calling one of the
> register_type() functions, as explained below. maybe my english
> is worse than i think, coding example (using dbapi as the module name :)
>

We need to be able to specify handling for input and output.  For instance, since Python has no boolean type and some databases do
(at least Informix does), the enduser needs to be able to specify to the driver that the type should be mapped.  This is also
important for working with BLOBs and CLOBs so the user is free to use python files, strings, byte arrays, and the have the data
properly converted.  This holds true on the outbound as well.

>
> # this little demo show how to fetch some icons stored in the db
> # both as pnginame type and simple strings...
>
> import dbapi, Image, StringIO
>
> def makePngImage(s):
>     return Image.open(StringIO(s))
>
> o = dbapi.connect(DSN)
> c = o.cursor()
>
> # we do a select to obtain the type (name = '' select 0 rows, but
> # we are interested in the type_code, we don't want rows at now
> c.execute("SELECT icon FROM icons_table WHERE name = ''")
> type_code = c.description[0][1]
>
> # this register the type as a *generic* type caster for every select
> # on columns that have a type of code_type
> PNGIMAGE = .new_type("PNGIMAGE", makePngImage, (type_code,))
> dbapi.register_type(PNGIMAGE)
>
> # puts in icons a series of Image object from the Image library
> c.execute("SELECT icon FROM icons_table WHERE name LIKE '%.png'")
> icons = c.fetchall()
>
> # now, what happens if the db does not support a different type
> # for images but just store them in a binary or string field?
> # we tell the cursor to apply the typecasting object to the column 1
> # (in column 0 we put the icons names...)
> c.register_type(PNGIMAGE, 0)
> ic.execute("SELECT name, icon FROM icons_table WHERE name LIKE '%.png'")
> names_and_icons = c.fetchall()
>

I presume this is a typo?  Should it not read:

 c.register_type(PNGIMAGE, 1)

since the icon is second in the select?

> it is a little bit more clear now? note that the default STRING, DATETIME,
> NUMBER, etc. objects are created by the module at init time but are
> exactly the same and are registered *only* with the module, not with
> cursoer or connections. you can override them a module level or by
> registering your custom objects with the connections and cursors.
>

I like the idea of a single callable object as the converter so you can easily mix and match for datatypes, but it needs to go both
ways.  I'm also partial to my syntax:

ic.execute("SELECT name, icon FROM icons_table WHERE name LIKE '%.png'", bindings={1, PNGIMAGE})

so the state can be maintained just during the call to execute.  If you wish to keep it for all calls just pull the dictionary out
and then include it with each call.

> > Note that you have a multiple to one mapping here, so the interface
> > won't be able to map a specific type code to one well defined
> > type constructor. Also, the data returned by the database may
> > be very different from what you pass in on the input side at
> > Python level, e.g. on input you may have Date(123456) with 123456
> > being a 1970 based ticks value, but the database generates
> > 1900 based ticks values (OK, this is an extreme example, but you
> > get the point...) ?
>
> this confusion derives from the fact that you interpreted my type object
> as two-ways. they are one-way only, they only convert from the db to
> python. the old Date, Binary, etc, are still good to convert from python
> to the db. to make it clearer, BINARY (all uppercase) converts from the
> db to python and produce a python buffer, Binary convert from python to
> the db and what it produces is application dependent.
>

Even if it's application dependent, why not have a consistent mechanism for converting?

> [snip]
> > Make that connection.register_type(...) and cursor.register_type(...)
> > with the cursor inheriting the type registrations from the connection.
> > A module scope registration will only cause problems (thread-wise
> > and design-wise too).
>
> the module scope registration is there to allow you to override the
> default type casting object: STRING, DATETIME, etc... so we have:
>
> module.register_type(TYPE)  -  convert based on type_code
> connection.register_type(TYPE)  -  convert based on the type_code
> cursor.register_type(TYPE, column)  -  convert based on column
>

I would also like to add support for cursor lifecycles.  This is especially useful for me because the JDBC drivers for each vendor
offer a number of very convenient methods that simplify work later, such as auto-incremented columns.  Does this hold true for other
technologies such as ODBC or native libraries?

thanks,

brian