Style formating of multiline query, advise

[someone]

Hi,

what is good style for multiline queries to database?
Is that one ok?
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;"""

Regards, Pet

 

[John Machin]

Hi,

what is good style for multiline queries to database?
Is that one ok?
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;"""

There's no tablet of stone, like PEP 8 It seems to be a matter of
individual taste; clarity and consistency and not overdoing
parentheses count for me.

IMO you have too much leading whitespace, you have "as" in upper,
lower and no case, and I'd set out the inner select differently:

query = """
SELECT * FROM (
SELECT a.columna, a.columnb, a.iso
FROM all AS 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;
"""

Cheers,
John

 

[someone]

There's no tablet of stone, like PEP 8 It seems to be a matter of
individual taste; clarity and consistency and not overdoing
parentheses count for me.

IMO you have too much leading whitespace, you have "as" in upper,

thanks for reply.
So, no indentation? query is inside of function, of course.

 

[someone]

Agree, that looks nicer

And I'd not recomment SELECT * for anything beside test queries in an
interactive session or if you are going to copy tables...

yes, it I know.

Thank you for responce!

 

[Steve Holden]

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

 

[someone]

Also, for SQL, (A) why are you using nested joins?, and

inner select produce smaller set which is then joined with other
table, kind a optimization

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

Yes, my original question was about formatting. It's not original
query (only a part).
But thank you anyway!

 

[Marco Mariani]

inner select produce smaller set which is then joined with other
table, kind a optimization

Did you time it?
I've done some "kind of a optimization" that slowed queries by tenfold,
because postgres didn't need my advice, and knew better. RDBMS
performance is non-intuitive, really. If you're using mysql, YMMV,
because its optimizer is not as good.

Yes, my original question was about formatting. It's not original
query (only a part).

Try this:

http://www.dpriver.com/pp/sqlformat.htm


My 2c: I use textwrap.dedent to strip leading spaces from every line.

 

[someone]

Did you time it?
I've done some "kind of a optimization" that slowed queries by tenfold,
because postgres didn't need my advice, and knew better. RDBMS

Query itself is fast. I'll try to time it and will report my results
here

performance is non-intuitive, really. If you're using mysql, YMMV,
because its optimizer is not as good.

That's true, I don't trust optimizer

Try this:

http://www.dpriver.com/pp/sqlformat.htm

My 2c: I use textwrap.dedent to strip leading spaces from every line.

textwrap.dedent? What's that? I'm new in python. Will google it.
How do your query formatting looks like then?

 

[someon]

Did you time it?

I've did explain on both kinds of query (with nested select and
without) and query plan is identical to both. So, there is no
difference, well in PostGres