Select instance

The select instance represents a query. Every instance is created as a table of the database or as a combination of the tables. Every instance method of the select instance begins with an uppercase letter (Update, Delete, FetchAll). Every attribute that begins with lowercase letter is treated as a column name. Every instance method modifies directly the select on which it is called (this has further implications for joins) and always returns self. This means, that simple queries can fit on one line of code - ideal for web template languages.

Complex select instance initialization:

foo = db.foo('nick','name',age=23)

The unnamed parameters are treated as column names. If no columns were selected for the query, the '*' (all columns) is assumed. Named parameters will be appended to query as conditions. All conditions must be satisfied for every returned row.

For debugging purposes the select instance can be easily printed. The actual quoting of the values will be done by the DB-API module, especially when using unicode strings, the repr() value may not be entirely correctly quoted, but the DB-API module will take care of this.

>>> foo = db.foo('nick','name',age=23,hair='blue')
>>> print repr(foo)
< SQL: SELECT foo.nick,foo.name FROM foo  WHERE ((foo.hair = 'blue') AND (foo.age = 23))   >

Order clause

SQL language provides variety of things that can be expressed. For example the queries can be ordered descending by age and ascending by surname and then name.

foo = db.foo(city='Alabama')
foo.DescOrder('age').Order('surname','name')
for row in foo:
    print .....

The columns can be alternatively expressed as attributes of the origianl select instance. If the select instance comprises only of 1 table (no joins were performed yet), you can access the columns of the table as attributes of the select instance.

foo = db.foo(city='Alabama')
foo.DescOrder(foo.age).Order(foo.surname,foo.name)

Limit/offset clause

LIMIT/OFFSET can be represented simply by slices.

query = db.test('name',queue='BigQueue').Order('position')[10:20]

This query provides a list of people queued in BigQueue on positions 10 to 20.

Delete, Update - DML

Once you can select rows in a table, you will want to change them or delete them. Select instance provides a handy method Delete, that just deletes everything that the select instance selects. The following code deletes all rows from the table foo.

db.foo().Delete()                 ## Not a good idea..., deletes everything from table foo
db.foo(userid=andrewid).Delete()  ## Deletes only line with userid=andrewid

Updating can be done either using a syntax similar to the insert method, or you can directly assign to the attribute. Every assignment generates an UPDATE query to the database, if you are updating more then one column, using Update method is pereferred.

foo = db.foo(userid=andrew)
foo.Update(name='YourName', address='WhereDoYouLive')
# Alternativly, but it will generate 2 UPDATE queries
foo.name = 'YourName'
foo.address = 'WhereDoYouLive'

Other special methods

The select instance provides other special methods:

• FetchAll() - returns list of all selected rows
• Count(),Max(),Min(),Sum() - if it contains 1 table, you may specify argument directly as a string
• AddCol(column) - add a column to query. This is mainly useful for columns representing aggregate functions or case statements etc.
• Distinct() - Sets attribute on select instance, so that it will not return duplicate row on execution. (the 'DISTINCT' SQL keyword)

number_of_19_yer_old = db.foo(age=19).Count()
richness = db.foo().Sum('money')

# Alternatively by specifying column as select instance attribute
foo = db.foo()
cars = foo.Count(foo.cars)

---