[egenix-users] How to get a list of table names via ODBC in Windows using mxODBC?

M.-A. Lemburg mal at lemburg.com
Sat Oct 5 22:29:15 CEST 2002


zhihua ouyang wrote:
> Hi All:
>   I just start to use mxODBC and have no problem to
> fetch records from a table via ODBC in windows using
> mxODBC. But I don't know how to get a list of table
> names for a given database via ODBC in Windows using
> mxODBC? Does anyone know how to get it?
> 
> Thanks very much in advance.

The .tables() catalog method will give you this information:

"""
Catalog Methods

Catalog methods allow you to access meta-level and structural information about a data source in a (more-or-less) 
portable way. Please note that these methods are extensions to the DB API standard. Also, some ODBC drivers to not 
support all of these methods or return unusable data. As a result, you should verify correct operation for your target 
data sources prior to relying on these methods.

All of the following catalog methods use the same interface: they do an implicit call to cursor.execute() and return 
their output in form of a list of rows which that can be fetched with the fetchXXX() methods in the usual way. The 
methods always return the number of rows in the result set.

Please refer to the ODBC documentation for more detailed information about parameters (if you pass None as a value where 
a string would be expected, that entry is converted to NULL before passing it to the underlying ODBC API) and the layout 
of the result sets.

Notes:

The result set layouts described here may not apply to your data source. Some databases do not provide all the 
information given here and thus generate slightly different result sets; expect column omissions or additions.

All catalog methods support keywords and use the indicated default values for parameters which are omitted in the call.

IMPORTANT: The search patterns given as parameters to these catalog methods are usually interpreted in a case-sensitive 
way. This means that even if the database itself behaves case-insensitive for identifiers, you may still not find what 
you're looking for if you don't use the case which the database internally uses to store the identifier. As an example 
take the SAP DB: it stores all unquoted identifiers using uppercase letters. Trying to fetch e.g. information about a 
table using a lowercase version of the name will result in an empty result set. You can use 
connection.getinfo(SQL.IDENTIFIER_CASE) to determine how the database stores identifiers. See the ODBC documentation for 
details.

.tables(qualifier=None, owner=None, table=None, type=None)
     Catalog method which generates a result set having the following schema:

     Column Name Column Datatype Comment
     TABLE_CAT VARCHAR(128) The name of the catalog containing TABLE_SCHEM. This column contains a NULL value.
     TABLE_SCHEM VARCHAR(128) The name of the schema containing TABLE_NAME.
     TABLE_NAME VARCHAR(128) The name of the table, or view, or alias, or synonym.
     TABLE_TYPE VARCHAR(128) Identifies the type given by the name in the TABLE_NAME column. It can have the string 
values "TABLE", "VIEW", "INOPERATIVE VIEW", "SYSTEM TABLE", "ALIAS", or "SYNONYM".
     REMARKS VARCHAR(254) Contains the descriptive information about the table.
"""

See the Microsoft ODBC site for the ODBC documentation.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/




More information about the egenix-users mailing list