Section 11.2. Straight SQL Queries

11.2. Straight SQL Queries

But even when you have objects that express the full range of possible relationships, sometimes you still don't quite have what you want. You need to be able to select objects based on more-specific criteria. In standard SQL, you could just write a select query and get what you want. And you've already seen how you can pass a strait SQL query string into a SQLObject's select() statement (see Chapters 6 and 10). Sometimes you need something with a bit more flexibility.

There are some SQLObject-specific ways to handle this, which we cover in the next section. But sometimes you just need to get down and dirty and play with the database directly, which is why the SQLObject connection object has a queryAll method. This method lets you run raw SQL queries against the DB.

The following code creates a model class called Digit and populates it with the digits from 0 to 9:

 names = ['Zero', 'One', 'Two', 'Three', 'Four',           'Five', 'Six', 'Seven', 'Eight', 'Nine'] class Digit(SQLObject):   name = EnumCol(enumValues=names)   value = IntCol() Digit.dropTable(ifExists=True) Digit.createTable() for i in range(0, len(names)):   Digit(name=names[i], value=i)

And here is the DB schema:

CREATE TABLE digit (     id INTEGER PRIMARY KEY,     name VARCHAR(5) CHECK (name in ('Zero', 'One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven', 'Eight', 'Nine')),     value INT );

Using the queryAll() method is different from using select() or selectBy(). You have to pass in an SQL query. The result is a list of tuples and not a SearchResults object. The database is accessed immediately and the entire result set is available immediately in memory. Another difference is that using queryAll, you can request only some of the columns of each row. Here's some code that extracts from the DB only the name and value (without the ID) of each Digit object whose value is greater than 5:

c = Digit._connection results = c.queryAll('SELECT name, value FROM digit WHERE value > 5') assert type(results) == list print results Output: [(u'Six', 6), (u'Seven', 7), (u'Eight', 8), (u'Nine', 9)]

As you can see, you have to address the DB table name digit and not the class name Digit, and you have to compose the SQL query string properly. The pros of raw SQL are that you get absolute control on your query, and it's generally going to be faster. But there are some cons, too. Using queryAll means you lose access to all the reasons you chose to use SQLObject in the first place: DB-agnostic SQL, Pythonic API, encapsulated best practices, no string concatenation, automatic character escaping (which helps you avoid SQL injection attacks) and all the other general SQL maladies you'd rather avoid.

Note

You can use queryAll to execute any SQL statement such as INSERT, DELETE, or even CREATE TABLE. Don't let the name queryAll() mislead you.