FAQ
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])
=====Code11Z, number=(47,)
=====
I expected to see "numberG". Why does Python return "(47,)"?

Thank you.

Search Discussions

  • Ben Finney at Nov 11, 2008 at 1:41 am

    Gilles Ganault <nospam at nospam.com> writes:

    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])
    =====> Code11Z, number=(47,)
    =====>
    I expected to see "numberG". 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
  • Ben Finney at Nov 11, 2008 at 1:52 am
    My apologies, my response was rather confused.

    Ben Finney <bignose+hates-spam at benfinney.id.au> writes:
    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
  • Andrew at Nov 11, 2008 at 2:02 am

    Ben Finney wrote:
    Gilles Ganault <nospam at nospam.com> writes:

    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])
    =====>> Code11Z, number=(47,)
    =====>>
    I expected to see "numberG". 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:
    Code11Z, numberG

    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
  • Ben Finney at Nov 11, 2008 at 3:39 am

    Andrew <alif016 at gmail.com> writes:

    (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
  • Gilles Ganault at Nov 11, 2008 at 8:56 am

    On Mon, 10 Nov 2008 20:02:39 -0600, Andrew 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])
    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 at Nov 11, 2008 at 9:20 am

    On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault wrote:
    On Mon, 10 Nov 2008 20:02:39 -0600, Andrew 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.

    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
  • Steve Holden at Nov 11, 2008 at 11:47 am

    Chris Rebert wrote:
    On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault wrote:
    On Mon, 10 Nov 2008 20:02:39 -0600, Andrew 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""")
    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/
  • John Machin at Nov 11, 2008 at 12:33 pm

    On Nov 11, 10:47?pm, Steve Holden wrote:
    Chris Rebert wrote:
    On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault wrote:
    On Mon, 10 Nov 2008 20:02:39 -0600, Andrew 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.
  • Steve Holden at Nov 11, 2008 at 2:08 pm

    John Machin wrote:
    On Nov 11, 10:47 pm, Steve Holden wrote:
    Chris Rebert wrote:
    On Tue, Nov 11, 2008 at 12:56 AM, Gilles Ganault wrote:
    On Mon, 10 Nov 2008 20:02:39 -0600, Andrew 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/
  • Rob Williscroft at Nov 11, 2008 at 9:52 pm
    Steve Holden wrote in news:mailman.3804.1226412496.3487.python-
    list at 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.
  • Steve Holden at Nov 12, 2008 at 2:25 am

    Rob Williscroft wrote:
    Steve Holden wrote in news:mailman.3804.1226412496.3487.python-
    list at 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/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedNov 11, '08 at 1:24a
activeNov 12, '08 at 2:25a
posts12
users7
websitepython.org

People

Translate

site design / logo © 2022 Grokbase