[DB-SIG] Other extensions
M.-A. Lemburg
mal at egenix.com
Tue May 15 20:36:27 CEST 2007
On 2007-05-15 19:58, Art Protin wrote:
> Dear Marc-Andre, et al,
> 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).
>>
>>
>>
> No, this can not be done in a standard way above the DB-API level.
> There is nothing
> in the DB-API specification that can be used in a DBMS independent
> manner, that
> would be assured of producing the answers. One would need to presume
> that all
> DBMSs have SQL access to the systems tables and ours for one does not.
>
> I value a simple and clean interface. I feel that nothing should be
> added to the API
> can be built from the tools that API provides. I do not see how the
> existing API provides
> enough functionality to get this data in a generic way.
This depends on the database backend. Most of them provide
system tables with the needed information in one form or
another.
Others don't and require using special interfaces (at C level).
It's difficult to standardize. ODBC has gotten this pretty well
sorted out, IMHO, but emulating it in the DB API would be
quite difficult for the module authors.
>>> 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.
>>
>>
> Please explain how this is done, as I believe that there is no way
> provided in the
> API to do it in a standard or DBMS independent manner.
There is no DBMS independent way of dealing with stored procedures.
However, most DBMS that support stored procedures (and that I know
of :-)) allow creation of these using cursor.execute().
Some require using database specific tools and sometimes the
whole process of adding stored procedures lies completely
outside the scope of a database interface - instead you write
Java, C, Python or some other language code and plug this
directly into the database engine.
>>> * My implementation has made some attempt to address this. All of our
>>> queries are "named" and "stored" but they are either stored with the
>>> session (connection) or with the user account (as provided in
>>> connect()).
>>> Everything stored with the session vanishes when the connection
>>> closes
>>> and everything stored with user account is visible by all connections
>>> using that account. Thus I made visible objects of the class
>>> Server (via
>>> an attribute of connection objects), keep all the account info
>>> there and
>>> provided some methods on server objects to create persistent named
>>> queries and to control access to them by other accounts. I have no
>>> method to destroy a persistent query yet.
>>>
>>
>> Like everything that deals with stored procedures, this is highly
>> database specific.
>>
>>
>>
> Yes, but is there any commonality in what the different interfaces could
> offer
> that could be the basis for defining general approaches?
Apart from what .callproc() offers ? I don't think so.
>>> B. Metadata
>>> Not all DBMSs provide SQL access to the system tables. In fact, the
>>> DBMS I work with most is one that doesn't.
>>> 1. Has there been a discussion yet about how a user/application might do
>>> discovery of the table names?
>>> 2. and the column names with a table?
>>> 3. and the types of the columns?
>>> * My implementation has done naught to address this limitation.
>>>
>>
>> See our mxODBC interface for how this can be done via catalog
>> methods:
>>
>> http://www.egenix.com/products/python/mxODBC/
>>
>>
>>
>>> 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.
>>
>>
>>
> It seems strange to me to contradict the editor of the specification
> about what the
> specification says. However, I do not find anything in the first
> hundred lines that
> mention either "relational" or "SQL", rather it talks about "Database
> Interfacing"
> and queries.
> I have no problem at all with placing the utmost priority in making sure
> that the
> API works with SQL queries on relational DBMSs, but I have no respect for
> efforts to make it only work with relational DBMSs or only work with SQL.
>
> If my DBMS is the only one that has an alternative to SQL, then it makes no
> sense to try to "standardize" alternative languages, and I accept that.
> I do not
> accept that the API must somehow limit itself to SQL even when there is a
> common need for more.
>
> When no one else comes forward with a similar need, I will assume that
> there is
> no one else who has such a need or interest and that is reason enough to
> drop
> this debate.
Well, the DB-SIG list is called "Python Tabular Databases SIG"
and so far we've all been talking about SQL as query language
which by itself already is a rather broad scope due to the many
different dialects.
Like I said: if the interface is also usable for other query
languages that's fine, but the spec itself is being designed
against SQL.
We already have a problem with the parameter markers. I wouldn't
want to open yet another can of worms ;-)
>>> 2. Has there been any discussion of the representation of query language
>>> names?
>>> * My implementation had to address this because our DBMS has its own
>>> preferred query language and management requires that I provide
>>> access
>>> to it (which I accept as perfectly reasonable). To avoid
>>> confusion that might
>>> arise when trying to recognize the difference between it and SQL,
>>> I simply
>>> added extension methods like Cursor.exec_alt(prog, parm, keys) where
>>> prog is just the (non-SQL) program in a string, parm is just
>>> parameters for
>>> the query (just like for .execute()) and keys is a list of keys to
>>> use when
>>> parm is a dictionary (to linearize the parameters for handing off
>>> to the DBMS).
>>> But this does not address how a third party application might
>>> discover that
>>> an alternative language is available nor how it would know how to
>>> pass
>>> such a query from a sophisticated user to this alternative method.
>>>
>>
>> Unless the .execute() method signature doesn't provide the
>> necessary detail, I'd generally do this by passing an additional
>> (keyword) parameter to .execute().
>>
>>
>>
> I could easily adopt such an approach. What about the next revision
> having some
> mention of such addition parameters to .execute()? Would that be
> something like
> lang='SQL'
> being the default that a user could override?
All such extensions would be module specific. This is needed
to recognize places in your code that need adjusting in case
you want to port to a different module.
>> I don't think that the DB-API should require a mechanism for
>> querying the query language as this is normally always SQL (in
>> some dialect).
>>
>>
> Only if no one else needs something besides SQL.
>
> Moreover, I did not expect such a mechanism would be required. Rather,
> I had
> expected it to be the recognized optional mechanism. If an interface
> does not
> support anything but SQL, it would not need the mechanism. A
> user/application
> could look for the method or attribute and know the default (that only
> SQL is
> supported) by the absense.
>
> The issue is it is much much better that implementers use the same common
> extensions when they will suffice and not do yet another unique solution.
> We will however add what ever extensions are needed to make the features
> of our unique DBMS available to our users. SO it is all about balance.
As always :-)
I know of a couple of databases that e.g. allow use of different
SQL dialects. They can, for example, emulate Oracle or SQL Server
SQL syntax and semantics.
In order for this to work, you have to pass in connection
parameters. The SQL dialect is not changeable after connect.
mxODBC supports this, but only through virtue of being able
to pass connection strings via a special DriverConnect()
connection API.
The same approach can be used to configure other parameters
of a connection, e.g. read-only connections, code pages,
optimizations, etc.
>>> I doubt this is a complete list, but my mind has gotten empty
>>> while writing
>>> this so I will send it as is.
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Source (#1, May 15 2007)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
More information about the DB-SIG
mailing list