On Nov 11, 10:47 pm, Steve Holden <[EMAIL PROTECTED]> wrote: > Chris Rebert wrote: > > On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > >> On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <[EMAIL PROTECTED]> 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. > 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. -- http://mail.python.org/mailman/listinfo/python-list