[DB-SIG] RE: column type casting

[Federico Di Gregorio]

Scavenging the mail folder uncovered M.-A. Lemburg's letter:
[snip]
> > mmm, bad english, example: TYPE == cursor.fetchone().description[1]
> > should be true is the type code returned by the db is one of the
> > values in type_codes.
> 
> This is OK for data input, but how do you specify the case where
> you want database output to use a specific type object ?

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


# 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()

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.

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

[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 hope this clarifies my proposal a little bit,
federico

-- 
Federico Di Gregorio
MIXAD LIVE Chief of Research & Technology              fog@mixadlive.com
Debian GNU/Linux Developer & Italian Press Contact        fog@debian.org
  Qu'est ce que la folie? Juste un sentiment de liberté si
   fort qu'on en oublie ce qui nous rattache au monde... -- J. de Loctra