def test_query(self): # Assert table query results from Table.search(). for kwargs, sql, rows in ( (dict(fields=db.ALL), "select persons.* from `persons`;", [ (1, u"john", 30, 2), (2, u"jack", 20, 2), (3, u"jane", 30, 1) ]), (dict(fields=db.ALL, range=(0, 2)), "select persons.* from `persons` limit 0, 2;", [ (1, u"john", 30, 2), (2, u"jack", 20, 2) ]), (dict(fields=db.ALL, filters=[ ("age", 30, "<") ]), "select persons.* from `persons` where persons.age<30;", [(2, u"jack", 20, 2)]), (dict(fields=db.ALL, filters=db.any(("age", 30, "<"), ("name", "john"))), "select persons.* from `persons` where persons.age<30 or persons.name='john';", [(1, u"john", 30, 2), (2, u"jack", 20, 2)]), (dict(fields=["name", "gender.name"], relations=[db.relation("gender", "id", "gender")]), "select persons.name, gender.name from `persons` left join `gender` on persons.gender=gender.id;", [(u"john", u"male"), (u"jack", u"male"), (u"jane", u"female")]), (dict(fields=["name", "age"], sort="name"), "select persons.name, persons.age from `persons` order by persons.name asc;", [(u"jack", 20), (u"jane", 30), (u"john", 30)]), (dict(fields=["name", "age"], sort=1, order=db.DESCENDING), "select persons.name, persons.age from `persons` order by persons.name desc;", [(u"john", 30), (u"jane", 30), (u"jack", 20)]), (dict(fields=["age", "name"], sort=["age", "name"], order=[db.ASCENDING, db.DESCENDING]), "select persons.age, persons.name from `persons` order by persons.age asc, persons.name desc;", [(20, u"jack"), (30, u"john"), (30, u"jane")]), (dict(fields=["age", "name"], group="age", function=db.CONCATENATE), "select persons.age, group_concat(persons.name) from `persons` group by persons.age;", [(20, u"jack"), (30, u"john,jane")]), (dict(fields=["id", "name", "age"], group="age", function=[db.COUNT, db.CONCATENATE]), "select count(persons.id), group_concat(persons.name), persons.age from `persons` group by persons.age;", [(1, u"jack", 20), (2, u"john,jane", 30)])): v = self.db.persons.search(**kwargs) v.xml self.assertEqual(v.SQL(), sql) self.assertEqual(v.rows(), rows) # Assert Database.link() permanent relations. v = self.db.persons.search(fields=["name", "gender.name"]) v.aliases["gender.name"] = "gender" self.db.link("persons", "gender", "gender", "id", join=db.LEFT) self.assertEqual( v.SQL(), "select persons.name, gender.name as gender from `persons` left join `gender` on persons.gender=gender.id;" ) self.assertEqual(v.rows(), [(u'john', u'male'), (u'jack', u'male'), (u'jane', u'female')]) print "pattern.db.Table.search()" print "pattern.db.Table.Query"
def test_query(self): # Assert table query results from Table.search(). for kwargs, sql, rows in ( (dict(fields=db.ALL), "select persons.* from `persons`;", [(1, u"john", 30, 2), (2, u"jack", 20, 2), (3, u"jane", 30, 1)]), (dict(fields=db.ALL, range=(0, 2)), "select persons.* from `persons` limit 0, 2;", [(1, u"john", 30, 2), (2, u"jack", 20, 2)]), (dict(fields=db.ALL, filters=[("age", 30, "<")]), "select persons.* from `persons` where persons.age<30;", [(2, u"jack", 20, 2)]), (dict(fields=db.ALL, filters=db.any(("age", 30, "<"), ("name", "john"))), "select persons.* from `persons` where persons.age<30 or persons.name='john';", [(1, u"john", 30, 2), (2, u"jack", 20, 2)]), (dict(fields=["name", "gender.name"], relations=[db.relation("gender", "id", "gender")]), "select persons.name, gender.name from `persons` left join `gender` on persons.gender=gender.id;", [(u"john", u"male"), (u"jack", u"male"), (u"jane", u"female")]), (dict(fields=["name","age"], sort="name"), "select persons.name, persons.age from `persons` order by persons.name asc;", [(u"jack", 20), (u"jane", 30), (u"john", 30)]), (dict(fields=["name","age"], sort=1, order=db.DESCENDING), "select persons.name, persons.age from `persons` order by persons.name desc;", [(u"john", 30), (u"jane", 30), (u"jack", 20)]), (dict(fields=["age","name"], sort=["age","name"], order=[db.ASCENDING, db.DESCENDING]), "select persons.age, persons.name from `persons` order by persons.age asc, persons.name desc;", [(20, u"jack"), (30, u"john"), (30, u"jane")]), (dict(fields=["age","name"], group="age", function=db.CONCATENATE), "select persons.age, group_concat(persons.name) from `persons` group by persons.age;", [(20, u"jack"), (30, u"john,jane")]), (dict(fields=["id", "name","age"], group="age", function=[db.COUNT, db.CONCATENATE]), "select count(persons.id), group_concat(persons.name), persons.age from `persons` group by persons.age;", [(1, u"jack", 20), (2, u"john,jane", 30)])): v = self.db.persons.search(**kwargs) v.xml self.assertEqual(v.SQL(), sql) self.assertEqual(v.rows(), rows) # Assert Database.link() permanent relations. v = self.db.persons.search(fields=["name", "gender.name"]) v.aliases["gender.name"] = "gender" self.db.link("persons", "gender", "gender", "id", join=db.LEFT) self.assertEqual(v.SQL(), "select persons.name, gender.name as gender from `persons` left join `gender` on persons.gender=gender.id;") self.assertEqual(v.rows(), [(u'john', u'male'), (u'jack', u'male'), (u'jane', u'female')]) print "pattern.db.Table.search()" print "pattern.db.Table.Query"
)) db.orders.append(product_id=1, customer_id=2) # Hofstadter orders pizza. # Show all the products in the database: print "There are", db.products.count(), "products available:" for row in db.products.rows(): print row # Note how the orders table only contains integer id's. # This is much more efficient than storing entire strings (e.g., customer address). # To get the related data, we can create a query with relations between the tables. q = db.orders.search(fields=[ "products.description", "products.price", "customers.name", "date" ], relations=[ relation("product_id", "products.id", "products"), relation("customer_id", "customers.id", "customers") ]) print print "Invoices:" for row in q.rows(): print row # (product description, product price, customer name, date created) print print "Invoice query SQL syntax:" print q.sql() print print "Invoice query XML:" print q.xml # The XML can be passed to Database.create() to create a new table (with data). # This is explained in the online documentation.
field("date", DATE, default=NOW) # By default, current date/time. )) db.orders.append(product_id=1, customer_id=2) # Hofstadter orders pizza. # Show all the products in the database: print "There are", db.products.count(), "products available:" for row in db.products.rows(): print row # Note how the orders table only contains integer id's. # This is much more efficient than storing entire strings (e.g., customer address). # To get the related data, we can create a query with relations between the tables. q = db.orders.search( fields = ["products.description", "products.price", "customers.name", "date"], relations = [ relation("product_id", "products.id", "products"), relation("customer_id", "customers.id", "customers") ] ) print print "Invoices:" for row in q.rows(): print row # (product description, product price, customer name, date created) print print "Invoice query SQL syntax:" print q.sql() print print "Invoice query XML:" print q.xml # The XML can be passed to Database.create() to create a new table (with data).