A Python way to get MS Access table column information?

[goldtech]

Using Python and OBDC in MS-Access DBs. So, I'm able to run SQL
statements from Python on an Access DB.

Is there an SQL statement that will give me column information? For a
table I want to know the data type and of course colum/Attribute name
for each column.

So far the answer has been "no". VB or some other tool is needed to do
that I'm told.

Using just Python and OBDC is there a way? Maybe Win32com?

Thanks

 

[Diez B. Roggisch]

Using Python and OBDC in MS-Access DBs. So, I'm able to run SQL
statements from Python on an Access DB.

Is there an SQL statement that will give me column information? For a
table I want to know the data type and of course colum/Attribute name
for each column.

So far the answer has been "no". VB or some other tool is needed to do
that I'm told.

Who said that? Did you check the cursor objects description-property, as
described in

http://www.python.org/dev/peps/pep-0249/

That at least for other DBs gives the needed info, and it seems it's
required.

Diez

 

[M.-A. Lemburg]

Using Python and OBDC in MS-Access DBs. So, I'm able to run SQL
statements from Python on an Access DB.

Is there an SQL statement that will give me column information? For a
table I want to know the data type and of course colum/Attribute name
for each column.

So far the answer has been "no". VB or some other tool is needed to do
that I'm told.

Using just Python and OBDC is there a way? Maybe Win32com?

Here's a quick example:

# Table that you're interested in:
tablename ='MyTable'

# Load mxODBC
import mx.ODBC.Windows

# Open the connection
database = mx.ODBC.Windows.DriverConnect(
'DSN=<password>;'
'UID=<username>;'
'PWD=<username>')

# Create a cursor to execute statements on
cursor = database.cursor()

# Issue a dummy statement that won't return anything,
# but will setup the cursor.description attribute
cursor.execute('select * from %s where 1=0' % tablename)

# Print a list of column names:
print 'Found these columns:'
for columndef in cursor.description:
print ' ', columndef[0]

For more information on the format of cursor.description,
see the DB-API 2.0 spec:

http://www.python.org/dev/peps/pep-0249/

of the mxODBC manual:

http://www.egenix.com/products/python/mxODBC/#Documentation

If you need even more meta-information about the columns
or the schema in general, have a look at the catalog methods
which are available in mxODBC, e.g. cursor.columns().

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Oct 15 2007)________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611