[DB-SIG] New update for DB API 1.1

M.-A. Lemburg mal@lemburg.com
Thu, 25 Mar 1999 13:32:44 +0100


Andy Dustman wrote:
> 
> 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.

Then ROWID should not be equal to any description field type code,
e.g. see the example from mxODBC below.

> And there are several types which would qualify
> as NUMBER or STRING
>
> [...]
>
> This is what I would propose:
> 
> 1) Make type code field in description database-dependent.

This is already possible. Note that I changed the wording of the
definition of type objects (the parts you quoted). In mxODBC for
example I provide all the different SQL data type codes (as integers)
in the description field; the type objects are of the form:

# Helper for coltypes
class _dbiSet:
    def __init__(self,*values):
	self.values = values
    def __cmp__(self,other):
	if other in self.values:
	    return 0
	if other < self.values:
	    return 1
	else:
	    return -1

# Note that tests like 'coltype is STRING' don't work, you have
# use 'coltype == STRING'.
STRING = _dbiSet(SQL.CHAR, SQL.LONGVARCHAR)
BINARY = _dbiSet(SQL.BINARY, SQL.LONGVARBINARY)
NUMBER = _dbiSet(SQL.DECIMAL, SQL.DOUBLE, SQL.FLOAT, SQL.INTEGER)
DATE = _dbiSet(SQL.DATE)

# mxODBC doesn't support RowID columns; use cursor.specialcolumns() to
# find out which columns are best suited to uniquely identify a row.
# This object is not equal to any other object.
ROWID = _dbiSet()

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

You can use the above approach to meet the DB API requirements
and still provide more detailed database dependent information
in the description field.

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

I'm not a too good writer and even worse have a mathematical
background, so maybe someone could edit the two sections to
clearify them...

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

mxODBC converts them to Python floats; but you're right: a
fixed point type a la the one that Tim Peters posted on c.l.p
written in C wouldn't be a bad idea (it could then also be used
for monetary values).

For now, I think floats are the way to go: at least they preserve
the fraction part.

-- 
Marc-Andre Lemburg                               Y2000: 282 days left
---------------------------------------------------------------------
          : Python Pages >>> http://starship.skyport.net/~lemburg/  :
           ---------------------------------------------------------