Search code examples
pythonjdbcdatabase-metadata

What is the Python equivalent to JDBC DatabaseMetaData?

What is the Python equivalent to DatabaseMetaData

Solution

  • This is not a python-specific answer; in fact I don't know if Python data drivers have this sort of thing. But maybe this info will help.

    The ANSI SQL-92 and SQL-99 Standard requires the INFORMATION_SCHEMA schema, which stores information regarding the tables in a catalog.

    The metadata you seek can be retrieved with a query on views in that schema.

    for example:

    select column_name, is_nullable, data_type, character_maximum_length as maxlen 
from information_schema.columns 
where table_name = 'Products'

    Not all databases implement that part of the standard. Oracle, for example, does not.

    Fortunately, there are also database-specific tables that store that kind of info.

    While Microsoft SQL Server supports the Information_Schema thing, there are also SQL Server-specific tables that give more metadata information. These are [CatalogName].dbo.sysobjects and [CatalogName].dbo.sysolumns. Similar queries on these tables will give you the metadata you seek. Example:

    select * from [CatalogName].dbo.syscolumns 
where id = 
    (Select id from [CatalogName].dbo.sysobjects where name = 'Products')

    In Oracle, the ALL_TAB_COLUMNS table can give you the information: 

    select column_name, data_type, data_length, data_precision, data_scale
from ALL_TAB_COLUMNS
where table_name = 'EMP';

    Whether you query the standard views or the db-specific views, you don't need ODBC to do these queries - you can use whatever db connection you have available for operational data, subject to security approvals of course.