Find out the schema with psycopg?
steve at holdenweb.com
Sat Dec 24 10:54:03 CET 2005
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.
> 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 for x in curs.fetchall())
for tbl in tables:
curs.execute("""SELECT column_name, data_type,
ORDER BY ordinal_position""", (tbl, ))
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/
More information about the Python-list