Section 11.3. Smart Queries

11.3. Smart Queries

So, perhaps you want to have the benefits of SQLObject without losing the flexibility to create complex queries against your database. Fortunately, the makers of SQLObject thought this, too, so they provide a couple of mechanisms that make it possible to create complex queries without the need to drop down to queryAll.

You've already seen a glimpse of how easy it is to query the DB using SQLObject select() and selectBy() methods. In this chapter, you'll see how to get the maximum out of your DB while still reaping the benefits of SQLObject. As always, there is a subtle balancing act between ease of use, level of control, and performance. Some object relational mappers (ORM) trap you into specific uses and prevent you from doing optimizations; other data access schemes push you to optimize your code for performance prematurely. But SQLObject gives you multiple ways to access your data with different mixes of these properties. Novices can stick to the simple API, and experts can dig deeper and find the right solution. The important thing is that SQLObject doesn't force you to do anything in a specific way. You can always tell it to stay out of your way.

11.3.1. sqlbuilder-Based Queries

SQLObject lets you have your cake and eat it, too. You can combine raw SQL power and easy object-oriented access with SQLObject. The key is the SQLBuilder module. This module defines a number of namespaces and classes that allow you to generate SQL expressions from objects. Suppose, for example, that you want to construct the following SQL query:

SELECT digit.name, digit.value FROM digit WHERE ((digit.value > 5) AND (digit.value < 9)): from sqlobject.sqlbuilder import * where_clause = AND(table.digit.value > 5, table.digit.value < 9) columns = [Digit.q.name, Digit.q.value] selectStatement = Select(columns, where_clause) print selectStatement

To use the special sqlbuilder classes and operator, you must import names from sqlobject.sqlbuilder. They're not exported automatically from sqlobject. The where clause is an AND expression in this case. The columns to be selected are a list of objects accessible from the special Digit.q object. Each model class has a q member, which is a sqlbuilder.SQLObjectTable object. It is designed to allow sqlbuilder to work its magic on the columns and interact with the special SQLExpression objects. The Select instance accepts the column list and the where clause and generates the desired SQL query.

You can compose almost any SQL expression and use operators such as OR, NOT, IN, LIKE, EXISTS, ENDWITH and various joins.

If you want to select full rows from the DB, you can combine sqlbuilder-based where clauses with the select() method of model classes. Here is a complicated where clause that selects all the digits that have a name with more than four characters and a value greater than seven, or which have a name ending with ee (solution: three and eight):

whereClause = AND(func.length(Digit.q.name) > 4,                   OR(Digit.q.value > 7, Digit.q.name.endswith('ee'))) print list(Digit.select(whereClause)) Output: [<Digit 4 name=u'Three' value=3>, <Digit 9 name=u'Eight' value=8>]

The preceding code uses the func.length DB function to test the length of the name. The func namespace provides access to native DB functions. It also uses endswith() to test for names that end with ee. SQLObject provides startswith(), endswith(), and contains() as convenience methods that translate to STARTSWITH, ENDSWITH, and CONTAINS operators. Here is the generated SQL:

SELECT digit.id, digit.name, digit.value FROM digit WHERE ((length(digit.name) > 4) AND ((digit.value > 7) OR (digit.name LIKE '%ee')))

11.3.2. Selecting from Multiple Tables

SQLBuilder is cool and everything, but sometimes you still need more. You need to get a result set that contains columns from multiple tables. Of course, you could just use MultipleJoin and RelatedJoin to express these relationships and always use them to retrieve the related items of a single object. This is useful but potentially inefficient (talks a lot to the DB), and you don't always have control of the database to be able to add the relationships you want.

Once again, SQLObject has a solution. You can use SQLBuilder to create SQL join stantements for you. Let's take a look at a Person class and a Book class, where each book has an author who is a person:

class Person(SQLObject):     firstName = StringCol()     lastName = StringCol() class Book(SQLObject):     title = StringCol()     author_id = ForeignKey('Person')     genre = EnumCol('Fiction', 'Science', 'Science Fiction')

