Issue: Get all results from a Select Query without specifying columns
Author: ketozhang
State: closed
Created: 2018-03-03 01:03:25

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]](http://docs.peewee-orm.com/en/latest/peewee/querying.html
)[[2]](http://docs.peewee-orm.com/en/latest/peewee/query_examples.html#basic-exercises). 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** 
```python
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**
```python
query = User.select()
cursor = db.execute_sql()(*query) 
print(cursor.fetchone()) # prints a row as a tuple!!
```

**Bad practice, but works perfectly 👍**
```python
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](http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#returning-lists-and-scalars):

..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 

```python
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?
](https://stackoverflow.com/questions/43755511/is-there-a-way-to-query-all-in-peewee)
* [Get full result back from PeeWee query (for conversion to JSON)](https://stackoverflow.com/questions/35225682/get-full-result-back-from-peewee-query-for-conversion-to-json?rq=1)

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:

```python
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:

```python
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.

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.

```python
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'}]
```

@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](http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#lists-of-records), majority of the documentation extract results by knowing which columns you specifically want. There is rarely any documentation on all columns for instance:

```python
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](http://docs.peewee-orm.com/en/latest/search.html?q=dict&check_keywords=yes&area=default)

>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)
```python
query = User.select()
row1 = query.execute().cursor.fetchone()
```

Thank you very much, this does solve my problem!


> 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.

```python
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).

`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.

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.