Select, Insert, Delete and other Queries

Now that you have declared models it’s time to query the the database. We will be using the model definitions from the Quickstart chapter.

Inserting Records

Before we can query something we will have to insert some data. Inserting data into the database is a three step process:

  1. Create the Python object
  2. Add it to the session
  3. Commit the session

The session here is essentially a beefed up version of a database transaction. This is how it works:

>>> from yourapp import User
>>> me = User('admin', 'admin@example.com')
>>> db.add(me)
>>> db.commit()

Alright, that was not hard. What happens at what point? Before you add the object to the session, SQLAlchemy basically does not plan on adding it to the transaction. That is good because you can still discard the changes. For example think about creating the post at a page but you only want to pass the post to the template for preview rendering instead of storing it in the database.

The add() function call then adds the object. It will issue an INSERT statement for the database but because the transaction is still not committed you won’t get an ID back immediately. If you do the commit, your user will have an ID:

>>> me.id

Deleting Records

Deleting records is very similar, instead of add() use delete():

>>> db.delete(me)
>>> db.commit()

Querying Records

So how do we get data back out of our database? For this SQLA-Wrapper provides a query attribute. When you call it whith your Model class you will get back a new query object over all records. You can then use methods like filter() to filter the records before you fire the select with all() or first().

If you want to go by primary key you can also use get().

The following queries assume following entries in the database:

id username email
1 admin admin@example.com
2 peter peter@example.org
3 guest guest@example.com

Retrieve a user by username:

>>> peter = db.query(User).filter_by(username='peter').first()
>>> peter.id
>>> peter.email

Same as above but for a non existing username gives None:

>>> missing = db.query(User).filter_by(username='missing').first()
>>> missing is None

Selecting a bunch of users by a more complex expression:

>>> db.query(User).filter(User.email.endswith('@example.com')).all()
[<User u'admin'>, <User u'guest'>]

Ordering users by something:

>>> db.query(User).order_by(User.username)
[<User u'admin'>, <User u'guest'>, <User u'peter'>]

Limiting the number of users returned:

>>> db.query(User).limit(1).all()
[<User u'admin'>]

Getting user by primary key:

>>> db.query(User).get(1)
<User u'admin'>

Getting the bigger id:

>>> db.query(db.func.max(User.id)).scalar()

Queries in Views

If you write a view function it’s often very handy to return a 404 error (or some other error) for missing entries. Because this is a very common idiom, SQLA-Wrapper provides a helper for this exact purpose.

Instead of get() one can use get_or_error() and instead of first(), first_or_error(). This will raise the error you give to it instead of returning None:

from werkzeug.exceptions import NotFound

def show_user(username):
    user = db.query(User).filter_by(username=username).first_or_error(NotFound)
    return render_template('show_user.html', user=user)