mxODBC sql MSAccess

Discussion in 'Python' started by BartlebyScrivener, Nov 22, 2005.

  1. Hello, I'm new to python and trying to get records from an MSAccess
    database using mxODBC. It works, but the output is not formatted the
    way I want it.

    Here's the script:

    import mx.ODBC.Windows as odbc

    driv='DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:/Access
    Databases/Quotations2005'

    conn = odbc.DriverConnect(driv)
    c = conn.cursor()
    c.execute ("SELECT Author, Topic1, Topic2, Quote FROM QuotesToTxt WHERE
    Author LIKE 'Mencken%'")

    rows = c.fetchall()
    for r in rows:
    print r

    And here's what I get:

    ('Mencken, H.L.', 'Americans', 'Democracy', 'Democracy is the theory
    that the common people know what they want, and deserve to get it good
    and hard.')
    ('Mencken, H.L.', 'Conscience', 'Mother-In-Law', 'Conscience is a
    mother-in-law whose visit never ends. The inner voice which warns us
    that someone may be looking.')

    Where are the parenthese and single quotes coming from? SQL or mxODBC?
    And how can I get just simple tab-delimited records with a standard
    carriage return separating the records?

    Thanks so much for any help.

    bs
    BartlebyScrivener, Nov 22, 2005
    #1
    1. Advertising

  2. BartlebyScrivener

    Guest

    BartlebyScrivener wrote:
    > Hello, I'm new to python and trying to get records from an MSAccess
    > database using mxODBC. It works, but the output is not formatted the
    > way I want it.
    >
    > Here's the script:
    >
    > import mx.ODBC.Windows as odbc
    >
    > driv='DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:/Access
    > Databases/Quotations2005'
    >
    > conn = odbc.DriverConnect(driv)
    > c = conn.cursor()
    > c.execute ("SELECT Author, Topic1, Topic2, Quote FROM QuotesToTxt WHERE
    > Author LIKE 'Mencken%'")
    >
    > rows = c.fetchall()
    > for r in rows:
    > print r
    >
    > And here's what I get:
    >
    > ('Mencken, H.L.', 'Americans', 'Democracy', 'Democracy is the theory
    > that the common people know what they want, and deserve to get it good
    > and hard.')
    > ('Mencken, H.L.', 'Conscience', 'Mother-In-Law', 'Conscience is a
    > mother-in-law whose visit never ends. The inner voice which warns us
    > that someone may be looking.')
    >
    > Where are the parenthese and single quotes coming from? SQL or mxODBC?


    >From Python. You data is stored as a list of tuples.


    > And how can I get just simple tab-delimited records with a standard
    > carriage return separating the records?


    for r in rows:
    print "%s\t%s\t%s\t%s" % r

    >
    > Thanks so much for any help.
    >
    > bs
    , Nov 22, 2005
    #2
    1. Advertising

  3. BartlebyScrivener

    Chris Curvey Guest

    mxODBC implements the Python DB-API spec, which states that each "row"
    of query results is returned as a tuple. If you want the data
    displayed differently, you can do it yourself.

    for row in rows:
    print "\t".join(row)

    should do it.
    Chris Curvey, Nov 22, 2005
    #3
  4. Works!!

    Thank you all so much. I didn't know it was coming back as a tuple, and
    I'm sure that would have taken me four hours to figure out.

    Appreciate it!

    bs

    --------

    Chris Curvey wrote:
    > mxODBC implements the Python DB-API spec, which states that each "row"
    > of query results is returned as a tuple. If you want the data
    > displayed differently, you can do it yourself.
    >
    > for row in rows:
    > print "\t".join(row)
    >
    > should do it.
    BartlebyScrivener, Nov 22, 2005
    #4
  5. BartlebyScrivener

    Steve Holden Guest

    BartlebyScrivener wrote:
    > Hello, I'm new to python and trying to get records from an MSAccess
    > database using mxODBC. It works, but the output is not formatted the
    > way I want it.
    >
    > Here's the script:
    >
    > import mx.ODBC.Windows as odbc
    >
    > driv='DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:/Access
    > Databases/Quotations2005'
    >
    > conn = odbc.DriverConnect(driv)
    > c = conn.cursor()
    > c.execute ("SELECT Author, Topic1, Topic2, Quote FROM QuotesToTxt WHERE
    > Author LIKE 'Mencken%'")
    >
    > rows = c.fetchall()
    > for r in rows:
    > print r
    >
    > And here's what I get:
    >
    > ('Mencken, H.L.', 'Americans', 'Democracy', 'Democracy is the theory
    > that the common people know what they want, and deserve to get it good
    > and hard.')
    > ('Mencken, H.L.', 'Conscience', 'Mother-In-Law', 'Conscience is a
    > mother-in-law whose visit never ends. The inner voice which warns us
    > that someone may be looking.')
    >
    > Where are the parenthese and single quotes coming from? SQL or mxODBC?
    > And how can I get just simple tab-delimited records with a standard
    > carriage return separating the records?
    >
    > Thanks so much for any help.
    >
    > bs
    >

    Well you know that answer now. You might also consider using the recipe at

    http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81189

    to give you the column titles and so on.

    regards
    Steve
    --
    Steve Holden +44 150 684 7255 +1 800 494 3119
    Holden Web LLC www.holdenweb.com
    PyCon TX 2006 www.python.org/pycon/
    Steve Holden, Nov 23, 2005
    #5
    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. Siegfried Heintze
    Replies:
    1
    Views:
    771
    Paul Henderson
    Apr 15, 2006
  2. eGenix Team: M.-A. Lemburg
    Replies:
    1
    Views:
    310
  3. eGenix Team: M.-A. Lemburg
    Replies:
    0
    Views:
    294
    eGenix Team: M.-A. Lemburg
    May 29, 2007
  4. eGenix Team: M.-A. Lemburg
    Replies:
    0
    Views:
    281
    eGenix Team: M.-A. Lemburg
    Aug 22, 2007
  5. eGenix Team: M.-A. Lemburg

    ANN: eGenix mxODBC and mxODBC Zope DA on 64-bit FreeBSD

    eGenix Team: M.-A. Lemburg, Sep 12, 2007, in forum: Python
    Replies:
    0
    Views:
    315
    eGenix Team: M.-A. Lemburg
    Sep 12, 2007
Loading...

Share This Page