MS Access db (mdb): viewing table attributes

[gau.tai]

I have an Access 2003 database, protected by a workgroup, that I am
trying to view through python. Currently, I'm attempting dao with the
win32 package, and I'm able to view all of the table names, but I don't
know how to view the attributes of the tables.

My code:

import win32com.client
from pprint import pprint

#sample code to list all tables in the selected database
daoEngine = win32com.client.Dispatch('DAO.DBEngine.36')
dtbs = daoEngine.OpenDatabase('database_file.mdb')
for table in dtbs.TableDefs:
if table.Name[:4] <> "MSys" and table.Name[:4] <> "~TMP":
pprint(table.Name.encode('utf-8'))
#the above works, but below does not:
for col in table.Fields:
pprint(col.Name, Col.Type, Col.Size)

I am getting that len(Fields) = 0, and I know the database tables are
all full of columns (and data). Is there also some resource that
defines all the properties of TableDefs? I wasn't able to find a
python reference for this.

Thank You,
Gau

[Felipe Almeida Lessa]

Em Sex, 2006-03-10 Ã*s 09:53 -0800, ga*****@gmail.com escreveu:

I have an Access 2003 database, protected by a workgroup, that I am
trying to view through python. Currently, I'm attempting dao with the
win32 package, and I'm able to view all of the table names, but I don't
know how to view the attributes of the tables.

I don't know if you can't use ODBC, but that's what I'm using now to
access an Access database. A (maybe) useful snippet:

import dbi, odbc # The order of this import is important!
connection = odbc.odbc('Driver={Microsoft Access Driver (*.mdb)};' +
'Dbq=C:\database.mdb;Uid=Admin;Pwd=;')
cursor = connection.cursor()
cursor.execute('SELECT column1, column2, COUNT(*) FROM table' +
'WHERE column1 < column2' +
'GROUP BY column1, column2')
print cursor.fetchall()

AFAIK, the main advantage is that it is easier to change to another
database later if needed, as the odbc module uses the same interface as
many others.

At least in my application, it has the same performance as Access itself
(most of my queries return just some sums and groups, none of them
return big chunks of data, so most part of the processing is kept on the
Jet side).

Hope that helps,
Felipe.

--
"Quem excele em empregar a força militar subjulga os exércitos dos
outros povos sem travar batalha, toma cidades fortificadas dos outros
povos sem as atacar e destrói os estados dos outros povos sem lutas
prolongadas. Deve lutar sob o Céu com o propósito primordial da
'preservação'. Desse modo suas armas não se embotarão, e os ganhos
poderão ser preservados. Essa é a estratégia para planejar ofensivas."

-- Sun Tzu, em "A arte da guerra"

[gau.tai]

Here is the full code I'm using now to try out your sample:

import dbi, odbc
import win32com.client
from pprint import pprint

#sample code to list all tables in the selected database
daoEngine = win32com.client.Dispatch('DAO.DBEngine.36')
dtbs = daoEngine.OpenDatabase('database.mdb')
for table in dtbs.TableDefs:
if table.Name[:4] <> "MSys" and table.Name[:4] <> "~TMP":
pprint(table.Name.encode('utf-8'))

#sample code that will execute a stored query with parameters
dbconn = odbc.odbc('DSN=db; UID=user; PWD=pass')
dbcursor = dbconn.cursor()
dbcursor.execute('execute "result specs" 1')
dbcursor.execute('SELECT column1, column2, COUNT(*) FROM Weight ' +
'WHERE column1 < column2 ' +
'GROUP BY column1, column2')
#pprint(dbcursor.fetchall())
dbcursor.close()
dbconn.close()

I get the error: dbcursor.execute('SELECT column1, column2, COUNT(*)
FROM Weight ' +
dbi.program-error: [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 2. in EXEC

[gau.tai]

ok, I know what I had wrong :) I was hoping that your code would
return the column names for me, but it was expecting me to list the
columns to use. I want to know how to retrieve that list of columns
through python.

[BartlebyScrivener]

Gau,

I'm a beginner and had fits connecting to my Access 2003 DB of
quotations. The best advice that finally worked for me was to use
mxODBC. In fact, you might just search this forum for Access and
mxODBC.

I can't customize to fit your situation, but here is the connection
part of a script I use to extract a random quote. I'm on Windows XP
using newest Python from ActiveState.

# Based on
# http://aspn.activestate.com/ASPN/Coo.../Recipe/389535
# Instructions for customizing are at:
# http://www.egenix.com/files/python/mxODBC.html

import mx.ODBC.Windows as odbc
import random
import textwrap

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

conn = odbc.DriverConnect(driv)
c = conn.cursor()

# Just counts the quotes, doesn't select them
c.execute ("SELECT COUNT(Quote) FROM Table1")

# Yields the number of rows with something in the quote field
total_quotes = c.fetchone()

# Get a random number somewhere between
# 1 and the number of total quotes

quote_number = random.randint(1, total_quotes[0])

# Select a quote where the ID matches that number
c.execute ("SELECT Author, Quote FROM QUOTES7 WHERE ID = %d" %
quote_number)

quote = c.fetchone()

The rest of the script just deals with formatting and presentation of
the quote.

Hope this helps.

rpd

[BartlebyScrivener]

>> I was hoping that your code would

return the column names for me, but it was expecting me to list the
columns to use. I want to know how to retrieve that list of columns
through python.

I think once you establish connection to the database using Python and
mxODBC, then your question becomes an SQL question not a Python
question.

rpd

[gau.tai]

That would be fine for me to switch to mxodbc, I've used it before and
it worked fine. But if I switch, do you know how I can get the column
names from the table? Maybe I'm not being clear with my question here.
I'm going to try again....

here's a table (it's in access):

