Peewee: Get all results from a Select Query without specifying columns

[ketozhang]

Hi, so far I've read majority of the documentation on peewee. While there are plenty of instructions on how to create a SelectQuery [1][2]. There is no example on extracting everything about the results (list of rows). One would expect a query statement from SQL return a table-like. I've come from using SQLalchemy expression language to peewee ORM. I have no background on Flask

Throughout reading the whole documentation and a whole page of google results I've never found the proper use of SelectQuery.

Goal: Extract a range of entries from SQL

Not very useful as it only returns PK

db = SqliteDatabase(...)

query = User.select().where(User.id.between(0,100))
cursor = db.execute(query) 
print(cursor.fetchone()) # prints primary key

If the previous one only return PK why does this return everything

query = User.select()
cursor = db.execute_sql()(*query) 
print(cursor.fetchone()) # prints a row as a tuple!!

Bad practice, but works perfectly 👍

query = User.select().where(User.id.between(0,100))
rows = [row._data for row in query]

Now I'm just being hopeful

import pandas as pd
query = User.select().where(User.id.between(0,100))
pd.read_sql(query.sql()) # this is obviously incorrect

Either I am very ignorant about how ORM work... actually SQLAchemy supports this:

..or there is some missing about peewee that the developers decided it is not within the scope of peewee to take care of. So is there a method from query that is on the lines of

query = User.select().where(User.id.between(0,100))
rows = query.all() # a list of results or rows, no need to be dictionary but preferable

Similar concerns:

• #134
• Is there a way to query.all() in peewee?
  
• Get full result back from PeeWee query (for conversion to JSON)

[coleifer]

I'm not sure about your definition for the User model you've used in your examples, but presumably it has fields on it besides the ID?

Let's look at some examples:

class User(Model):
    username = TextField()

The user model in these examples has a username field, as well as an implicit "id" field. As you will see, User.select() will select all the columns in the table by default. The query is iterable and the results it yields are, by default, instances of the model class. You can, if you like, get different objects from the cursor as the examples below show:

In [7]: query = User.select().where(User.id.between(0, 3))

In [8]: for user in query:
   ...:     print(user.id, user.username)
   ...:     
1 huey
2 charlie
3 mickey

In [9]: for user_dict in query.dicts():
   ...:     print(user_dict)
   ...:     
{'id': 1, 'username': 'huey'}
{'id': 2, 'username': 'charlie'}
{'id': 3, 'username': 'mickey'}

In [10]: for user_row in query.namedtuples():
    ...:     print(user_row)
    ...:     
Row(id=1, username='huey')
Row(id=2, username='charlie')
Row(id=3, username='mickey')

There's no magic ".all()" you need... simply iterate over the Select query itself. If you explicitly want a cursor-wrapper (though there's no need, as the query itself is fine to use), you can use the return value of Select.execute().

Does this answer your question?

PS - I'm finding it hard to believe you've read the docs if you are confused on this... it's covered by the quick-start if you'd like a refresher.

[coleifer]

If you want to work with python lists of objects, just wrap the query in list(...), although there's no need to as the query itself is iterable.

In [11]: query = User.select().where(User.id < 4)

In [12]: list(query)
Out[12]: 
[<__main__.User at 0x7f983115b240>,
 <__main__.User at 0x7f983115b828>,
 <__main__.User at 0x7f9831157ac8>]

In [13]: list(query.dicts())
Out[13]: 
[{'id': 1, 'username': 'huey'},
 {'id': 2, 'username': 'charlie'},
 {'id': 3, 'username': 'mickey'}]

In [14]: [x for x in query]
Out[14]: 
[<__main__.User at 0x7f983115b240>,
 <__main__.User at 0x7f983115b828>,
 <__main__.User at 0x7f9831157ac8>]

In [15]: [x for x in query.dicts()]
Out[15]: 
[{'id': 1, 'username': 'huey'},
 {'id': 2, 'username': 'charlie'},
 {'id': 3, 'username': 'mickey'}]

[ketozhang]

@coleifer Thank you

I've now found the documentation hidden away in the near last row of querying
http://docs.peewee-orm.com/en/latest/peewee/querying.html?highlight=dicts()#retrieving-row-tuples-dictionaries-namedtuples

PS - I'm finding it hard to believe you've read the docs if you are confused on this... it's covered by the quick-start if you'd like a refresher.

No, it is not in quickstart, majority of the documentation extract results by knowing which columns you specifically want. There is rarely any documentation on all columns for instance:

query = (Pet
         .select(Pet, Person)
         .join(Person)
         .where(Pet.animal_type == 'cat'))

for pet in query:
    print(pet.name, pet.owner.name) #this is great if I only want name and owner's name

My suggestion is to highlight the query.dicts() method. I simply could've just searched for 'dicts' however majority of your documentation that uses dictionary are for query building see here

There's no magic ".all()" you need... simply iterate over the Select query itself. If you explicitly want a cursor-wrapper (though there's no need, as the query itself is fine to use), you can use the return value of Select.execute().

I believe I've used this before but the query returned nothing (while it shouldn't)

query = User.select()
row1 = query.execute().cursor.fetchone()

Thank you very much, this does solve my problem!

[coleifer]

I believe I've used this before but the query returned nothing (while it shouldn't)

You're using private APIs... really you shouldn't need to access the DB-API 2.0 cursor at all since Peewee provides cursor-wrappers that are efficient.

query = User.select()
a_row = query.get()

# Or,
a_row = query[0]

# Or,
a_row = query.first()

Remember that with select queries there's no need to explicitly call execute() -- they will execute implicitly when iterated (or if you call .get() or .first() on them).

[ketozhang]

row1 = query.execute().cursor.fetchone() was constructed from your following statement:

If you explicitly want a cursor-wrapper (though there's no need, as the query itself is fine to use), you can use the return value of Select.execute().

I must have interpreted it wrong.

[coleifer]

Calling execute() on a select query returns a cursor-like object but it is not a db-api cursor. Again, not necessary as the query itself should be treated like a cursor.