Selecting multiple records

We can use Model.select() to retrieve rows from the table. When youconstruct a SELECT query, the database will return any rows that correspondto your query. Peewee allows you to iterate over these rows, as well as useindexing and slicing operations:

  1. >>> query = User.select()
  2. >>> [user.username for user in query]
  3. ['Charlie', 'Huey', 'Peewee']
  4.  
  5. >>> query[1]
  6. <__main__.User at 0x7f83e80f5550>
  7.  
  8. >>> query[1].username
  9. 'Huey'
  10.  
  11. >>> query[:2]
  12. [<__main__.User at 0x7f83e80f53a8>, <__main__.User at 0x7f83e80f5550>]

Select queries are smart, in that you can iterate, index and slicethe query multiple times but the query is only executed once.

In the following example, we will simply call select() anditerate over the return value, which is an instance of Select.This will return all the rows in the User table:

  1. >>> for user in User.select():
  2. ...     print user.username
  3. ...
  4. Charlie
  5. Huey
  6. Peewee

Note

Subsequent iterations of the same query will not hit the database as theresults are cached. To disable this behavior (to reduce memory usage), callSelect.iterator() when iterating.

When iterating over a model that contains a foreign key, be careful with theway you access values on related models. Accidentally resolving a foreign keyor iterating over a back-reference can cause N+1 query behavior.

When you create a foreign key, such as Tweet.user, you can use thebackref to create a back-reference (User.tweets). Back-referencesare exposed as Select instances:

  1. >>> tweet = Tweet.get()
  2. >>> tweet.user  # Accessing a foreign key returns the related model.
  3. <tw.User at 0x7f3ceb017f50>
  4.  
  5. >>> user = User.get()
  6. >>> user.tweets  # Accessing a back-reference returns a query.
  7. <peewee.ModelSelect at 0x7f73db3bafd0>

You can iterate over the user.tweets back-reference just like any otherSelect:

  1. >>> for tweet in user.tweets:
  2. ...     print(tweet.message)
  3. ...
  4. hello world
  5. this is fun
  6. look at this picture of my food

In addition to returning model instances, Select queries can returndictionaries, tuples and namedtuples. Depending on your use-case, you may findit easier to work with rows as dictionaries, for example:

  1. >>> query = User.select().dicts()
  2. >>> for row in query:
  3. ...     print(row)
  4.  
  5. {'id': 1, 'username': 'Charlie'}
  6. {'id': 2, 'username': 'Huey'}
  7. {'id': 3, 'username': 'Peewee'}

See namedtuples(), tuples(),dicts() for more information.

Iterating over large result-sets

By default peewee will cache the rows returned when iterating over aSelect query. This is an optimization to allow multiple iterationsas well as indexing and slicing without causing additional queries. Thiscaching can be problematic, however, when you plan to iterate over a largenumber of rows.

To reduce the amount of memory used by peewee when iterating over a query, usethe iterator() method. This method allows you to iteratewithout caching each model returned, using much less memory when iterating overlarge result sets.

  1. # Let's assume we've got 10 million stat objects to dump to a csv file.
  2. stats = Stat.select()
  3.  
  4. # Our imaginary serializer class
  5. serializer = CSVSerializer()
  6.  
  7. # Loop over all the stats and serialize.
  8. for stat in stats.iterator():
  9.     serializer.serialize_object(stat)

For simple queries you can see further speed improvements by returning rows asdictionaries, namedtuples or tuples. The following methods can be used on anySelect query to change the result row type:

Don’t forget to append the iterator() method call to alsoreduce memory consumption. For example, the above code might look like:

  1. # Let's assume we've got 10 million stat objects to dump to a csv file.
  2. stats = Stat.select()
  3.  
  4. # Our imaginary serializer class
  5. serializer = CSVSerializer()
  6.  
  7. # Loop over all the stats (rendered as tuples, without caching) and serialize.
  8. for stat_tuple in stats.tuples().iterator():
  9.     serializer.serialize_tuple(stat_tuple)

When iterating over a large number of rows that contain columns from multipletables, peewee will reconstruct the model graph for each row returned. Thisoperation can be slow for complex graphs. For example, if we were selecting alist of tweets along with the username and avatar of the tweet’s author, Peeweewould have to create two objects for each row (a tweet and a user). In additionto the above row-types, there is a fourth method objects()which will return the rows as model instances, but will not attempt to resolvethe model graph.

For example:

  1. query = (Tweet
  2.          .select(Tweet, User)  # Select tweet and user data.
  3.          .join(User))
  4.  
  5. # Note that the user columns are stored in a separate User instance
  6. # accessible at tweet.user:
  7. for tweet in query:
  8.     print(tweet.user.username, tweet.content)
  9.  
  10. # Using ".objects()" will not create the tweet.user object and assigns all
  11. # user attributes to the tweet instance:
  12. for tweet in query.objects():
  13.     print(tweet.username, tweet.content)

For maximum performance, you can execute queries and then iterate over theresults using the underlying database cursor. Database.execute()accepts a query object, executes the query, and returns a DB-API 2.0 Cursorobject. The cursor will return the raw row-tuples:

  1. query = Tweet.select(Tweet.content, User.username).join(User)
  2. cursor = database.execute(query)
  3. for (content, username) in cursor:
  4.     print(username, '->', content)