[DB-SIG] Proposal: Standardized way to get database schema

M.-A. Lemburg mal@lemburg.com
Mon, 16 Sep 2002 10:48:57 +0200

Fabien COUTANT wrote:
> Hello,
> I want to go on the idea of standardizing the way
> to introspect a database
> schema.
> Following the suggestion from Marcos Sanchez
> Provencio, I wrote a suggestion for
> an API
> extension that provides schema introspection.
> What I did:
> - tried to conform to the
> current spirit of the
>     API.
> - took some ideas from Brian Zimmer
> - directly modified HTML since
> it's the only
>     document I have.

The DB API is available as PEP 249. If sending in patches,
please use the text form of that PEP.

> - am not sure whether multi-schema support should
>     be kept,
> since there is otherwise no support
>     for it in the rest of the API.
> - the parts about
> primary/foreign keys, procedures
>     and indexes are incomplete.  I just gave the
>     idea.
> - not
> sure of the exact list of SQL kinds that
>     should be included.  I don't remember the
>     standard
> perfectly...
> I hope this can be a starting point, as there
> is some work remaining.

There's a problem with your approach: many databases
store the schema information in (system) database tables
and to get at these you'd have to run actual queries against
the database connection. This usually requires having
a cursor setup to perform the tasks, so it would be
more natural to expose the various methods on cursor
objects rather than to create a new kind of hybrid
object for schemas.

Another aspect to consider is that databases tend to
provide much more information than exposed in your

I'd suggest to use the ODBC catalog methods as basis
since these are in wide use and have proven their
ability to capture enough information to be of good
use to the programmer.

BTW, instead of making the API more complicated, we
could also have a support module which then supports
various backends, much like the dbinfo.py module I
started out with some years ago and which has made its
way into mxODBC as mx.ODBC.Misc.dbinfo (this module aims
at translating abstract schemas into specific database ones
to support cross-database schema creation).

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/