[DB-SIG] New update for DB API 1.1

Andy Dustman adustman@comstar.net
Wed, 24 Mar 1999 17:18:30 -0500 (EST)


Okay, one more thing that bugs me, and this is somewhat more serious.

We still have the various type indicators which are inherited from the
defunct dbi module, i.e. BINARY (was RAW), STRING, NUMBER, DATE, TIME,
TIMESTAMP, and ROWID. (The constructors seem fine.) DATE, TIME, and
TIMESTAMP directly correspond to MySQL types. MySQL doesn't have anything
that corresponds to ROWID. And there are several types which would qualify
as NUMBER or STRING:

>>> dir(_mysql.FIELD_TYPE)
['BLOB', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'DOUBLE', 'ENUM', 'FLOAT',
'INT24', 'INTERVAL', 'LONG', 'LONGLONG', 'LONG_BLOB', 'MEDIUM_BLOB',
'NEWDATE', 'NULL', 'SET', 'SHORT', 'STRING', 'TIME', 'TIMESTAMP', 'TINY',
'TINY_BLOB', 'VAR_STRING', 'YEAR', '__doc__', '__module__']

In addition, design of the module has obviated (word for the day) for the
dbi constants. At the lower level (_mysql), there are result objects, and
they have a describe() method which produces the description for the
result. At present, it uses the MySQL type codes. But the application
really never has to look at these codes, because I also employ a
module-wide dictionary that maps MySQL types to Python functions which
convert a string (what MySQL always returns) into a Python type. There are
some reasonable defaults for numbers and they are also pluggable at
run-time, and MySQLdb does this for the date/time types (and anything not 
mapped is returned as a string):

>>> MySQLdb.type_conv 
{13: <built-in function int>, 12: <function ParseDateTime at 810a3d0>, 11:
<function ParseTime at 80fd8d0>, 10: <function ParseDate at 8110df0>, 9:
<built-in function int>, 8: <built-in function long>, 7: <function
mysql_timestamp_converter at 8107850>, 5: <built-in function float>, 4:
<built-in function float>, 3: <built-in function int>, 2: <built-in
function int>, 1: <built-in function int>}

So the old dbi types are really not useful at all for MySQL. In my
experience, they are not useful for mxODBC, at least with Solid. I realize
other databases may need some help doing the type conversion. But, they
still don't seem very useful. Hmmm, should that NUMBER column be converted
to an int, a long, or a float? Is that BINARY column a BLOB, LONG_BLOB,
MEDIUM_BLOB, or a TINY_BLOB? Generally, these are things you need to know
before you can carry out type conversion.

This is what I would propose:

1) Make type code field in description database-dependent.

2) Provide a set of truth functions which take the place of comparing
against fixed type code. For example, instead of:

    if typecode == NUMBER: ...

to:

    if isnumber(typecode): ...

I'm not really even sure that #2 is even necessary. I think the database
ought to convert columns into standard Python types, or other types if
possible, and otherwise return strings (or buffers) for "weird" types,
like ENUM or SET. (And NULL -> None, obviously.)

Converting from Python types to column types is another matter. For my
MySQLdb, it's been sufficient to call str() on each parameter, make sure
special characters are quoted, and then add the necessary quotation marks.

Maybe I'm just not understanding this section, or the section doesn't
really mean what it says:

"A Cursor Object's description attribute returns information about each of
the result columns of a query. The type_code must be equal to one of type
codes defined below.

"Note: The values returned in the description tuple may not necessarily be
identical to the defined type codes, i.e. while coltype == STRING should
always work, coltype is STRING may fail. This is necessary because the
database interface may want to return more specific type codes than the
ones defined below."

But if the database is returning a more specific type code, how can it be
equal to one of the defined type codes if several of the specific type
codes map to a defined type code, such as BLOB, TINY_BLOB, MEDIUM_BLOB,
LONG_BLOB all mapping to BINARY? I suppose this would work:

    if typecode in BINARY: ...

assuming BINARY = (BLOB, TINY_BLOB, MEDIUM_BLOB, LONG_BLOB). Is it useful?
Not particularly, at least for me, but it seems more workable than the
current API...

My database experience with the API is limited to Solid with mxODBC and
MySQL, so if someone else out there has some perspective of why we need
this stuff for some other database, let's hear it.

The last bit, what to do with NUMERIC/DECIMAL SQL fields (i.e.
fixed-point)? Obviously converting them to integers is RIGHT OUT, and
converting them to floats is probably not a good idea either. At the
moment I just leave 'em as strings and let the application figure it out.
This may need to just stay an open issue for awhile unless someone wants
to come up with a fixed-point type for Python (perhaps a wrapper around
long?).

-- 
Andy Dustman  (ICQ#32922760)    You should always say "spam" and "eggs"
ComStar Communications Corp.                 instead of "foo" and "bar"
(706) 549-7689 | PGP KeyID=0xC72F3F1D   in Python examples. (Mark Lutz)