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

M.-A. Lemburg mal at egenix.com
Tue May 15 08:59:08 CEST 2012

Edward Welbourne wrote:
> Hi DB-sig,
> I'm digging data out of my mobile 'phone so that I don't lose it during
> an upgrade.  It turns out to hold my address-book in a sqlite database,
> so I duly connected to that using the pysqlite2 package; which
> implements PEP 249 (DB API 2.0) - indeed, the nearest it gets to
> documentation was something google found me that mentioned it
> implemented the PEP.  (Hint to the maintainer if reading this: the
> .__doc__ of either pysqlite2 or its .dbapi2 would be a good place to say
> that, ideally complete with the PEP's URL.)
> I looked at SQL references and at the PEP but nothing tells me a
> standardised way to ask a database its list of tables.  SQL tutorials
> seem to assume I created the tables, so obviously know their names; the
> explorer trying to make sense of a found database isn't considered.  It
> would seem that each database has its own idiosyncratic way of getting
> such a list, typically be selecting from a special table, whose name
> varies from database to database, as does the column name for the names
> of tables.
> Oracle:
> SELECT table_name FROM user_tables
> (or SELECT owner, table_name FROM all_tables, or FROM dba_tables).
> SQLite:
> SELECT name FROM sqlite_master WHERE type='table';
> Notice the need for a WHERE clause, in this case.
> MS's SQL server uses meta-table sys.tables, if my googling is accurate,
> but the source didn't say what column-name to ask for.
> Most of SQL is sufficiently standardised to permit writing portable code
> to explore a database - provided access compatible with PEP 249 is
> available - without need to know details of the SQL implementation; so
> it would be nice if some future version of the DB API were to specify a
> standard method, of connection objects, that can be used to obtain the
> list of tables available via the connection.
> I can imagine that most of the other information in meta-tables and
> kindred magic is only actually useful in conjunction with other
> implementation-specific features of the database, so not worth exposing
> in a portable form; but having a list of the table-names I can use in
> plain SQL queries is useful in general, without need for reference to
> (other) implementation details.
> A sufficient solution - for the three sample databases for which I found
> details - would be to provide a string global of the module,
> implementing the DB API, whose value is an SQL string that can be passed
> as the command to its .connect(whatever).cursor().execute(command); but
> I suspect it would make better sense to provide a separate method of
> connection objects, that simply returns a list of (or iterator over)
> table names.

What you're describing sounds a lot like the catalog methods in
the ODBC standard.

Those are typically implemented by either
having the ODBC drivers implement a set of queries that generate
result sets in a predefined format, or by adding views to the
database backend which implement them. Sometimes, both methods
are used.

The application can then call the catalog methods with a
set of parameters and the database will run the queries to
build the result set with meta data.

Even though most databases provide the needed information,
I'm not sure whether we should ask database module authors
to implement similar catalog methods. The reason is that
for some of them, the required information may not be
readily available in the database, or may require installing
views to allow users without full permissions to retrieve
the data from the system tables.

We could standardize those methods as optional add-ons to
the DB-API specification, though, so that those modules
which do want to implement them at least all use the
same methods, parameters and result set layouts.

If there is interest in this, I'd suggest to follow the
ODBC standard for this and use method names derived from
the ODBC API names. mxODBC implements these and also
documents the result set layouts:


Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, May 15 2012)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
2012-07-02: EuroPython 2012, Florence, Italy               48 days to go
2012-04-26: Released mxODBC 3.1.2                 http://egenix.com/go28
2012-04-25: Released eGenix mx Base 3.2.4         http://egenix.com/go27

::: Try our new mxODBC.Connect Python Database Interface 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

More information about the DB-SIG mailing list