On 15/05/12 12:06 PM, Vernon Cole wrote:
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 <e...@chaos.org.uk
<mailto:e...@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 !
This is hard. Gerald [1] attempts to provide a compact but meaningful
and consistent data dictionary for a number of backends (currently
Oracle, MySQL and Postgres). Patches or suggestions for improvement -
including a module for SQLite - are welcome.
The industry standard (if such a term can be used) is for relational
databases to provide an INFORMATION_SCHEMA [2]. The problem with this is
that where it is provided (and AFAIK SQLite doesn't) not all databases
are equal (MySQL doesn't provide 'referential_constraints' for instance)
and some key pieces of information aren't included. Although to be fair
these are usually elements such as tablespace which are implementation
specific so wouldn't be expected to be available in 'standard' views.
[1] http://halfcooked.com/code/gerald/
[2] https://en.wikipedia.org/wiki/Information_Schema
Regards,
Andy
--
From the desk of Andrew J Todd esq - http://www.halfcooked.com/
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
