Style formating of multiline query, advise

[Steve Holden]

Scott David Daniels wrote:
> someone wrote:
>> what is good :) style for multiline queries to database?...
>> query = """ SELECT * FROM (
>>                    SELECT a.columna, a.columnb, a.iso
>>                       FROM all a
>>                       WHERE (a.name = LOWER(%s))  ) AS c
>>                  JOIN other as b on c.gid = b.id
>>                  WHERE class = 'A'
>>                  ORDER BY population DESC
>>                  LIMIT %s;"""
> 
> Also, for SQL, (A) why are you using nested joins?, and
> (B) always show what columns you want as output.  SELECT * is a hack
> to make interactive use easier, not a durable way to write queries.
> 
> query = """SELECT a.columna, a.columnb, a.iso, b.id, ...
>     FROM all AS a, other as b
>     WHERE a.name = LOWER(%s)
>      AND  a.gid = b.id
>      AND  b.class = 'A'
>     ORDER BY population DESC
>     LIMIT %s;"""

In more modern syntax this would be

SELECT a.columna, a.columnb, a.iso, b.id, ...
    FROM all AS a
         JOIN other AS b
           ON a.gid = b.id
     WHERE  a.name = LOWER(%s)
       AND  b.class = 'A'
    ORDER BY population DESC
    LIMIT %s;"""

or something similar. The fact that you don't write JOIN in your SQL
doesn't mean the database isn't performing a join to perform the query
... I agree the nested query was somewhat over the top.

regards
 Steve
-- 
Steve Holden           +1 571 484 6266   +1 800 494 3119
Holden Web LLC                 http://www.holdenweb.com/
Want to know? Come to PyCon - soon! http://us.pycon.org/