RE: database

Discussion in 'Python' started by Pettersen, Bjorn S, Sep 18, 2003.

  1. > From: Alberto Vera [mailto:]
    >
    > 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
     
    Pettersen, Bjorn S, Sep 18, 2003
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,431
    Carl Prothman [MVP]
    Jan 12, 2005
  2. =?Utf-8?B?U1M=?=
    Replies:
    3
    Views:
    3,882
    Juan T. Llibre
    May 15, 2005
  3. Replies:
    8
    Views:
    480
  4. casper
    Replies:
    0
    Views:
    9,104
    casper
    Apr 24, 2006
  5. Keith
    Replies:
    1
    Views:
    2,367
    Keith
    Jul 3, 2006
Loading...

Share This Page