By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,597 Members | 1,547 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,597 IT Pros & Developers. It's quick & easy.

[Newbie] Strange output from list

P: n/a
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.
Nov 11 '08 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Gilles Ganault <no****@nospam.comwrites:
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.

--
\ “What is it that makes a complete stranger dive into an icy |
`\ river to save a solid gold baby? Maybe we'll never know.” —Jack |
_o__) Handey |
Ben Finney
Nov 11 '08 #2

P: n/a
My apologies, my response was rather confused.

Ben Finney <bi****************@benfinney.id.auwrites:
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.
This remains true. No matter how many columns you specify in the
SELECT clause, each result row is a tuple.
SQLAlchemy represents this with a sequence of ResultProxy objects.
I mistakenly assumed you are using SQLAlchemy, which on re-reading
your post doesn't seem likely.

Instead, by the standard library ‘sqlite3’ module, you will receive
each result row as an ‘sqlite3.Row’ object:

A Row instance serves as a highly optimized row_factory for
Connection objects. It tries to mimic a tuple in most of its
features.

It supports mapping access by column name and index, iteration,
representation, equality testing and len().

<URL:http://docs.python.org/library/sqlite3.html#row-objects>

Since you only asked for the row to be printed, you therefore got a
string representation of the entire row (which mimics a Python tuple,
but is actually a different class with more functionality).

--
\ “Geeks like to think that they can ignore politics. You can |
`\ leave politics alone, but politics won't leave you alone.” |
_o__) —Richard Stallman, 2002-07-26 |
Ben Finney
Nov 11 '08 #3

P: n/a
Ben Finney wrote:
Gilles Ganault <no****@nospam.comwrites:

>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
--
Andrew

Nov 11 '08 #4

P: n/a
Andrew <al*****@gmail.comwrites:
(47,) is the python representation of a one item tuple
It's also the representation of a one-column result row, which is more
pertinent here.

Just because ‘str(foo) == str(bar)’, does *not* necessarily mean
‘type(foo) == type(bar)’, nor even ‘isinstance(foo, type(bar))’.

It's important to know that result rows are *not* tuples, and that
they have different (and more flexible) semantics.

--
\ “To succeed in the world it is not enough to be stupid, you |
`\ must also be well-mannered.” —Voltaire |
_o__) |
Ben Finney
Nov 11 '08 #5

P: n/a
On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <al*****@gmail.comwrote:
>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?
Nov 11 '08 #6

P: n/a
On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault <no****@nospam.comwrote:
On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <al*****@gmail.comwrote:
>>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?
--
http://mail.python.org/mailman/listinfo/python-list
Nov 11 '08 #7

P: n/a
Chris Rebert wrote:
On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault <no****@nospam.comwrote:
>On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <al*****@gmail.comwrote:
>>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
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/

Nov 11 '08 #8

P: n/a
On Nov 11, 10:47*pm, Steve Holden <st...@holdenweb.comwrote:
Chris Rebert wrote:
On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault <nos...@nospam.comwrote:
On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <alif...@gmail.comwrote:
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.

Nov 11 '08 #9

P: n/a
John Machin wrote:
On Nov 11, 10:47 pm, Steve Holden <st...@holdenweb.comwrote:
>Chris Rebert wrote:
>>On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault <nos...@nospam.comwrote:
On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <alif...@gmail.comwrote:
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/

Nov 11 '08 #10

P: n/a
John Machin wrote:
On Nov 11, 10:47 pm, Steve Holden <st...@holdenweb.comwrote:
>Chris Rebert wrote:
>>On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault <nos...@nospam.comwrote:
On Mon, 10 Nov 2008 20:02:39 -0600, Andrew <alif...@gmail.comwrote:
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/

Nov 11 '08 #11

P: n/a
Steve Holden wrote in news:mailman.3804.1226412496.3487.python-
li**@python.org in comp.lang.python:
>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
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.

Rob.
--
http://www.victim-prime.dsl.pipex.com/
Nov 11 '08 #12

P: n/a
Rob Williscroft wrote:
Steve Holden wrote in news:mailman.3804.1226412496.3487.python-
li**@python.org in comp.lang.python:
>>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

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
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/

Nov 12 '08 #13

This discussion thread is closed

Replies have been disabled for this discussion.