[DB-SIG] Other extensions

[Michael Bayer]

On May 15, 2007, at 12:38 PM, M.-A. Lemburg wrote:

> On 2007-05-15 18:06, Art Protin wrote:
>> Dear folks,
>>     I have lots more questions about ways that the API could and  
>> possibly
>> should be enriched.
>
> There have been some discussions about this, but since no standard
> API could be found, no additions to the DB-API were made.
>
> ODBC has a very complete set of catalog functions for querying
> meta-data of a database. It works by creating result sets that you
> can then fetch using the standard DB-API tools, so it's fairly
> straight forward to use.
>
> Internally, most ODBC drivers map these function calls to standard
> SQL SELECTs which work against the internal system tables or call
> special stored procedures in the database to create the result sets.
>
> I suppose the same could be done at a Python interface level (which
> could be the DB-API level or a level above the DB-API).

the "standard" for database metadata are the information_schema  
tables/views, they are part of ANSI 2003.  Currently, there is  
support for information_schema in postgres, mysql, SQL Server 7, and  
possibly Oracle.  At least for the PG/mysql implementations, they are  
not compatible with each other and in the case of MySQL does not even  
provide complete information as compared its built-in commands.  also  
information_schema is implemented as views within PG and have some  
performance issues.

Id also say that the structure of information_schema is way too  
complicated for typical usage and is not intuitive at all...then  
again an API/schema that is intentionally simplified might not  
provide full flexibility.


>> A. Stored Procedures:
>> 1.    The API provides a method for calling a stored procedure.   
>> Has there
>>     been any discussion about how a user/application might  
>> discover the
>>     names of such store procedures?
>> 2.   Has there been any discussion of how a user/application might  
>> create
>>     a stored procedure?
>
> This can normally be done using standard .execute() calls.

.callproc is better since it accounts for in/out parameters.

>
>> C. Non-SQL Queries
>> 1. Has there been any discussion of how a user/application should  
>> present
>>     queries that are in some other query language?
>
> No. The DB-API is about relational databases and SQL as query
> language. The interfaces may also be suitable for other query
> languages, but that's out of scope for the DB-API.

agreed