Generic database dictionary access

Discussion in 'Python' started by Edward Diener, Jun 13, 2004.

  1. Version 2.0 of the Python database API was written over 5 years ago, in
    1999. While it has been used successfully by many implementations, there is
    no generic access into the data dictionary of relational databases except at
    the table column level. I am working on some Python which would hopefully
    give me a form of generic access to more common data dictionary
    functionality such as indices, constraints etc. but no such functionality
    currently exists in the official Python distribution. Has there been any
    movement in the 5 year time span since the 2.0 API was published to add
    richer generic functionality for relational databases as regards data
    dictionary access ? I fully realize that each major RDBMS has its own
    methods for programatically querying its data dictionary, and I suspect that
    there is no common SQL specification for doing so, but I would think that
    richer functionality along these lines might be welcome to Python database
    programmers other than myself.
     
    Edward Diener, Jun 13, 2004
    #1
    1. Advertising

  2. Edward Diener

    Michael Fuhr Guest

    "Edward Diener" <> writes:

    > Has there been any movement in the 5 year time span since the 2.0 API
    > was published to add richer generic functionality for relational
    > databases as regards data dictionary access ? I fully realize that each
    > major RDBMS has its own methods for programatically querying its data
    > dictionary, and I suspect that there is no common SQL specification for
    > doing so, but I would think that richer functionality along these lines
    > might be welcome to Python database programmers other than myself.


    SQL defines INFORMATION_SCHEMA as a standard way to query metadata,
    although not all databases implement it. Google for more info.

    Recent versions of PostgreSQL support INFORMATION_SCHEMA -- here's
    the doc:

    http://www.postgresql.org/docs/7.4/static/information-schema.html

    --
    Michael Fuhr
    http://www.fuhr.org/~mfuhr/
     
    Michael Fuhr, Jun 14, 2004
    #2
    1. Advertising

  3. Michael Fuhr wrote:
    > "Edward Diener" <> writes:
    >
    >> Has there been any movement in the 5 year time span since the 2.0 API
    >> was published to add richer generic functionality for relational
    >> databases as regards data dictionary access ? I fully realize that
    >> each major RDBMS has its own methods for programatically querying
    >> its data dictionary, and I suspect that there is no common SQL
    >> specification for doing so, but I would think that richer
    >> functionality along these lines might be welcome to Python database
    >> programmers other than myself.

    >
    > SQL defines INFORMATION_SCHEMA as a standard way to query metadata,
    > although not all databases implement it. Google for more info.


    Yes, I see it but it seems as if even some of the large databases don't
    support it ( unless I am mistaken, Oracle for instance ). Nonetheless it
    would be great if the Python database API were able to use it to create
    richer data dictionary information than what it provides. In particular
    constraints and unque key/foreign key links in tables if this could be
    discovered generically through the INFORMATION_SCHEMA standard methods.
     
    Edward Diener, Jun 14, 2004
    #3
  4. Edward Diener

    David Fraser Guest

    Edward Diener wrote:
    > Version 2.0 of the Python database API was written over 5 years ago, in
    > 1999. While it has been used successfully by many implementations, there is
    > no generic access into the data dictionary of relational databases except at
    > the table column level. I am working on some Python which would hopefully
    > give me a form of generic access to more common data dictionary
    > functionality such as indices, constraints etc. but no such functionality
    > currently exists in the official Python distribution. Has there been any
    > movement in the 5 year time span since the 2.0 API was published to add
    > richer generic functionality for relational databases as regards data
    > dictionary access ? I fully realize that each major RDBMS has its own
    > methods for programatically querying its data dictionary, and I suspect that
    > there is no common SQL specification for doing so, but I would think that
    > richer functionality along these lines might be welcome to Python database
    > programmers other than myself.
    >
    >

    Hear Hear. This would be a welcome improvement. Note that you could
    create it as a library that runs on top of DB-API drivers and knows
    various flavours of database, and then work towards standardizing the
    interface... I for one would be interested in the library

    David
     
    David Fraser, Jun 14, 2004
    #4
  5. David Fraser wrote:
    > Edward Diener wrote:
    >> Version 2.0 of the Python database API was written over 5 years ago,
    >> in 1999. While it has been used successfully by many
    >> implementations, there is no generic access into the data dictionary
    >> of relational databases except at the table column level. I am
    >> working on some Python which would hopefully give me a form of
    >> generic access to more common data dictionary functionality such as
    >> indices, constraints etc. but no such functionality currently exists
    >> in the official Python distribution. Has there been any movement in
    >> the 5 year time span since the 2.0 API was published to add richer
    >> generic functionality for relational databases as regards data
    >> dictionary access ? I fully realize that each major RDBMS has its
    >> own methods for programatically querying its data dictionary, and I
    >> suspect that there is no common SQL specification for doing so, but
    >> I would think that richer functionality along these lines might be
    >> welcome to Python database programmers other than myself.
    >>
    >>

    > Hear Hear. This would be a welcome improvement. Note that you could
    > create it as a library that runs on top of DB-API drivers and knows
    > various flavours of database, and then work towards standardizing the
    > interface... I for one would be interested in the library


    As Mr. Fuhr has pointed out, there is an SQL standard for it in
    INFORMATION_SCHEMA. One could attempt to build a base class for it using
    this and a set of functions which exposed the data dictionary through it,
    and then derive from that class and override its functionality for databases
    which don't support it but provide different methods to access the data
    dictionary data.

    Finding out which databases do support it, and to what extent, would be the
    first hurdle. Then deciding what the class functionality desired would be
    the next. Of course writing the correct SQL to get what one wants would be
    the final challenge.
     
    Edward Diener, Jun 15, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Murat Tasan
    Replies:
    1
    Views:
    8,131
    Chaitanya
    Feb 3, 2009
  2. Replies:
    2
    Views:
    480
  3. Ilias Lazaridis
    Replies:
    6
    Views:
    485
    Ilias Lazaridis
    Feb 21, 2006
  4. Monty
    Replies:
    3
    Views:
    1,859
    Walter Wang [MSFT]
    Nov 6, 2007
  5. minlearn
    Replies:
    2
    Views:
    494
    red floyd
    Mar 13, 2009
Loading...

Share This Page