[Newbie] Strange output from list

[Gilles Ganault]

Hello

I'm getting some unwanted result when SELECTing data from an SQLite
database:

======
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])
======
Code=0111Z, number=(47,)
======

I expected to see "number=47". Why does Python return "(47,)"?

Thank you.

 

[Andrew]

Hello

I'm getting some unwanted result when SELECTing data from an SQLite
database:

======
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])
======
Code=0111Z, number=(47,)
======

I expected to see "number=47". Why does Python return "(47,)"?

The result of an SQL SELECT is a sequence of tuples, where each item
in the tuple is a value for a column as specified in the SELECT
clause.

SQLAlchemy represents this with a sequence of ResultProxy objects.
When you convert a ResultProxy object to a string, it displays like a
tuple. See the documentation for other ways of accessing various
attributes of a ResultProxy object.

(47,) is the python representation of a one item tuple
If you want:
Code=0111Z, number=47

Just change your code to:
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])
Notice the extra [0] index on the "result"

In English:
Item zero of the tuple that is item zero of result

E.g.

result = [(47,)]
result = result[0]
result (47,)
result[0]

47

 

[Gilles Ganault]

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])
Notice the extra [0] index on the "result"

In English:
Item zero of the tuple that is item zero of result

Thanks, it worked. But why does "id[0]" return the value of the first
(and only) column as I expected it, while I need to use "result[0]
[0]" to access the first column?

 

[Chris Rebert]

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.

Cheers,
Chris
--
Follow the path of the Iguana...
http://rebertia.com

Notice the extra [0] index on the "result"

In English:
Item zero of the tuple that is item zero of result

Thanks, it worked. But why does "id[0]" return the value of the first
(and only) column as I expected it, while I need to use "result[0]
[0]" to access the first column?

 

[Steve Holden]

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""")
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 ...

regards
Steve

 

[John Machin]

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.

 

[Steve Holden]

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]

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]

Steve Holden wrote in (e-mail address removed) in comp.lang.python:


You must mean an "SQL Server" other than the Microsofts one, as:

select count( aid ) as "count"
from table_1
group by aid

count
-----------
8
8
8
8
8
8
8
8

(8 row(s) affected)

and:

select count( aid ) as "count"
from table_1

count
-----------
64

(1 row(s) affected)

Like it should.

Hmm, strange. I must be thinking of some other SQL Server then. Or, more
likely, some other error situation.

regards
Steve