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).
