Find out the schema with psycopg?

[Steve Holden]

Ben Hutchings wrote:
> Steve Holden <steve at holdenweb.com> wrote:
> 
>>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.
> 
> <snip>
> 
> It's not *that* non-portable - information_schema is a standard part
> of ANSI SQL and is supported by several RDBMSes.
> 
Less non-portable than querying the metadata directly, certainly, and 
hopefully increasingly more portable as time goes by. Thanks for 
reminding me of this desirable attribute of the information_schema. I 
was wrong to say "platform-specific".

Other readers may be interested in a python snippet that gives them a 
little more information about their tables using the information_schema:

from pprint import pprint
curs.execute("""SELECT table_name FROM information_schema.tables
              WHERE table_schema='public' AND table_type='BASE TABLE'""")
tables = (x[0] for x in curs.fetchall())

for tbl in tables:
     print tbl.capitalize()
     print "="*len(tbl)
     curs.execute("""SELECT column_name, data_type, 
character_maximum_length,
                  numeric_precision, numeric_precision_radix, 
numeric_scale, is_nullable
                  FROM information_schema.columns
                  WHERE table_name=%s
                  ORDER BY ordinal_position""", (tbl, ))
     pprint(curs.fetchall())


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/