Retrieving blog entries

While creating, updating, and deleting are fairly straightforward operations, the real fun starts when we look at ways to retrieve our entries. We'll start with the basics, and then work our way up to more interesting queries.

We will use a special attribute on our model class to make queries: Entry.query. This attribute exposes a variety of APIs for working with the collection of entries in the database.

Let's simply retrieve a list of all the entries in the Entry table:

In []: entries = Entry.query.all()
In []: entries # What are our entries?
Out[]: [<Entry u'First entry'>, <Entry u'Second entry'>, <Entry u'Third entry'>, <Entry u'Fourth entry'>]

As you can see, in this example the query returns a list of Entry instances that we created. When no explicit ordering is specified, the entries are returned to us in an arbitrary order chosen by the database. Let's specify that we want the entries returned to us in an alphabetical order by title:

In []: Entry.query.order_by(Entry.title.asc()).all()
Out []:
[<Entry u'First entry'>,
 <Entry u'Fourth entry'>,
 <Entry u'Second entry'>,
 <Entry u'Third entry'>]

Shown next is how you would list your entries in reverse-chronological order, based on when they were last updated:

In []: oldest_to_newest = Entry.query.order_by(Entry.modified_timestamp.desc()).all()
Out []:
[<Entry: Fourth entry>,
 <Entry: Third entry>,
 <Entry: Second entry>,
 <Entry: First entry>]

Filtering the list of entries

It is very useful to be able to retrieve the entire collection of blog entries, but what if we want to filter the list? We could always retrieve the entire collection and then filter it in Python using a loop, but that would be very inefficient. Instead we will rely on the database to do the filtering for us, and simply specify the conditions for which entries should be returned. In the following example, we will specify that we want to filter by entries where the title equals 'First entry'.

In []: Entry.query.filter(Entry.title == 'First entry').all()
Out[]: [<Entry u'First entry'>]

If this seems somewhat magical to you, it's because it really is! SQLAlchemy uses operator overloading to convert expressions such as <Model>.<column> == <some value> into an abstracted object called BinaryExpression. When you are ready to execute your query, these data-structures are then translated into SQL.

Note

A BinaryExpression is simply an object that represents the logical comparison and is produced by over riding the standards methods that are typically called on an object when comparing values in Python.

In order to retrieve a single entry, you have two options: .first() and .one(). Their differences and similarities are summarized in the following table:

Let's try the same query as before but, instead of calling .all(), we will call .first() to retrieve a single Entry instance:

In []: Entry.query.filter(Entry.title == 'First entry').first()
Out[]: <Entry u'First entry'>

Notice how previously .all() returned a list containing the object, whereas .first() returned just the object itself.

Special lookups

In the previous example we tested for equality, but there are many other types of lookups possible. In the following table, we have listed some that you may find useful. A complete list can be found in the SQLAlchemy documentation.

Combining expressions

The expressions listed in the preceding table can be combined using bitwise operators to produce arbitrarily complex expressions. Let's say we want to retrieve all blog entries that have the word Python or Flask in the title. To accomplish this, we will create two contains expressions, then combine them using Python's bitwise OR operator, which is a pipe | character, unlike a lot of other languages that use a double pipe || character:

Entry.query.filter(Entry.title.contains('Python') | Entry.title.contains('Flask'))

Using bitwise operators, we can come up with some pretty complex expressions. Try to figure out what the following example is asking for:

Entry.query.filter(
    (Entry.title.contains('Python') | Entry.title.contains('Flask')) &
    (Entry.created_timestamp > (datetime.date.today() - datetime.timedelta(days=30)))
)

As you probably guessed, this query returns all entries where the title contains either Python or Flask, and that were created within the last 30 days. We are using Python's bitwise OR and AND operators to combine the sub-expressions. For any query you produce, you can view the generated SQL by printing the query as follows:

In []: query = Entry.query.filter(
 (Entry.title.contains('Python') | Entry.title.contains('Flask')) &
 (Entry.created_timestamp > (datetime.date.today() - datetime.timedelta(days=30)))
)
In []: print str(query)

SELECT entry.id AS entry_id, ...
FROM entry 
WHERE (
 (entry.title LIKE '%%' || :title_1 || '%%') OR (entry.title LIKE '%%' || :title_2 || '%%')
) AND entry.created_timestamp > :created_timestamp_1

Negation

There is one more piece to discuss, which is negation. If we wanted to get a list of all blog entries that did not contain Python or Flask in the title, how would we do that? SQLAlchemy provides two ways to create these types of expressions, using either Python's unary negation operator (~) or by calling db.not_(). This is how you would construct this query with SQLAlchemy:

Using unary negation:

In []: Entry.query.filter(~(Entry.title.contains('Python') | Entry.title.contains('Flask')))

Using db.not_():

In []: Entry.query.filter(db.not_(Entry.title.contains('Python') | Entry.title.contains('Flask')))

Operator precedence

Not all operations are considered equal to the Python interpreter. This is like in math class, where we learned that expressions such as 2 + 3 * 4 are equal to 14 and not 20, because the multiplication operation occurs first. In Python, bitwise operators all have a higher precedence than things such as equality tests, so this means that, when you are building your query expression, you have to pay attention to the parentheses. Let's look at some example Python expressions and see the corresponding query:

If you find yourself struggling with operator precedence, it's a safe bet to put parentheses around any comparison that uses ==, !=, <, <=, >, and >=.