Conditions

Python provides interface for very complex operator overloading. The boolean operators cannot be overriden, that's why the sqlabstr interface uses the binary operators. Unfortunately the binary operators & (and) and | (or) have very high priority, you will have to be very careful about not forgetting correct parenthesis.

The condition can be appended to query using following instance methods: And(), Or(), AndNot(), OrNot(). Argument of the function is either a generic condition (described later) or, if the query represents only 1-table query, a pair column=value.

foo = db.foo()
foo.And(nick='andrew').AndNot(city='alabama')
foo.Or(nick='peter',city='New York')
# foo selects all 'andrew's who are not from Alabama, OR all 'peter's who are from 'New York'

Generic conditions

Generic conditions are specified using column names - attributes of the select instance. Apart from using the methods mentioned above (And(), Or(), AndNot(), OrNot()), you may use in-place operators &= and |= - they work exactly as functions And() and Or(). The left side of the condition should be a column name - use (foo.nick == 'andy') instead of ('andy' == foo.nick).

foo = db.foo()
foo &= (foo.nick != 'andy') & ((foo.age < 10) | (foo.age > 50)) # Line 1
foo |= (foo.nick == 'andy') & (foo.age > 50)                    # Line 2
# The result is (Line_1 OR Line_2)

Functions

SQL functions tend to have a pretty stupid syntax, which is not by default supported by this module. Support for these functions can be easily added, if you find it useful. That said, sqlabstr supports all normal multiparameter functions. They are accessible in the Database.f namespace and can be freely used in general conditions, Order() and DescOrder() functions and AddCol(). SQL functions are available in the db.f. namespace.

foo = db.foo()
foo &= db.f.surname(foo.name) == 'Bush' 
foo.AddCol(db.f.surname(foo.name), db.f.decode_id(foo.id))

Aggregate functions - grouping

SQL aggregate functions are available in db.a. namespace. If sqlabstr finds a column containing aggregate functions, it automatically groups the query by other columns (this is SQL requirement). If you want to group by a column, simply add it through AddCol(), and the column will be automatically added to GROUP BY clause. Sqlabstr supports the '*' and automatically fetches list of columns from database and builds column list in GROUP BY clause.

foo = db.foo()
foo.AddCol(db.a.average(foo.age), 
           foo.city)
## Computes average age of people in different cities

HAVING clause

Adding a HAVING clause is done using Having() method.

foo = self.db.foo('abc')
foo.AddCol(self.db.a.sum(foo.xyz) >> '	sum')
foo.Having(self.db.a.sum(foo.xyz) > 5)

Column renaming - AS

Should you wish give the columns a name, use the '>>' operator:

foo = db.foo()
foo.AddCol(db.a.average(foo.age) >> 'average_age',
	   foo.city >> 'city_name')
for row in foo:
    print row.city_name,':',row.average_age

Subqueries in WHERE clause

You can use subqueries in the WHERE clause as easily as using other condition. Special operator '<<' is provided for operator IN, other operators (ANY...) can be easily provided e.g. as db.f.any.

test = db.test('city',state='California')

foo = db.foo('nick','name')
foo &= foo.city << test
# 'Test' table would contain all cities in California, 
# foo query would contain all people who live in california.

---