Section 10.3. Simple Database Queries

10.3. Simple Database Queries

SQLObject provides you many ways to access your data. You can get a single row by its ID, you can use sqlbuilder to construct SQL queries in a structured way, you can select a set of objects with some values using selectBy(), and you can just use plain SQL. In addition, SQLObject allows you to define one-to-many and many-to-many relationships between tables and access them as plain attributes.

SQLObject gives you three different ways to set up queries. The three query flavors are simple, structured, and raw. You've already seen raw queries in WhatWhat Status, and we cover how to use the query builder syntax to create complex, structured queries in Chapter 11, "Mastering SQLObject." For now, let's take a look at simple queries, which might just satisfy all your query needs. This is especially true if you are free to construct your DB schema in a way that fits your data access patterns. SQLObject tries hard (and succeeds) to make simple things simple and complex things possible.

10.3.1. Getting a Single Object by ID

It is useful sometimes to just get a single row by ID. I do it constantly during interactive development, and you might also find it useful in your program. The ID will usually be stored as a foreign key or as a result of a query. SQLObject makes it as easy as possible. Just call the get() method of your model class with the ID:

import os, sys from sqlobject import * db_filename = os.path.abspath('test.db').replace(':\\', '|\\') sqlhub.processConnection = connectionForURI('sqlite:/' + db_filename) class Highlander(SQLObject):   name = StringCol()   motto = StringCol() Highlander.dropTable(ifExists=True) Highlander.createTable() Highlander(name='Connor Macleod', motto='There can be only one!') o = Highlander.get(1) print o.name, 'says:', o.motto

10.3.2. Getting an Entire Table

Getting all the objects in a table is common. Often, you need to display an entire table or export it to some other medium. SQLObject gives you the select() method. Just call it, and the contents of the entire table are at your fingertips:

class Something(SQLObject):   name = StringCol() Something.dropTable(ifExists=True) Something.createTable() for i in range(1,11):   Something(name='Something #%d' % i) sqlhub.processConnection.debug = 1 sqlhub.processConnection.debugOutput = 1 result = Something.select()

select() doesn't return a list of SQLObject instances that correspond to rows in your table as you might expect. Instead, it returns an instance of a special type called SelectResults. The idea is to allow you to work with large tables comfortably without hogging your memory. When select() returns, the DB has not been accessed yet. The DB will be accessed only when you try to retrieve something from the SelectResults object, and in the most efficient possible way. Here are a few access patterns and the corresponding SQL generated:

>>> result[4]  1/QueryR  :  SELECT something.id, something.name FROM something WHERE 1 = 1 LIMIT 1 OFFSET 4 <Something 1 name='Something #1'> >>> >>> result.count()  1/QueryR  :  SELECT COUNT(*) FROM something WHERE 1 = 1 10 >>> >>> slice = result[3:5] >>> slice <SelectResults at ec15b0> >>> list(slice)  1/QueryR  :  SELECT something.id, something.name FROM something WHERE 1 = 1 LIMIT 2 OFFSET 3 [<Something 4 name='Something #4'>, <Something 5 name='Something #5'>] >>>

SearchResults will query the DB for a single object when you access it by index, it will execute a COUNT(*) query if you call the count() method, and it will return a new SearchResults object when you slice an existing SelectResults object. Note the slice [3:5] was actually extracted from the DB only when I called the list() factory function that actually creates a list of SQLObject instances. So, if you want to actually have an entire table for processing (for instance, for releasing the DB connection while you work on it), convert the SelectResults to a list. SelectResults supports the for iteration syntax, too:

>>> for o in result[2:6]: print o ...  1/QueryR  :  SELECT something.id, something.name FROM something WHERE 1 = 1 LIMIT 4 OFFSET 2 <Something 3 name='Something #3'> <Something 4 name='Something #4'> <Something 5 name='Something #5'> <Something 6 name='Something #6'> >>>

SelectResults emulates a sequence by implementing the special _getitem_ method. Note that it doesn't implement the _len_ special method that is called by Python when the len() function is invoked on an object. You must call count(). This is a little non-Pythonic because it breaks the illusion of SelectResults being a regular Python sequence.

10.3.3. Selecting Rows by Column Values

Another common access pattern is to select rows from a table based on a specific column value or a combination of column values. SQLObject gives you the selectBy() method. Suppose you have a table of web frameworks in various programming languages and you want to select only the Python-based ones:

class WebFramework(SQLObject):   name = StringCol()   language = StringCol() WebFramework.dropTable(ifExists=True) WebFramework.createTable() [WebFramework(name=f, language='Python') for f in ['TurboGears', 'DJango', 'Pylons']] [WebFramework(name=f, language='Java') for f in ['Struts', 'Shale', 'WebWork', 'Spring MVC']] [WebFramework(name=f, language='Ruby') for f in ['Ruby on Rails', 'ruby-waf']] sqlhub.processConnection.debug = 1 result = WebFramework.selectBy(language='Python') >>> for f in result: ...   print f.name ...  1/QueryR  :  SELECT web_framework.id, web_framework.name, web_framework.language FROM web_framework WHERE language = 'Python' TurboGears DJango Pylons

You can use selectBy() with multiple values, too:

>> result = WebFramework.selectBy(name='Shale', language='Java') >>> result[0]  1/QueryR  :  SELECT web_framework.id, web_framework.name, web_framework.language FROM web_framework WHERE name = 'Shale' AND language = 'Java' LIMIT 1 <WebFramework 5 name='Shale' language='Java'>

SQLObject will generate a WHERE clause with AND between all the column values you provide. selectBy() works best (fastest) if the relevant columns are indexed.

As you can see, SQLObject does a good job of providing a simple, yet efficient SQL-less query facility that may satisfy all your data access needs. If you need more, SQLObject is up to the challenge, as you will see in Chapters 11 and 12. Many of these features are explored in more depth, and you learn how to use SQLObject's query builder syntaxand much, much more.