[DB-SIG] Optional DB API Extensions

Andy Dustman andy@dustman.net
25 Oct 2001 10:28:19 -0400


On Thu, 2001-10-25 at 06:52, Stuart Bishop wrote:

> I also would like the connection method:
>          quote(object)
> 
>              Returns a SQL quoted version of the given variable as a
>              string, suitable for execution by the database backend.

MySQLdb does this, though the method is connection.literal(object). It
looks up the correct conversion function for that type/class in
connection.converter (defaulting to str()) and then escapes any special
characters. If a non-string sequence is passed, the sequence is
preserved and the objects in the sequences are converted.

> I'd also add cursor methods for Python 2.2 iterators:
> 
>          next()
> 
>              Return the next row from the currently executing SQL 
> statement.
>              A StopIteration exception is raised when the result set is 
> exhausted.
>              This method will raise a NameError exception if run under 
> versions of
>              Python earlier than Python 2.2.

This can be implemented as:

    def next(self):
        row = self.fetchone()
        if row: return row
        else: raise StopIteration

In fact if you run this on earlier versions of Python, you will get a
NameError when the result set is exhausted, unless you've otherwise
defined StopIteration. It seems like this (and __iter__()) could be
implemented for all Python versions, even pre-2.2 where iterators do not
exist (and these methods are unlikely to be used in those versions
anyway).

> Another optional method may be a call to have the fetchXXX methods return
> dictionaries rather than sequences, as this seems to be a common 
> extension
> and feature request.

MySQLdb is extended to use user-defined Cursor classes to produce
abnormal behavior, such as DictCursor returning dictionaries. As has
been pointed out, your query may return two columns of the same name.
DictCursor deals with this by using table.column as the key for the
second column of the same name. Preferably, queries should *not* return
two columns with the same name: One should be renamed with the SQL
column AS alias syntax. Another cursor class, OldDictCursor, was defined
for compatibility with the old MySQLmodule, and sets all keys to
table.column.
 
-- 
Andy Dustman         PGP: 0x930B8AB6
    @       .net     http://dustman.net/andy
You can have my keys when you pry them from my dead, cold neurons.