 |
 |
 |
Module Reference |  |
 |
 |
|
|
|
Module Reference | |
|
|
You can build complex data structures because each table can contain as many columns, multiple, related and single joins as you like. This isn't a lot of use if you cannot then select the information you want. So far you know how to select data using a series of keys or rowids but the power of SQL is in being able to perform complex queries on that information. The web.database.object module has a facility for doing just that.
For this example we create two tables:
import web.database, web.database.object, datetime
connection = web.database.connect(type="sqlite",database="object-query.db")
cursor = connection.cursor()
person = web.database.object.Table("Person")
person.addColumn(web.database.object.String(name="firstName"))
person.addColumn(web.database.object.String(name="surname", unique=True, required=True, key=True))
queryExample = web.database.object.Table('QueryExample')
queryExample.addColumn(web.database.object.Date(name="testDate"))
queryExample.addColumn(web.database.object.Integer(name="testInteger"))
queryExample.addColumn(web.database.object.Integer(name="testNumber"))
queryExample.addColumn(web.database.object.Email(name="email"))
database = web.database.object.Database()
database.addTable(person)
database.addTable(queryExample)
database.init(cursor)
database['Person'].insert(firstName="John", surname="Smith")
database['Person'].insert(firstName="Owen", surname="Jones")
database['QueryExample'].insert(
testDate=datetime.date(2004,7,11),
testInteger = 10,
testNumber = 15,
email = 'james@example.com'
)
To match any rows where the firstName is 'John' we make use of the column attribute of each table. The column attribute is a magic dictionary which allows you to compare columns to objects in natural Python code to produce a where clause string. It is best explained by an example:
>>> where = database['Person'].column['firstName'] == "John"
>>> print where
(Person.firstName = 'John')
>>> rows = database['Person'].select(where=where)
>>> print rows
{'Smith': <web.database.object.Row from Person table, rowid=1, firstName='John', surname='Smith'>}
Here are some more examples.
>>> column = database['queryExample'].column >>> column.date == datetime.date(2003,12,12) "(QueryExample.testDate = '2003-12-12')" >>> column.integer < 5 "(QueryExample.testInteger < 5)"
You can also do more complex queries using AND, OR or NOT. There are two ways of doing this. Both methods are equivalent so please use whichever one you prefer.
AND, OR or NOT are methods of the QueryBuilder class.
>>> where = column.AND(column.email == 'james@jimmyg.org', column.integer < 5) "(QueryExample.email = 'james@jimmyg.org') AND (QueryExample.testInteger < 5)" >>> where = column.NOT(column.email == 'james@jimmyg.org') "NOT (QueryExample.email = 'james@jimmyg.org')"
&, | or ~ are defined to mean AND, OR or NOT respectively. You can use them to achieve the same result as above like this:
>>> where = (column.email == 'james@jimmyg.org') & (column.integer < 5) "((QueryExample.email = 'james@jimmyg.org') AND (QueryExample.testInteger < 5))" >>> where = ~(column.email == 'james@jimmyg.org') "(NOT (QueryExample.email = 'james@jimmyg.org'))"
Note:
The brackets are required for queries using the &, | or ~ operators because the operators have the same precedence as other Python operators.
The QueryBuilder is not suitable for all queries. For example it does not currently support the multiple, single or related joins. If you try to access these columns you will get an error saying the key is not found.
However, all is not lost. Since this is an SQL database after all you can use an SQL cursor.select() method to get the rowids of the rows you are after and then convert them to objects using the row() method of the appropriate table object.
This situation may change with later versions of the module.
|
|
|
Module Reference | |
|
|