Eddy:<br> 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.<br>
<br> 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.<br>
--<br>Vernon Cole<br><div class="gmail_quote"><br>On Mon, May 14, 2012 at 8:33 AM, Edward Welbourne <span dir="ltr"><<a href="mailto:eddy@chaos.org.uk" target="_blank">eddy@chaos.org.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi DB-sig,<br>
<br>
I'm digging data out of my mobile 'phone so that I don't lose it during<br>
an upgrade. It turns out to hold my address-book in a sqlite database,<br>
so I duly connected to that using the pysqlite2 package; which<br>
implements PEP 249 (DB API 2.0) - indeed, the nearest it gets to<br>
documentation was something google found me that mentioned it<br>
implemented the PEP. (Hint to the maintainer if reading this: the<br>
.__doc__ of either pysqlite2 or its .dbapi2 would be a good place to say<br>
that, ideally complete with the PEP's URL.)<br>
<br>
I looked at SQL references and at the PEP but nothing tells me a<br>
standardised way to ask a database its list of tables. SQL tutorials<br>
seem to assume I created the tables, so obviously know their names; the<br>
explorer trying to make sense of a found database isn't considered. It<br>
would seem that each database has its own idiosyncratic way of getting<br>
such a list, typically be selecting from a special table, whose name<br>
varies from database to database, as does the column name for the names<br>
of tables.<br>
<br>
Oracle:<br>
SELECT table_name FROM user_tables<br>
(or SELECT owner, table_name FROM all_tables, or FROM dba_tables).<br>
<br>
SQLite:<br>
SELECT name FROM sqlite_master WHERE type='table';<br>
Notice the need for a WHERE clause, in this case.<br>
<br>
MS's SQL server uses meta-table sys.tables, if my googling is accurate,<br>
but the source didn't say what column-name to ask for.<br>
<br>
Most of SQL is sufficiently standardised to permit writing portable code<br>
to explore a database - provided access compatible with PEP 249 is<br>
available - without need to know details of the SQL implementation; so<br>
it would be nice if some future version of the DB API were to specify a<br>
standard method, of connection objects, that can be used to obtain the<br>
list of tables available via the connection.<br>
<br>
I can imagine that most of the other information in meta-tables and<br>
kindred magic is only actually useful in conjunction with other<br>
implementation-specific features of the database, so not worth exposing<br>
in a portable form; but having a list of the table-names I can use in<br>
plain SQL queries is useful in general, without need for reference to<br>
(other) implementation details.<br>
<br>
A sufficient solution - for the three sample databases for which I found<br>
details - would be to provide a string global of the module,<br>
implementing the DB API, whose value is an SQL string that can be passed<br>
as the command to its .connect(whatever).cursor().execute(command); but<br>
I suspect it would make better sense to provide a separate method of<br>
connection objects, that simply returns a list of (or iterator over)<br>
table names.<br>
<br>
Eddy.<br>
<span class="HOEnZb"><font color="#888888">--<br>
Note: I'm not on DB-sig, so please keep me overtly CC'd in replies, if<br>
you care whether I see them !<br>
_______________________________________________<br>
DB-SIG maillist - <a href="mailto:DB-SIG@python.org">DB-SIG@python.org</a><br>
<a href="http://mail.python.org/mailman/listinfo/db-sig" target="_blank">http://mail.python.org/mailman/listinfo/db-sig</a><br>
</font></span></blockquote></div><br>