=== tablename = mytable ===

id | user | pass | access privileges <-- these are the
column names I want
================================
0 bob 12345 admin/full
1 sam 53432 power user
4 mike 43234 guest

I know how to connect to the database.
I know how to get the table names.
How can I get the names of the columns for each table??

[gau.tai]

BartlebyScrivener:

Maybe you're right, and I'll try posting to another group. However, I
have found resources for doing this using the same data structure:
"TableDefs" which is in VB and python. I see how they are doing it in
VB, but I don't know how this works in python. I was hoping someone
could at least point me to the details of this object, so I could then
examine it's properties myself. In VB, it looks like it's more of a
property of the object and not an SQL query.

[gau.tai]

For more reference, I got my information to start this from:
http://aspn.activestate.com/ASPN/Coo...n/Recipe/52267

When I try to run that code, it gives me this:
File "C:\Python24\Lib\site-packages\win32com\client\util.py", line
83, in next
return _get_good_object_(self._iter_.next())
pywintypes.com_error: (-2146825178, 'OLE error 0x800a0c26', None, None)

The error is pointing to:
"for idx in currTabl.Indexes:"

[BartlebyScrivener]

>> How can I get the names of the columns for each table??

SELECT * FROM mytable

[Larry Bates]

ga*****@gmail.com wrote:

I have an Access 2003 database, protected by a workgroup, that I am
trying to view through python. Currently, I'm attempting dao with the
win32 package, and I'm able to view all of the table names, but I don't
know how to view the attributes of the tables.

My code:

import win32com.client
from pprint import pprint

#sample code to list all tables in the selected database
daoEngine = win32com.client.Dispatch('DAO.DBEngine.36')
dtbs = daoEngine.OpenDatabase('database_file.mdb')
for table in dtbs.TableDefs:
if table.Name[:4] <> "MSys" and table.Name[:4] <> "~TMP":
pprint(table.Name.encode('utf-8'))
#the above works, but below does not:
for col in table.Fields:
pprint(col.Name, Col.Type, Col.Size)

I am getting that len(Fields) = 0, and I know the database tables are
all full of columns (and data). Is there also some resource that
defines all the properties of TableDefs? I wasn't able to find a
python reference for this.

Thank You,
Gau

Not quite sure about DAO but when I use ODBC I get the fieldnames
by doing (stripped from working program):

crsr.execute(SQL_query)
fieldinfo=crsr.description
fieldnames=["%s" % i[0].lower() for i in fieldinfo]

Hope this helps.

-Larry Bates

[BartlebyScrivener]

Gau,

This prints the names of the columns in my database.

# Modification of
# http://aspn.activestate.com/ASPN/Coo.../Recipe/389535
# Instructions for customizing are at:
# http://www.egenix.com/files/python/mxODBC.html

import mx.ODBC.Windows as odbc

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

conn = odbc.DriverConnect(driv)
c = conn.cursor()

# get the column names from Table1
c.execute ("SELECT * FROM Table1")

# get column names
cols= [ i[0] for i in c.description ]
print '\n\ncols=',cols

[Steve Holden]

BartlebyScrivener wrote:

Gau,

This prints the names of the columns in my database.

# Modification of
# http://aspn.activestate.com/ASPN/Coo.../Recipe/389535
# Instructions for customizing are at:
# http://www.egenix.com/files/python/mxODBC.html

import mx.ODBC.Windows as odbc

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

conn = odbc.DriverConnect(driv)
c = conn.cursor()

# get the column names from Table1
c.execute ("SELECT * FROM Table1")

# get column names
cols= [ i[0] for i in c.description ]
print '\n\ncols=',cols

Note that there's no requirement that the SELECT actually retrieve any
data, son the normal technique is to use a query guaranteed to return no
rows, such as

SELECT * FROM Table1 WHERE 1=0

See also

http://aspn.activestate.com/ASPN/Coo...n/Recipe/81189

for an algorithm that will show data fron an arbitrary query in a
reasonably tidy display.

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC/Ltd www.holdenweb.com
Love me, love my blog holdenweb.blogspot.com

[gau.tai]

Thanks so much for the information, both of you. I guess I should have
just looked at it more simply to begin with. Now, I can move on to
more complicated information retrieval :-) My ultimate plan with this
task is to get whatever table attributes I can, like foreign/primary
keys, data types & length/size, etc. You both have been a great help.