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@python.org
http://mail.python.org/mailman/listinfo/db-sig
