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

[Vernon Cole]

Eddy:
  As you discovered, every different database system has a unique way of
getting a table listing.  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.  Mine will basically hook up to
anything _except_ sqlite. I have no idea which engine or engines may show
up at the other end.  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.

  I think that it would be wonderful if someone (you could volunteer) were
to make a package which does exactly what you suggest. That package would
have to determine what the underlying engine is, then give the correct
commands. If you write it, I will use it.  I'ld even contribute the query
for Microsft SQL tables.
--
Vernon Cole

On Mon, May 14, 2012 at 8:33 AM, Edward Welbourne <eddy at chaos.org.uk> 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.
>
>        Eddy.
> --
> Note: I'm not on DB-sig, so please keep me overtly CC'd in replies, if
> you care whether I see them !
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20120514/be6ecf03/attachment.html>