A Python way to get MS Access table column information?

[M.-A. Lemburg]

On 2007-10-15 17:16, goldtech wrote:
> 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)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: 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