Strange output from list

[Steve Holden]

John Machin wrote:
> On Nov 11, 10:47 pm, Steve Holden <st... at holdenweb.com> wrote:
>> Chris Rebert wrote:
>>> On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault <nos... at nospam.com> wrote:
>>>> On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <alif... at gmail.com> wrote:
>>>>> sql = 'SELECT id FROM master'
>>>>> rows=list(cursor.execute(sql))
>>>>> for id in rows:
>>>>>       sql = 'SELECT COUNT(code) FROM companies WHERE code="%s"' % id[0]
>>>>>       result = list(cursor.execute(sql))
>>>>>       print "Code=%s, number=%s" % (id[0],result[0][0])
>>> Using liberal "term rewriting", consider the following rough
>>> equivalencies in the code:
>>> id[0] <==> rows[INDEX_HERE][0] <==> list(cursor.execute(sql))[INDEX_HERE][0]
>>> result[0][0] <==> list(cursor.execute(sql))[0][0]
>>> Note that in both cases, the list is sliced twice; the for-loop just
>>> conceals the `[INDEX_HERE]` implicit slicing that is caused by
>>> iterating over the list.
>> You might also want to consider saving some time by using a SQL solution
>> (assuming SQLite supports it, which it should) (untested):
>>
>> cursor.execute("""
>> SELECT master.id, count(companies.code)
>>    FROM master JOIN companies ON master.id = companies.code
>>    GROUP BY companies.code""")
> 
> Shouldn't it be GROUP BY master.id? I would have thought that SQL
> would be sad about a non-aggregate (master.id) that's in the SELECT
> list but not also in the GROUP BY list.
> 
Well, I did say "untested". But in SQL Server, for example, any field
argument to COUNT() must be an aggregated column. So it may depend on
the SQL implementation. I should really have said

GROUP BY master.id, companies.code

which is the kind of stupidity SQL's brainless implementations force one
to resort to.

>> for id, count in cursor.fetchall():
>>    print "Code=%s, number=%s" % (id, count)
>>
>> I'd like to think it makes the Python a bit more readable too ...
> 
> Agreed. result[0][0] is an abomination.
> 
Though one I am sure we have all used at times. The original code wasn't
too bad for a beginner.

regards
 Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/