Querying The Models

If you remember the post that we created the models in, you will also remember that TurboGears uses SQLAlchemy for the model part of MVC.

Now, we are going to do some practice on querying the models. In the meanwhile, you will discover the TurboGears console. Let’s get into our project’s home dir and start the console.

cd ~/projects/tg2env/ideas
source ../bin/activate
paster shell development.ini

Let’s import our models and the DBSession object from ideas/model. At first, we are going to create some projects and tasks. When using queries, you will see which queries are running.

from ideas.model import DBSession, Project, Task
p1 = Project(project_name="TurboGears Shell Project")
p2 = Project(project_name="TurboGears Blog Project")
DBSession.add(p1)
DBSession.add(p2)
DBSession.flush()
t1 = Task(project_id=p1.id, task_name="Extend the shell", status="open")
t2 = Task(project_id=p2.id, task_name="Create the models", status="closed")
t3 = Task(project_id=p2.id, task_name="Use Twitter Bootstrap in the templates", status="open")
DBSession.add(t1)
DBSession.add(t2)
DBSession.add(t3)
DBSession.flush()

We have created two projects, the first project has 1 open task, second project has 2 tasks; 1 open and 1 closed. We are going to start with the basics.

As the first step, let’s query all projects.

DBSession.query(Project).all()
# output:
00:30:09,385 INFO  [sqlalchemy.engine.base.Engine] SELECT projects.id AS projects_id, projects.project_name AS projects_project_name, projects.project_description AS projects_project_description 
FROM projects
00:30:09,385 INFO  [sqlalchemy.engine.base.Engine] ()
[<ideas.model.project.Project object at 0x4161bd0>, <ideas.model.project.Project object at 0x40982d0>, <ideas.model.project.Project object at 0x40a42d0>]

I had 3 projects in my “ideas” app so it returned all 3.

Let’s order the projects by their name in the ascending order.

projects = DBSession.query(Project).order_by(Project.project_name.asc()).all()

>>> for p in projects: print p.project_name
... 
TurboGears Blog Project
TurboGears Project Management
TurboGears Shell Project

Now we are going to list all open tasks of “TurboGears Blog Project”.

DBSession.query(Task).filter(Task.project_id == p2.id).filter(Task.status == "open").all()
>>> for task in tasks: print task.task_name
... 
Use Twitter Bootstrap in the template

SQLAlchemy has an expression called “and_” so we can write the above query like this:

>>> from sqlalchemy.sql.expression import and_
>>> DBSession.query(Task).filter(and_(Task.project_id == p2.id, Task.status == "open")).all()
>>> for task in tasks: print task.task_name
... 
Use Twitter Bootstrap in the templates

And we are going to update the “Use Twitter Bootstrap in the template” task and set its status to “closed”.

DBSession.query(Task).filter(Task.id == t3.id).update({"status": "closed"})
00:45:53,126 INFO  [sqlalchemy.engine.base.Engine] UPDATE tasks SET status=? WHERE tasks.id = ?
00:45:53,126 INFO  [sqlalchemy.engine.base.Engine] ('closed', 4)
>>> t3.status
'closed'

And let's delete all the tasks of "TurboGears Shell Project".

DBSession.query(Task).filter_by(project_id=t1.id).delete()
00:59:01,585 INFO  [sqlalchemy.engine.base.Engine] DELETE FROM tasks WHERE tasks.project_id = ?
00:59:01,585 INFO  [sqlalchemy.engine.base.Engine] (2,)

For more on SQLAlchemy, please go read on http://docs.sqlalchemy.org/en/latest/orm/query.html and you can do the Object Relation Tutorial.

About these ads