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