Issue using cursor.execute and cursor.fetchall in python using sqlite

Question

I am having issues returning a list of all comments as tuples in my code. Here is what I have:

def list_comments(db, limit=None):
    """return a list of all comments as tuples

       - tuples are (id, useremail, page, comment)
       if limit is provided it should be an integer and only that
       many comments will be returned
    """

    cursor.execute("SELECT * FROM comments")
    manyresults = cursor.fetchall()

    for row in cursor:
        return row

So what i'm trying to do is select everything from my comments table and return it:

CREATE TABLE comments (
        id integer unique primary key autoincrement,
        useremail text,
        page text,
        comment text,
        FOREIGN KEY(useremail) REFERENCES users(email)
);"""

I am very new to this all so if I'm completely wrong please let me know where or what I'm doing wrong. Thanks!

EDIT: Here is a test I am running it against

 def test_list_comments(self):
    """The list_comments procedure should return a list of tuples
    one for each of the comment entries in the database, each tuple
    should contain """

    clist = interface.list_comments(self.db)

    # we should have the same number of comments as we created
    self.assertEquals(len(clist), len(self.comments), "Wrong number of comments returned from list_units, expected %d, got %d" % (len(self.comments), len(clist)))

    # comments should be in order so the largest id should be first
    self.assertEquals(clist[0][0], self.comments[-1][0], "Wrong comment id first in comment list, expected %d, got %d" % (clist[0][0], self.comments[-1][0]))

    # and the comment list should be ordered by id
    ids = [c[0] for c in clist]
    self.assertEqual(sorted(ids, reverse=True), ids, "List of comments returned is not in large-to-small order: %s" % (ids,))

    # try the limit argument
    clist = interface.list_comments(self.db, 3)
    self.assertEquals(len(clist), 3, "Wrong number of comments returned from list_comments with a limit argument, expected 3, got %d" % (len(clist),))

Nathan Villaescusa · Accepted Answer · 2013-04-17 03:43:31Z

2

You have a couple issues:

You are not doing anything with the result of cursor.fetchall()
You are only returning the first result, not all of them

I think what you need is:

cursor.execute("SELECT * FROM comments")
manyresults = cursor.fetchall()
return list(manyresults)

Though you can also do:

return list(cursor.execute("SELECT * FROM comments"))

answered Apr 17, 2013 at 3:43

Nathan Villaescusa

17.7k4 gold badges55 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2288946 Over a year ago

Oh that makes lots more sense I don't know why I didn't see that before. I do still somehow get an exception error using the tests I added to the original question description..

user2288946 Over a year ago

self.assertEquals(clist[0][0], self.comments[-1][0], "Wrong comment id first in comment list, expected %d, got %d" % (clist[0][0], self.comments[-1][0])) AssertionError: Wrong comment id first in comment list, expected 0, got 11

Nathan Villaescusa Over a year ago

You need to add 'ORDER BY id' to query otherwise the result will not be ordered by anything.

user2288946 Over a year ago

Still doesn't change that the test got 11 when 0 was expected. Thanks for the help so far I appreciate it!

Nathan Villaescusa Over a year ago

If you are doing an 'order by' the largest id will be last. You need 'order by id desc' to have the largest id first.

|

Collectives™ on Stack Overflow

Issue using cursor.execute and cursor.fetchall in python using sqlite

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related