Ben Hutchings wrote:
> Steve Holden <[EMAIL PROTECTED]> wrote:
>
>>[EMAIL PROTECTED] 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/
--
http://mail.python.org/mailman/listinfo/python-list
