mxODBC sql MSAccess

[BartlebyScrivener]

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

[mensanator]

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

[Chris Curvey]

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]

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.

[Steve Holden]

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/Coo...n/Recipe/81189

to give you the column titles and so on.

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/