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]

su*********@gma il.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('dbn ame=billings user=steve password=xxxxx port=5432') curs = conn.cursor()
curs.execute("" "select table_name from information_sch ema.tables .... WHERE table_schema='p ublic' AND table_type='BAS E
TABLE'""") curs.fetchall() [('contacts',), ('invoicing',), ('lines',), ('task',), ('products',),
('project'
,)]

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/

[Ben Hutchings]

Steve Holden <st***@holdenwe b.com> wrote:

su*********@gma il.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_sch ema is a standard part
of ANSI SQL and is supported by several RDBMSes.

--
Ben Hutchings
It is easier to write an incorrect program than to understand a correct one.

[Steve Holden]

Ben Hutchings wrote:

Steve Holden <st***@holdenwe b.com> wrote:

su*********@g mail.com wrote:

I am trying to discover the schema of a PostgreSQL database
programatica lly 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_sch ema 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_sch ema. 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_sch ema:

from pprint import pprint
curs.execute("" "SELECT table_name FROM information_sch ema.tables
WHERE table_schema='p ublic' AND table_type='BAS E 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_maxim um_length,
numeric_precisi on, numeric_precisi on_radix,
numeric_scale, is_nullable
FROM information_sch ema.columns
WHERE table_name=%s
ORDER BY ordinal_positio n""", (tbl, ))
pprint(curs.fet chall())
regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/