Find out the schema with psycopg?

[survivalist]

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?

Thanks in advance,

-S.

 

[Steve Holden]

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.
.... WHERE table_schema='public' AND table_type='BASE
TABLE'""")[('contacts',), ('invoicing',), ('lines',), ('task',), ('products',),
('project'
,)]
regards
Steve

 

[Ben Hutchings]

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.

 

[Steve Holden]

<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