JSON Query for Web2py
A web2py module to retrieve data using JSON.
Possible Query attributes
fields: Projection.order_fields: Ordering records.group_fields: Grouping records.distinct_field: Distinct record. (support only single field)where: Selection.join: Join tables.limit: Limit records.merge: Flatten record. Use for e.g.join.
Features
Examples
To use, put jsonquery.py under project's module folder. Then, import in
controller.
e.g.
from jsonquery import JsonQuery
def testpage():
jsq = JsonQuery(db, logger)
query = dict(fields=[dict(table="students")])
rows = jsq.run(query)
return rowsField Selection (mandatory)
Query with specific fields.
Selecting All fields. The following is quivalent to db(db.students).select()
query = {
"fields": [{
"table": "students"
}]
}
OR
query = dict(fields=[dict(table="students")])Selecting with specific fields. The following is equivalent to db(db.students).select(db.students.id, db.students.name)
query = {
"fields": [{
"table": "students",
"fields": [
{"field": "id"},
{"field": "name"}
]
}]
}Alias
Aliasing fields. In web2py: db(db.students).select(db.students.name.with_alias("student_name"))
query = {
"fields": [
"table": "students",
"fields": [
{"field": "name", "alias": "student_name"}
]
]
}Total rows
Getting total number of rows in a table. In web2py, this can be done simply db(db.students).count().
But, in jsonquery's way:
query = {"fields": [
{
"table": "students",
"fields": [
{"field": "id", "count": True}
]
}
]}ORDER BY
Ascending
Web2py Query: db(db.students).select(orderby=db.students.name)
query = {
"fields": [{"table": "students"}],
"order_fields": [
"table": "students",
"fields": [
{"field": "name"}
]
]
}Descending
Web2py Query: db(db.students).select(orderby=~db.student.name)
query = {
"fields": [{"table": "students"}],
"order_fields": [
"table": "students",
"fields": [
{"field": "name", "sort": "desc"}
]
]
}GROUP BY
COUNT
Web2py Query: db(db.students).select(db.students.class_id, db.students.id.count(), groupby=db.students.class_id)
query = {"fields": [
{
"table": "students",
"fields": [
{"field": "class_id"},
{"field": "id", "count": True}
]
}],
"group_fields": [
{
"table": "students",
"fields": [
{"field": "class_id"}
]
}
]}Count field can be aliased, too.
query = {"fields": [
{
"table": "students",
"fields": [
{"field": "class_id"},
{"field": "id", "count": True, "alias": "student_count"}
]
}],
"group_fields": [
{
"table": "students",
"fields": [
{"field": "class_id"}
]
}
]}DISTINCT
Web2py query: db(db.students).select(db.students.class_id, distinct=db.students.class_id)
query = {
"fields": [
{
"table": "students",
"fields": [
{"field": "class_id"}
]
}
],
"distinct_field": {
"table": "students",
"field": "class_id"
}
}NOTE: Reference for current approach - #316 and #1129
WORKAROUND for DISTINCT
query = {
"fields": [
{
"table": "students",
"fields": [
{"field": "class_id"}
]}
],
"group_fields": [
{
"table": "students",
"fields": [
{"field": "class_id"}
]
}
]
}WHERE
Conditional operators
eqis similar to==neis similar to!=gteis similar to>=lteis similar to<=gtis similar to>ltis similar to<startis similar todb.students.name.startswith('Aung')endis similar todb.students.name.endswith('Naing')containis similar todb.students.name.contains('Myint')
For example in jsonquery's way:
query = {"where": [
{
"table": "students",
"conditions": [
{
"field": "name",
"value": "Aung",
"operator": "start"
}
]
}
]}JOIN
INNER JOIN
Joining two tables in web2py: db(db.students).select(db.students.ALL, db.borrow.ALL, join=db.borrow.on(db.students.id == db.borrow.borrower_id)).
In jsonquery:
query = {
"fields": [{
"table": "students"
}, {
"table": "borrow"
}],
"join": [{
"on": {"table": "students", "field": "id"},
"joiner": {"table": "borrow", "field": "borrower_id"}
}]
}LIMIT
In web2py:db(db.students).select(limitby=(0, 10)).
In jsonquery:
query = {
"limit": {
"start": 0,
"end": 10
}
}