473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Find out the schema with psycopg?

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.

Dec 22 '05 #1
3 10533
su*********@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.
import psycopg2 as db
conn = db.connect('dbname=billings user=steve password=xxxxx port=5432') curs = conn.cursor()
curs.execute("""select table_name from information_schema.tables .... WHERE table_schema='public' AND table_type='BASE
TABLE'""") curs.fetchall() [('contacts',), ('invoicing',), ('lines',), ('task',), ('products',),
('project'
,)]


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/

Dec 22 '05 #2
Steve Holden <st***@holdenweb.com> wrote:
su*********@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.

--
Ben Hutchings
It is easier to write an incorrect program than to understand a correct one.
Dec 24 '05 #3
Ben Hutchings wrote:
Steve Holden <st***@holdenweb.com> wrote:
su*********@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/

Dec 24 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: AP | last post by:
Hello, I'm trying to use .NET to validate incoming XML documents against the appropriate schema in our database. I do not want to use the document's schemaLocation attribute to validate for...
2
by: Tarren | last post by:
Hi: The problem I am having is when I validate an xml file to a schema, it is erroring out every element. I think this has something to do with me defining/referencing the namespaces. I have...
0
by: Harold Putman | last post by:
The XmlValidatingReader seems to have changed drastically in .Net Frameworks 1.0 SP3 and beyond with regards to validating XML files that are XML Schemas. Consider the following code: ---...
1
by: John A Grandy | last post by:
Visual Studio 2005 RC. I create my first ASP.NET 2.0 Web Site at c:\code\my-app , and when I attempt to compile I receive about 30 errors related to the compiler not being able to find schema...
0
by: Derek | last post by:
I am creating an intranet using Visual Web Developer Express Edition. Everything has been working OK until yesterday when I started getting 62 messages all beginning "Could not find schema...
5
by: Rachel | last post by:
Hi, I currently have several sections pointing to various custom providers inside my Web.conf file. I get multiple messages about not being able to find schema information. ("Could not find...
3
by: Neilg | last post by:
Does any know what the messsage "could not find schema information... happens with Visual Studio 2005 and how to fix it? Thanks -- Neil Gott
0
by: jhansen | last post by:
I am getting informational warnings about the following app.config and indicate the following. I am using C# visual studio 2005 and used the Settings.settings to set up my values scoped as...
1
by: amir | last post by:
Hi, When compiling a page in VS2005 this morning I received 101 messages regarding schema problems in my web.config file. When I go to view an aspx page in my IIS, IE just displays a blank...
2
by: Mark | last post by:
Hi... I've been trying the .Validate() method on the XmlDocument to validate some xml against a schema, but one thing I noted was that unless the document explicitly declares the schema as a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.