How to use peewee limit()?

Question

With Peewee I'm trying to use limit as follows:

one_ticket = Ticket.select().limit(1)
print one_ticket.count()

This prints out 5 however. Does anybody know what's wrong here?

Ironmonger

This is a very old issue - this was fixed years ago. The count() method now takes into account any limit or ordering applied. – Ceram

Answer 1

Try running peewee in debug mode, which is actually just setting up the python logging module to handle logging.DEBUG level items:

import logging
logging.basicConfig(
    format='[%(asctime)-15s] [%(name)s] %(levelname)s]: %(message)s',
    level=logging.DEBUG
)

# From here, you can now perform your query, and you should see peewee's debug output using the logging module.
one_ticket = Ticket.select().limit(1)
print one_ticket.count()

Ideally, you should see the raw query.

Testicle

Thanks for the tip. The raw query is as follows: [2013-11-19 10:41:33,555] [peewee] DEBUG]: ('SELECT Count(t1."id") FROM "ticket" AS t1 LIMIT 1', []). This still leaves me clueless as to why the count is 5 though. Any ideas? – Ironmonger

You likely need to use one_ticket.wrapped_count() instead. The more direct one_ticket.count() will default to counting the number of items from primary key, as seen in the source: github.com/coleifer/peewee/blob/master/peewee.py#L1653 – Testicle

Unfortunately, wrapped_count() also results in 5. Also, it would be quite weird if the count-method on the result of a query doesn't count the results, but does a new query. (I really appreciate you trying to help me out though! :) ) Would you have any other ideas? – Ironmonger

Unfortunately, i'm spent. Last idea, update peewee to the latest version from github and try again. Perhaps it's a transient error? – Testicle

@Ceram - I was just rereading over this Q&A, and hit upon your comment. May I ask; why does peewee clear limits when doing a count? That seems totally unintuitive behaviour to me.. – Ironmonger

I'm way late to this discussion, but maybe this will help a new learner. The reason the SQL query returns 5 is that there are five items in the "ticket" table and that count of 5 can be returned as a single row in the SQL response. The LIMIT is just for the number of rows returned. This is how SQL works and has nothing to do with Peewee. – Embay

Answer 2

This is a very old issue - this was fixed years ago. The count() method now takes into account any limit or ordering applied.

In [1]: from peewee import *

In [2]: db = SqliteDatabase(':memory:')

In [3]: class Ticket(db.Model):
   ...:     pass
   ...: 

In [4]: Ticket.create_table()

In [5]: for i in range(10):
   ...:     Ticket.create()
   ...: 

In [6]: Ticket.select().limit(1).count()
Out[6]: 1

In [7]: Ticket.select().count()
Out[7]: 10

Ceram

Answer 3

This prints out 5 however. Does anybody know what's wrong here?

To get the number of results of a LIMIT query you can pass the iterator over len(), like len(one_ticket).

What is wrong is that .limit(1) and .count() issue two different SQL queries, and COUNT usually costs a lot more than a query with LIMIT 1 over the database.

For example, this is useful if you want to know if there exists some or more than one result in a query. Django's ORM QuerySet method .exists() queries for a full row with LIMIT 2 instead of a COUNT because COUNT hits the DB harder than LIMIT 2

Hispanic

Dear downvoter. Would you please share why this is a bad answer? Thanks. – Hispanic

It is not to the original questioners point - the question was why it returns 5, and the answer is because there was a bug. It should return 1. Everything else is distracting or irrelevant information. – Ceram