SQL-Statement with Python

[Alex Martelli]

"Steve Holden" <sholden at holdenweb.com> wrote in message
news:K4596.1054$DI.17324 at e420r-atl1.usenetserver.com...
    [snip]
> > Anyway, if that bothers you too much you get to choose among a
> > zillion other ways to ask a recordset for its contents, e.g....:
> >
> > for row in recordset.GetString()[:-1].split('\r'):
> >     first, last = row.split('\t')
> >     print "%s, %s" % (last, first)
> >
> > Or, simpler and faster, one can show off one's command of 'advanced'
> > SQL:-), and get the *db engine* to do one's work:
> >
> > recordset, result = connection.Execute(
> >   "Select LastName//', '//FirstName From Employees Order By LastName")
> > print recordset.GetString().replace('\r','\n')
> >
> pretty good, though I would suggest "... Order By LastName, FirstName" for
a
> definitive ordering. Wow, I wish my SQL was that good :-) And in fact I'm
> one of those who tend to write SQL keywords in UPPER CASE because that's
> what I'm used to.

Upper-case keywords is indeed the prevalent style, as is splitting SQL
statements up into clauses, also for readability:

recordset, result = connection.Execute(
    "SELECT LastName//', '//FirstName"
    "  FROM Employees"
    "  ORDER BY LastName, FirstName"
)

I guess this IS best, given the complexity of certain SQL statements -- it
just doesn't come natural to me when plonking down some simple little
thingy.  My fault.  (Not sure what the 'wish my SQL was that good' quip is
meant for -- IS there some engine that doesnt do such simple things right?).


> > Explanation for _what_ 'it'?  I must be confused...
> >
> "It" being the row-major vs. column-major ordering. I had completely
> overlooked the fact that Microsoft tend to design their subsystems to play
> with *their* languages. Don't talk to me about world domination!

Well, I'm sure Fortran programmers using SQL via ADO (there must be
a BUNCH of those out there...) are also happy.  I'm not aware of other
languages oriented to column-major matrices, though I'll bet there are.

But that's not really the point here.  The VB/VBscript programmer
will have to code nested indexed loops anyway since the language
does not support equivalence between 2d arrays and arrays-of-arrays;
eg, see http://www.stardeveloper.com/asp_getrows_4.asp for a typical:

         ' Showing Each Row
         For i = 0 To UBound(ds, 2)
            Response.Write "<tr>" & vbcrlf
            ' Showing Each Column
            For j = 0 To UBound(ds, 1)
               Response.Write "<td>"
               Response.Write ds(j, i)
               Response.Write "</td>"
               Next
            Response.Write "</tr>" & vbcrlf
         Next

where ds is the object (a VB/VBscript array) that GetRows returns.

Python equivalent would be something like (warning -- untested!):

# showing each row
for i in range(len(ds[0])):
    Response.Write("<tr>\n")
    # showing each column
    for j in range(len(ds)):
        Response.Write("<td>%s</td>" % ds[j][i])
    Response.Write("</tr>\n")

not really any less convenient than the VBScript equivalent above,
it seems to me.  The (little) defect in both cases is having to
iterate by index when iterating on content directly would be more
convenient and simpler if it were feasible ('For Each' in VBScript,
plain Python for without the range(len(...)) rigmarole) -- it's
not, in VBScript because of the non-equivalence of 2D arrays to
arrays of arrays, in Python because of the column-major thingy.

Had GetRows returned an array of arrays, life would be simpler
in *both* scripting languages!  Oh well... all in all, it's not
really a major issue, just one more little design booboo (it
stands out more in Python because the language itself is so free
from such little errors & irregularities -- in VBScript, one
does not notice it because there are so many in the language
itself, that, "what's one more between friends"!-).


Another viable approach, if you feel so strongly about it, is (and
this will surprise nobody) to allow iteration the way GvR intended.

You can do it simply, by transposing the wrong-way-round matrix
once and for all, e.g:

def transpose(matrix2d):
    result = [[] for i in range(len(matrix2d[0]))]
    for row in maxtrix2d:
        for i in range(len(row)):
            result[i].append(row[i])
    return result

which seems simplest to me; this allows recoding the above
Python snippet as the marginally clearer/more convenient:

for row in transpose(ds):
    Response.Write("<tr>\n")
    for col in row:
        Response.Write("<td>%s</td>" % col)
    Response.Write("</tr>\n")

Since only references are copied anyway, this is not too bad,
I think.  Still, one MIGHT get fancy:

class transpose:
    class RowWrapper:
        def __init__(self, matrix, index):
            self.matrix = matrix
            self.index = index
        def __getitem__(self, otherindex):
            return self.matrix[otherindex][self.index]
    def __init__(self, matrix):
        self.matrix = matrix
    def __getitem__(self, index):
        if index >= len(self.matrix[0]):
            raise IndexError, index
        return self.RowWrapper(self.matrix, index)

with the same Python snippet.  But I honestly fail to
see any advantage to this abstruse approach -- seems
substantially more complex for no performance gain,
indeed I suspect it would perform less well (but I
have not taken the trouble to time it:-).


Alex