DBI Commentary

[Gordon McMillan]

Timothy Grant writes:
[snip]
> First, embedding SQL in Python, even using the """SQL""" method is
> really ugly especially while keeping <tye4>indentation</tye4> consistent
> and it get's phenomenally ugly if the queries get at all complex. I
> don't think there is a solution to this problem, and I would hazzard a
> guess that no matter what language is in use it would be equally ugly.

You're right. Actually, in most languages, it's significantly 
uglier.

> Aahz made the very valid point that specifying columns in your query
> *guarantees* tuple return order. I had not fully appreciated that, and I
> have put that into use in a number of places. However, I have to agree
> with Lance when he says that "SELECT * FROM table" is not as often a bad
> thing as Aahz claimed. 

That was me, not Aahz. I don't know exactly what Lance was 
dealing with, but I'd guess that I wouldn't have done it that 
way. I will also grant you that the rules are quite different for a 
5 user app and a thousand user app. But "SELECT *" is 
inefficient on both ends, and it doesn't tell the DB or the DBA 
what you are interested in. It doesn't give the drivers any 
chance to prebind for efficient data transfer. And a getting the 
descriptor of the result set is another query.

If you want the descriptor for a table (and can't use 
DESCRIBE), get the descriptor for this :
"SELECT * from <table> where 1 = 0". Now store that 
somewhere.

> ... Using "SELECT field1, field2, field3 FROM table"
> simply becomes *far* to cumbersome and difficult to read/debug if you
> get beyond five or so fields. 

I should show you some raw ODBC code. Queries have up to 
50 columns, they are parameterized and the output columns 
are array bound. It takes 100s of lines of (very boring and very 
repetitive) C for each query. But it's about 5x faster than the 
(very popular) DB library it replaced.

> If you are dealing with a large number of
> columns (my current project has one table with 33). Debugging an error
> becomes an exercise in frustration (especially when using the """SQL"""
> method as the interpreter points the error message at the first line of
> the multi-line execute statement.

If you're using ODBC, turn ODBC tracing on. The log file will 
be huge, but it will tell you exactly what went wrong. If it's 
direct connect, the DB should have an equivalent facility.

If it's the Python you're worried about, define the SQL string 
above the execute, and execute the named string.

custbyzip = """\
 SELECT c_name, c_addr1, c_addr2, city, state, zip 
   FROM customer
   WHERE state = ? """

rslt = execute(custbyzip, ("MA",))
 
> Lance posted some very nice code to build dictionaries out of the
> results from a query... 

I've used the trick myself - but it was for building HTML tables 
to display the results from an ad-hoc query built from an HTML 
form. I personally would never use "*" if I new what columns I 
needed.


- Gordon