database

[Pettersen, Bjorn S]

> From: Alberto Vera [mailto:avera at coes.org.pe] 
>
> Hello:
>
> Do you have any example about how access to a database 
> using ODBC or native driver?

using the win32all odbc module

>>> import odbc
>>> cn = odbc.odbc('AcctDB')
>>> c = cn.cursor()
>>> c.execute('select * from AcctMetaInfo order by snapshotdate')
0
>>> row = c.fetchone()[:10] # first ten columns
>>> row
(141410449310712784L, 7051, <DbiDate object at 0x0089E070>, 2147483646,
0, 2147483646, '?1', 2147483646, '?1', <DbiRaw object at 0x0089E080>)
>>> str(row[2])
'Mon Apr 15 11:14:04 2002'
>>> str(row[-1])
'\t\x13\x14\x01\x00\x00\x00\x00\x00\x00\x00'
>>>


using the adodbapi module (http://sourceforge.net/projects/adodbapi):

>>> import adodbapi
>>> cn = adodbapi.connect('AcctDB')
>>> c = cn.cursor()
>>> c.execute('select * from AcctMetaInfo order by snapshotdate')
>>> row = c.fetchone()[:10]
>>> row
(141410449310712779L, 7051, datetime.datetime(2002, 4, 15, 11, 14, 4),
2147483646, 0, 2147483646, u'?1', 2147483646, u'?1', <read-write buffer
ptr 0x00A53C94, size 11 at 0x00A53C78>)
>>> row[2]
datetime.datetime(2002, 4, 15, 11, 14, 4)
>>> str(row[-1])
'\t\x13\x14\x01\x00\x00\x00\x00\x00\x00\x00'

using query analyzer:

AccountID            ...ID       SnapshotDate            ...
-------------------- ----------- ------------------------
141410449310712779   7051        2002-04-15 11:14:04.000 ...

The account id is a bigint, and as you can see the odbc module loses
precision because it is first converted to a float internally. 

Both modules convert currency/money data to float:

>>> c.execute('select Amount from FinancialInfo where acountid = 1234')
>>> c.fetchone()
(80.439999999999998,)
>>>

which is of course completely horrible.

With the adodbapi module you can change the default behavior however:

----------------------
import adodbapi

class Money(long):
  def __new__(self, value):
    return long.__new__(Money, value)

  def __repr__(self):
    d, c = divmod(self, 10000)
    return '$%s.%s' % (d,c)
		
def cvt_money((hi,lo)):
  # not extensively tested :-)
  return Money((hi<<32) + (lo < 0 and (2**32+lo) or lo))
		
adodbapi.variantConversions[adodbapi.adCurrency] = cvt_money

cn = adodbapi.connect('AcctDb')
c = cn.cursor()

c.execute('select Amount from FinancialInfo where where acountid =
1234')
print c.fetchone()
----------------------

the output:

($80.4400,)

which is much better :-)

I don't have mxODBC, nor any native drivers to test with (although I'd
be interested in the results if anyone else does..)

taking-a-break-from-mdx'ly y'rs,
-- bjorn