Dearest DBAPI authors, I have lurked with great interest on this board for quite some time, as the topics are generally near and dear to my heart. I believe I can say that I have used nearly all of your drivers over the last ten years. I have purchased Marc's products at least a dozen times on behalf of my customers, and I will generally favor a python DBAPI solution to the data transportation issue du jeur if one is available.
I'm not sure that my opinion is even worthy of consideration in this discussion, but I offer it nonetheless. My company specializes in the development of interfaces and "glue" applications. These are typically in the form of either a web or desktop application, or a background service that serves to automate, merge, and facilitate the presentation of various disparate internal systems into a more or less seamless facade. Python is our tool of choice because it allows us to construct our systems using tried-and-true modular design patterns, quickly, and without unnecessary licensing or technical restrictions. Our projects range in size from several hours of development to 1000 hours or so, with the typical project being best expressed in terms of days or a few weeks. As such, over the years our internal subversion repository has several thousand one-off tools developed with DBAPI in all different environments, primarily for data munging and conversion, and perhaps 40 or 50 "systems" that are deployed throughout our base of customers. I have developers ranging from young and inexperienced to old and wise on my team. My present concerns revolve around adopting internal standards that maximize the value of any code we write. Naturally, maximizing the opportunity for reuse of both techniques and entire code modules is important. This may not be the case throughout your user base, but in my case I am faced with the fact that human time is expensive, and machine time is cheap. So all of my effort is in the ongoing net reduction of human time, thereby increasing the value to my customers. It is in this context that I perceive 100% of the value of even having such a thing as DBAPI. With all due respect to the many years of effort that many of you have spent to assist me and people like me, I must confess that in my case the DBAPI itself is only as valuable as its ability to allow me to supplant the underlying database with minimal impact on my code. Perhaps I am in a fortunate niche, because I am frequently able to get by with a very minimal subset of standard SQL, and minimal use of database specific features. This is by choice, so as to maximize my ability to handle the things that are frequently outside of my control such as wholesale change in one or more of the domain specific systems that I need to integrate with. All of you, as developers of DBAPI compliant modules, have undertaken to supply me with some level of consistency across what can only be described as an exceedingly non-standard set of underlying databases. The harsh facts revolve around what you cannot offer me: - any sort of consistency of data types, particularly numeric types - very little in terms of help, despite your efforts, with metadata - any ability to unify the various forms of fully qualified table references - any ability to get consistent locking behavior - any ability to get consistent query cache behavior - any ability to have reusable stored procedures - any ability to NOT have SQL and its inherent impedance mismatches in my code - a fixed point data type with speed comparable to floats But if not the hope for some sort of database independence, the only thing it seems DBAPI gives me to date is that I will have a module, a connection object, a cursor object with some execute methods, and a description of fields in my cursor for which I can only really count on the field name being consistently available. Through no fault of any of yours, I cannot even find a reasonable way to write routines that do datetime manipulation in a reusable fashion, because I don't know ahead of time whether I will be using an mxDateTime (which I consider to be unfortunately superior as well as non-standard) or a python datetime, or a java.util.date or a java.sql.date, or a CLR System.DateTime. >From my humble perspective Daniele made the most salient point of the discussion in reference to optional behavior making things more difficult for everyone rather than simpler. I believe that in reference to "standard behavior" such as proposed behavior for DBAPI this is true. The purpose of a standard should be to standardize. As such, I fully support the mandate that DBAPI have SOME SORT of standard parameter substitution behavior that I can rely upon consistently -- because selfishly I know this will inherently increase the value of any code for which I am able to develop to the standard. The unfortunate reality is that any application developer who competently deals with real life data where it lives can easily code around any differences in your drivers. I have a humorous set of wrappers, proxies, and custom modules for this purpose. So humbly, I suggest perhaps categorizing users along the classification of whether or not their intent is relative database independence. For those who are using your driver merely because it is the de-facto tool one must use to get access to the database, sadly I believe that DBAPI itself is just another layer of throwaway middleware -- and more likely to stop me, as an app developer, from utilizing the special feature I need, than to help me. But from the perspective of a user who could benefit a great deal from database independence, the DBAPI is my only hope. As to WHICH paramstyle is best, my opinion is nearly worthless, but I will give it anyway. Again, humbly, I prefer qmark. My reasoning for this is that I find myself doing a large amount of string manipulation in my code, and python is quite powerful in this respect. It would be trivial (albeit spectacularly time consuming) for me to adjust all of my code to NEVER need to pass parameters to a DBAPI method call. However, a large subset of my code set involves translations from another language or environment, and these are most frequently ODBC. The real reason I use parameters is in the hope that the SQL engine will prepare my statement and reduce my round trip time. But in reality in most environments there are either better alternatives (i.e. inserts and updates with a list of parameters instead of a loop with single set of parameters), or the gain is trivial (i.e. a query cache is used anyway, or I am front-ending with memcache because ALL DB access is too slow). The qmark, being positional, allows me to write the most abstract form of statements, and at least makes it clear in my code which "layer" is performing the substitution. Again I wish to express my gratitude for all of you in this sometimes thankless task! Your Fan, J. Kennedy
_______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
