DBI Commentary
Gordon McMillan
gmcm at hypernet.com
Thu Jan 20 22:51:00 EST 2000
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
More information about the Python-list
mailing list