SQL-Statement with Python

[Steve Holden]

"Alex Martelli" <aleaxit at yahoo.com> wrote:
> "Steve Holden" <sholden at holdenweb.com> wrote:
>     [snip]

> > > 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"
> )
Interesting to note such stylistic differences. My own preferred notation
for this statement would be something like:

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?).
>
Simply irony. I'm learning to do without it, as it's often misunderstood in
the USA, just as sarcasm tends to be mistaken for aggression.

[ ... ]
>
> 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
>
For this type of construct I tend to use the collection syntax -- I should
emphasise that while I've coded a lot of VBScript I have hardly used VB at
all.

So my equivalent style (not the same code, since I'm lifting this from an
existing web page) would be:

Stmt="SELECT NwsNum, NwsStart, NwsEnd, NwsHead FROM newsitem ORDER BY
"&StrFld
Set RS=Oconn.Execute(Stmt)
%>
<h2>News Items Listed by <%=strHdr%></h2>
<p>Click on the item number to edit the data.</p>
<table>
<%
Do While Not RS.EOF %>
    <tr>
    <td><a href="FormNews.asp?key=<%=RS("NwsNum")%>">
        <%=RS("NwsNum")%></a></td>
    <td> </td><td><%=RS("NwsStart")%> - <%=RS("NwsEnd")%>
        </td><td><%=RS("NwsHead")%></td>
    </tr> <%
    RS.MoveNext
Loop %>
    <tr>
    <td><a href="FormNews.asp"><NEW></a></td>
    <td> </td><td>Create a news item</td>
    </tr>
</table>

But this makes each field reference very "bulky": teh recordset name, then
the field name surrounded by quotes and parentheses. However, this may well
not use the GetRows() method at all, and be something quite other than what
you are suggesting. I try to avoid looking too far "under the hood" at this
stuff.

[ ... ]
>
> 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:-).
>
let's-not-optimize-what-doesn't-need-optimizing-ly y'rs  - steve