[DB-SIG] DB API 3.0 strawman

Andy Dustman andy@dustman.net
Mon, 6 Aug 2001 19:47:27 -0400 (EDT)

On Mon, 6 Aug 2001, Stuart Bishop wrote:

>     Connection Object
> 	quote(object)
> 	    Returns a ANSI SQL quoted version of the given value as a
> 	    string.  For example:
> 		>>> print con.quote(42)
> 		42
> 		>>> print con.quote("Don't do that!")
> 		'Don''t do that!'
> 	    Note that quoted dates often have a RDBMS dependant syntax
> 	    (eg. "TO_DATE('01-Jan-2001 12:01','DD-MMM-YYYY H24:MI')" for
> 	    Oracle). I need to track down the official ANSI SQL 1992
> 	    or 1999 compliant syntax for specifying date/time/and datetime
> 	    datatype as strings (if it exists).
> 	    The quote method is invaluable for generating logs of SQL
> 	    commands or for dynamically generating SQL queries.
> 	    This method may be made a module level function as opposed
> 	    to a Connection method if it can be show that string quoting
> 	    will always be RDBMS independant.

MySQLdb-1.0.0 will expose a conn.literal() method. It seems that your
quote() does the same thing. I rejected quote() as a name because
literal() seems to better reflect the result, in my mind, at least. I
could live with quote() as a name, though. (I've never needed such a
method for my own code, but it was requested, and adding it allowed me to
simplify some of the cursor code a bit.) It's also a connection object
method, rather than a module function, because MySQL makes use of
character sets for it's quoting mechanism, and these are maintained on
per-connection bases. Actually, there is a module function version of it
as well, which more or less assumes ASCII. (The method calls
mysql_real_escape_string(), the function calls mysql_escape_string(). In
fact, the function is really an unbound method, in a sense...)

>     Row Object
> 	When a Cursor is iterated over, it returns Row objects.
> 	dtuple.py (http://www.lyra.org/greg/python/) provides such an
> 	implementation already.
> 	[index_or_key]
> 	    Retrieve a field from the Row. If index_or_key is an integer,
> 	    the column of the field is referenced by number (with the first
> 	    column index 0). If index_or_key is a string, the column is
> 	    referenced by its lowercased name (lowercased to avoid problems
> 	    with the differing methods vendors use to capitalize their column
> 	    names).

An interesting idea. MySQLdb has different Cursor classes, one of which is
DictCursor, which returns dictionaries instead of tuples. If I understand
the above correctly, Row objects would act as tuples AND dictionaries.
However, I think column names should not be molested. Most SQL
implementations are case-insensitive, AFAIK, but MySQL is not. FOO and foo
and two different columns (though it is a dumb idea to do this). Perhaps
each driver should supply it's own Row class, and some implementations
could choose to use case-insensitive keys.

(Lack of comments does not mean I agree on other points...)

Andy Dustman         PGP: 0xC72F3F1D
    @       .net     http://dustman.net/andy
I'll give spammers one bite of the apple, but they'll
have to guess which bite has the razor blade in it.