Let's add some authors and books:

for t in [Person, Book]:     t.dropTable(ifExists=True)     t.createTable() # Authors a1 = Person(firstName='Stephen', lastName='King') a2 = Person(firstName='Donald', lastName='Knuth') a3 = Person(firstName='Gigi', lastName='Sayfan') # bookless # Books by Stephen King Book(title='It', author_id=a1.id, genre='Fiction') Book(title='The Shining', author_id=a1.id, genre='Fiction') Book(title='Needful Things', author_id=a1.id, genre='Fiction') # Books by Donald Knuth Book(title='Concrete Mathmatics', author_id=a2.id, genre='Science') Book(title='Literate Programming', author_id=a2.id, genre='Science') Book(title='Surreal Numbers', author_id=a2.id, genre='Science')

Note that the preceding code does not define a MultipleJoin Books collection in the Person class, because not every person is an author, and this attribute doesn't make sense in a generic Person class. To list all the books and their authors, we can use the following code:

s = Select([Person.q.firstName, Person.q.lastName, Book.q.title], Person.q.id == Book.q.author_id) result = c.queryAll(str(s)) for item in result:     print item Output: ('Stephen', 'King', 'It') ('Stephen', 'King', 'The Shining') ('Stephen', 'King', 'Needful Things') ('Donald', 'Knuth', 'Concrete Mathematics') ('Donald', 'Knuth', 'Literate Programming') ('Donald', 'Knuth', 'Surreal Numbers')

This constructs the query using sqlbuilder's Select class and the q attribute of the Person and Book classes to specify the condition. Here is the generated SQL:

SELECT person.first_name, person.last_name, book.title FROM person, book WHERE (person.id = book.author_id)

A similar result using MultipleJoin will require one query to get all the authors, and then going over his books and printing the title will generate another another query to the DB for each book.

11.3.3. Joins

You can also use various forms of Join expression with the select() method of model classes. The following code gets the list of authors (people who have published a book):

authors = Person.select(join=INNERJOINOn(Person, Book, Person.q.id == Book. q.author_id), distinct=True) for a in authors:     print a Output: <Person 1 firstName='Stephen' lastName='King'> <Person 2 firstName='Donald' lastName='Knuth'>

Here is the generated SQL:

SELECT DISTINCT person.id, person.first_name, person.last_name FROM person INNER JOIN book ON (person.id = book.author_id) WHERE 1 = 1

Let's take it apart piece by piece. We pass two named arguments (join and distinct) to the Person.select() method. The join argument is an INNERJOINOn sqlbuilder expression. It expects the two tables to join and a condition. If the first table is None, it uses the primary table (Person in this case). The entire expression expands to this:

person INNER JOIN book ON (person.id = book.author_id)

The distinct=True argument is responsible for the DISTINCT keyword and to make sure we get every author just once and not once for every book. The other parts (SELECT keyword, column names, and the trivial WHERE clause) are generated by the select() method itself.

There is no book whose author_id points to Gigi Sayfan, so Gigi Sayfan is not listed. Because this is my first book, this is true at the time I'm writing this (now), but it will be false by the time you read this book (also now).

11.3.4. Nested Queries

Nested queries, or subselects, are often necessary to dissect complicated datasets or overcome some DB limitations. You can mix Select class instances in your sqlbuilder expressions to get nested queries. In general, any subselect can be written as a join, but not vice versa. Why should you even consider a subselect then? One reason is if you use the same subselect in multiple queries, you can use the results of the subquery without recomputing each time. Another reason is to break a complicated expression into multiple parts for clarity or debugging. The following code, using a subselect, will display the authors who published Fiction books:

subselect = Select(Book.q.author_id, where=AND(Outer(Person).q.id == Book.q.id, Book.q.genre == 'Fiction') authors = Person.select(IN(Person.q.id, subselect)))

What's going on? The Person.select() method selects only people who are in the results of the IN expression, which are all the IDs of people who have a book that is Fiction. The subselect is an instance of the Select class because I want to control the returned columns (just the ID).