SQL-Statement with Python

[Alex Martelli]

"Steve Holden" <sholden at holdenweb.com> wrote in message
news:OPg96.5664$DI.65547 at e420r-atl1.usenetserver.com...
    [snip]
> > 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 definitely don't like the triple quotes adjacent to the
string's substance, although I admit it's nice to avoid
repeating the quotes.  Would you settle for:

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

...?


> > 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.

I'm not in the US, but the above shows I'm quite capable of
missing it too (that it was a quip was clear, but I still did
not get the point -- still don't).


> 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.

I think all we're discussing is in both VB _and_ VBScript.


> So my equivalent style (not the same code, since I'm lifting this from an
> existing web page) would be:
    [snip]
> 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
    [snip]
> But this makes each field reference very "bulky": teh recordset name, then
> the field name surrounded by quotes and parentheses.

I don't mind this part, actually -- one could argue it's more
readable.  Given all the HTML/ASP cruft that surrounds the
substance here (and it's pretty typical -- and why I have a
slight stylistic preference for Response.Write), it's not a
bad thing that the substance stands out more.  Plus, names
rather than numeric indices are no bad thing either.  But...

> 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.

I tend to, because database performance issues tend to dominate
most DB uses.  Here, you'd better ensure you're getting a client-side
cursor, rather than a server-side one -- I forget if the default
value is certain when not specifying it, but 'explicit is better
than implicit', so, set Connection.CursorLocation to 3 (aka
adUseClient, but one rarely bothers with the symbolic names in
VBScript:-) to ensure client-side buffering &c.

You wouldn't want each MoveNext to have to hit the server again,
I'm sure... with a client-side cursor, you can also keep the
recordset and close the connection, pretty good practice from
a scalability standpoint if you're doing nothing more with it.


> > 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

I suspect we agree -- I was just showing the object wrapper
to ensure it wouldn't be suggested as an alternative in any
followups:-).


Alex