Find out the schema with psycopg?

[Steve Holden]

survivalist at gmail.com wrote:
> I am trying to discover the schema of a PostgreSQL database
> programatically at runtime.
> 
> I'm using psycopg (I assume that's still the best library).  Is there a
> way to query the schema other than opening a system pipe like "psql -d
> '\d'", "psql -d '\d tablename'", etc.?
> 
> DBIAPI 2.0 shows that the Cursor object has a .description method that
> describes rows, and there's the Type object.  But neither of these
> appear to give you table names.
> 
> Is there something else I should look at?

Yes, but as with so many of these things you'll have to accept it's a 
platform-specific (i.e. non-portable) solution, and it requires that you 
are running PostgreSQL 7.4 or higher. Under those circumstances you can 
query the metadata through the information schema.

 >>> import psycopg2 as db
 >>> conn = db.connect('dbname=billings user=steve password=xxxxx 
port=5432')
 >>> curs = conn.cursor()
 >>> curs.execute("""select table_name from information_schema.tables
...                 WHERE table_schema='public' AND table_type='BASE 
TABLE'""")
 >>> curs.fetchall()
[('contacts',), ('invoicing',), ('lines',), ('task',), ('products',), 
('project'
,)]
 >>>

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC                     www.holdenweb.com
PyCon TX 2006                  www.python.org/pycon/