[DB-SIG] Getting a list of tables from a database

Edward Welbourne eddy at opera.com
Tue May 15 17:17:53 CEST 2012

Vernon Cole:
> The problem with attempting to make a uniform tool for doing that at  
> the PEP 249 level, is that some api packages (such as the one I  
> maintain for Microsoft ADO, and anything that does ODBC) may attach to  
> dozens of different engines.

Sounds painful.  I'm not a database expert - I've had enough contact
with them to understand how useful it is that PEP 249 spares me the
details ! - so let me check I understand.  ODBC is an abstraction layer
between databases, that lets you access diverse databases via a common
API, much as PEP 249 does, but at closer-to-SQL level.  You appear to be
saying its common API doesn't provide a sensible way to ask each of its
data-sources for its list of tables - the client of ODBC is spared most
of the details of the underlying database, but is still left out to dry
where it comes to getting a list of tables.  This naturally makes it
hard to implement a list of tables in the python interface to ODBC (and
similar abstraction layers).  Sounds like ODBC isn't quite abstract
enough !  ... or have I misunderstood you ?

> I attempt to let the user determine that by prividing the non-standard
> attributes connection.dbs_name and connection.dbs_version.  (I
> borrowed the idea from mxodbc.)  The most we should hope for at the
> PEP 249 level would be to have those attributes become part of the
> standard.

This simply pushes the problem off onto the client of the module,
obliging them to delve into the pecuiarities of each database, which
rather works against the idea of having an abstraction layer - although,
of course, the abstraction layer saves clients *most* of the need to
know details of the underlying system.  It's just frustrating to be left
with functionality that's naturally part of the abstraction but not
practical to include in it - due to an intervening abstraction layer
that doesn't bother with the part in question.

I think it would be better to specify the list-of-tables as an optional
method of connections in the DB API.  Those implementations that aren't
stuck on the wrong side of an upstream abstraction that lacks the
necessary functionality can then support it, which is better than no-one
being able to.

Marc-Andre Lemburg:
> What you're describing sounds a lot like the catalog methods in
> the ODBC standard.

That all went clear over the top of my head !
As I say above, I'm no expert on databases; I know barely enough to
scrape data off my old 'phone before an upgrade, with a lot of help from
google ...


More information about the DB-SIG mailing list