Generic database dictionary access

[Edward Diener]

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.