For more examples look at the tests:
https://github.com/web2py/pydal/blob/master/tests/dbapi.py
On Monday, 13 May 2019 23:18:42 UTC-7, Massimo Di Pierro wrote:
>
> You may have missed this but there is a new functionality in PyDAL that
> allows you to create very powerful APIs. It is called DBAPI.
> It is still not 100% stable but it is working and I could use some help
> testing it.
> web3py's equivalent of appadmin will be called dbadmin (90% done) and it
> is based on DBAPI.
> Even if primarily designed for web3py it works for web2py too and you can
> find some preliminary examples below.
> Hope it is self-explanatory.
>
> DBAPI EXAMPLES
>
> Inspired by GraphQL but not quite the same
>
> Less powerful but simpler to use
>
> Self descriptive (@model=True) and policy based (policy set serverside)
>
> Support complex queries like:
>
> - name.eq=Clark Kent
> - name.ne=Clark Kent
> - name.gt=Clark Kent
> - name.lt=Clark Kent
> - name.ge=Clark Kent
> - name.le=Clark Kent
> - name.startswith=Clark
> - name.contains=Kent
> - not.real_identity.name.contains=Wayne
> - not.superhero.tag.superpower.description.eq=Flight
>
> Support de-normalization:
>
> - @lookup=real_identity
> - @lookup=real_identity[name]
> - @lookup=real_identity[name,job]
> - @lookup=identity:real_identity[name,job]
> - @lookup=identity!:real_identity[name,job]
> - @lookup=superhero.tag
> - @lookup=superhero.tag[strength]
> - @lookup=superhero.tag[strength].superpower
> - @lookup=superhero.tag[strength].superpower.description
>
> Fast. Even the most complex query is implemented with at most 2 selects +
> 1 select for each denormalized link table
> Example Model
>
> db.define_table(
> 'person',
> Field('name'),
> Field('job'))
>
> db.define_table(
> 'superhero',
> Field('name'),
> Field('real_identity', 'reference person'))
>
> db.define_table(
> 'superpower',
> Field('description'))
>
> db.define_table(
> 'tag',
> Field('superhero', 'reference superhero'),
> Field('superpower', 'reference superpower'),
> Field('strength', 'integer'))
>
> Example Controller
>
> from pydal.dbapi import DBAPI, Policy
> policy = Policy()
> policy.set('*', 'GET', authorize=lambda tablename, id, get_vars,
> post_vars:True, allowed_patterns=['*'])
> policy.set('*', 'PUT', authorize=lambda tablename, id, get_vars,
> post_vars:False)
> policy.set('*', 'POST', authorize=lambda tablename, id, get_vars,
> post_vars:False)
> policy.set('*', 'DELETE', authorize=lambda tablename, id, get_vars,
> post_vars:False)
>
> def api():
> return DBAPI(db, policy)(request.method, request.args(0), request.args(1),
> request.get_vars, request.post_vars)
>
> Example GET URLs
> /superheroes/default/api.json/superhero
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "id": 1
> },
> {
> "real_identity": 2,
> "name": "Spiderman",
> "id": 2
> },
> {
> "real_identity": 3,
> "name": "Batman",
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:06.764966",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero&@model=true
>
> {
> "status": "error",
> "timestamp": "2019-05-14T06:14:06.997662",
> "message": "Invalid table name: superhero_amp_@model=true",
> "code": 400,
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=real_identity
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": {
> "name": "Clark Kent",
> "job": "Journalist",
> "id": 1
> },
> "name": "Superman",
> "id": 1
> },
> {
> "real_identity": {
> "name": "Peter Park",
> "job": "Photographer",
> "id": 2
> },
> "name": "Spiderman",
> "id": 2
> },
> {
> "real_identity": {
> "name": "Bruce Wayne",
> "job": "CEO",
> "id": 3
> },
> "name": "Batman",
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:06.931746",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=identity:real_identity
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "id": 1,
> "identity": {
> "name": "Clark Kent",
> "job": "Journalist",
> "id": 1
> }
> },
> {
> "real_identity": 2,
> "name": "Spiderman",
> "id": 2,
> "identity": {
> "name": "Peter Park",
> "job": "Photographer",
> "id": 2
> }
> },
> {
> "real_identity": 3,
> "name": "Batman",
> "id": 3,
> "identity": {
> "name": "Bruce Wayne",
> "job": "CEO",
> "id": 3
> }
> }
> ],
> "timestamp": "2019-05-14T06:14:07.000183",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=identity!:real_identity[name,job]
>
>
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "name": "Superman",
> "identity_job": "Journalist",
> "identity_name": "Clark Kent",
> "id": 1
> },
> {
> "name": "Spiderman",
> "identity_job": "Photographer",
> "identity_name": "Peter Park",
> "id": 2
> },
> {
> "name": "Batman",
> "identity_job": "CEO",
> "identity_name": "Bruce Wayne",
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:06.881501",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=superhero.tag
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "superhero.tag": [
> {
> "strength": 100,
> "superhero": 1,
> "id": 1,
> "superpower": 1
> },
> {
> "strength": 100,
> "superhero": 1,
> "id": 2,
> "superpower": 2
> },
> {
> "strength": 100,
> "superhero": 1,
> "id": 3,
> "superpower": 3
> },
> {
> "strength": 100,
> "superhero": 1,
> "id": 4,
> "superpower": 4
> }
> ],
> "id": 1
> },
> {
> "real_identity": 2,
> "name": "Spiderman",
> "superhero.tag": [
> {
> "strength": 50,
> "superhero": 2,
> "id": 5,
> "superpower": 2
> },
> {
> "strength": 75,
> "superhero": 2,
> "id": 6,
> "superpower": 3
> },
> {
> "strength": 10,
> "superhero": 2,
> "id": 7,
> "superpower": 4
> }
> ],
> "id": 2
> },
> {
> "real_identity": 3,
> "name": "Batman",
> "superhero.tag": [
> {
> "strength": 80,
> "superhero": 3,
> "id": 8,
> "superpower": 2
> },
> {
> "strength": 20,
> "superhero": 3,
> "id": 9,
> "superpower": 3
> },
> {
> "strength": 70,
> "superhero": 3,
> "id": 10,
> "superpower": 4
> }
> ],
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:06.924036",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=superhero.tag.superpower
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "superhero.tag.superpower": [
> {
> "strength": 100,
> "superhero": 1,
> "id": 1,
> "superpower": {
> "id": 1,
> "description": "Flight"
> }
> },
> {
> "strength": 100,
> "superhero": 1,
> "id": 2,
> "superpower": {
> "id": 2,
> "description": "Strength"
> }
> },
> {
> "strength": 100,
> "superhero": 1,
> "id": 3,
> "superpower": {
> "id": 3,
> "description": "Speed"
> }
> },
> {
> "strength": 100,
> "superhero": 1,
> "id": 4,
> "superpower": {
> "id": 4,
> "description": "Durability"
> }
> }
> ],
> "id": 1
> },
> {
> "real_identity": 2,
> "name": "Spiderman",
> "superhero.tag.superpower": [
> {
> "strength": 50,
> "superhero": 2,
> "id": 5,
> "superpower": {
> "id": 2,
> "description": "Strength"
> }
> },
> {
> "strength": 75,
> "superhero": 2,
> "id": 6,
> "superpower": {
> "id": 3,
> "description": "Speed"
> }
> },
> {
> "strength": 10,
> "superhero": 2,
> "id": 7,
> "superpower": {
> "id": 4,
> "description": "Durability"
> }
> }
> ],
> "id": 2
> },
> {
> "real_identity": 3,
> "name": "Batman",
> "superhero.tag.superpower": [
> {
> "strength": 80,
> "superhero": 3,
> "id": 8,
> "superpower": {
> "id": 2,
> "description": "Strength"
> }
> },
> {
> "strength": 20,
> "superhero": 3,
> "id": 9,
> "superpower": {
> "id": 3,
> "description": "Speed"
> }
> },
> {
> "strength": 70,
> "superhero": 3,
> "id": 10,
> "superpower": {
> "id": 4,
> "description": "Durability"
> }
> }
> ],
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:07.118823",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=powers:superhero.tag[strength].superpower[description]
>
>
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "powers": [
> {
> "strength": 100,
> "superpower": {
> "description": "Flight"
> }
> },
> {
> "strength": 100,
> "superpower": {
> "description": "Strength"
> }
> },
> {
> "strength": 100,
> "superpower": {
> "description": "Speed"
> }
> },
> {
> "strength": 100,
> "superpower": {
> "description": "Durability"
> }
> }
> ],
> "id": 1
> },
> {
> "real_identity": 2,
> "name": "Spiderman",
> "powers": [
> {
> "strength": 50,
> "superpower": {
> "description": "Strength"
> }
> },
> {
> "strength": 75,
> "superpower": {
> "description": "Speed"
> }
> },
> {
> "strength": 10,
> "superpower": {
> "description": "Durability"
> }
> }
> ],
> "id": 2
> },
> {
> "real_identity": 3,
> "name": "Batman",
> "powers": [
> {
> "strength": 80,
> "superpower": {
> "description": "Strength"
> }
> },
> {
> "strength": 20,
> "superpower": {
> "description": "Speed"
> }
> },
> {
> "strength": 70,
> "superpower": {
> "description": "Durability"
> }
> }
> ],
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:07.260728",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=powers!:superhero.tag[strength].superpower[description]
>
>
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "powers": [
> {
> "strength": 100,
> "description": "Flight"
> },
> {
> "strength": 100,
> "description": "Strength"
> },
> {
> "strength": 100,
> "description": "Speed"
> },
> {
> "strength": 100,
> "description": "Durability"
> }
> ],
> "id": 1
> },
> {
> "real_identity": 2,
> "name": "Spiderman",
> "powers": [
> {
> "strength": 50,
> "description": "Strength"
> },
> {
> "strength": 75,
> "description": "Speed"
> },
> {
> "strength": 10,
> "description": "Durability"
> }
> ],
> "id": 2
> },
> {
> "real_identity": 3,
> "name": "Batman",
> "powers": [
> {
> "strength": 80,
> "description": "Strength"
> },
> {
> "strength": 20,
> "description": "Speed"
> },
> {
> "strength": 70,
> "description": "Durability"
> }
> ],
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:07.252807",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?@lookup=powers!:superhero.tag[strength].superpower[description],identity!:real_identity[name]
>
>
>
> {
> "count": 3,
> "status": "success",
> "code": 200,
> "items": [
> {
> "name": "Superman",
> "identity_name": "Clark Kent",
> "powers": [
> {
> "strength": 100,
> "description": "Flight"
> },
> {
> "strength": 100,
> "description": "Strength"
> },
> {
> "strength": 100,
> "description": "Speed"
> },
> {
> "strength": 100,
> "description": "Durability"
> }
> ],
> "id": 1
> },
> {
> "name": "Spiderman",
> "identity_name": "Peter Park",
> "powers": [
> {
> "strength": 50,
> "description": "Strength"
> },
> {
> "strength": 75,
> "description": "Speed"
> },
> {
> "strength": 10,
> "description": "Durability"
> }
> ],
> "id": 2
> },
> {
> "name": "Batman",
> "identity_name": "Bruce Wayne",
> "powers": [
> {
> "strength": 80,
> "description": "Strength"
> },
> {
> "strength": 20,
> "description": "Speed"
> },
> {
> "strength": 70,
> "description": "Durability"
> }
> ],
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:07.189899",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?name.eq=Superman
>
> {
> "count": 1,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "id": 1
> }
> ],
> "timestamp": "2019-05-14T06:14:07.185643",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?real_identity.name.eq=Clark Kent
>
> {
> "count": 1,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "id": 1
> }
> ],
> "timestamp": "2019-05-14T06:14:07.230685",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?not.real_identity.name.eq=Clark
> Kent
>
> {
> "count": 2,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 2,
> "name": "Spiderman",
> "id": 2
> },
> {
> "real_identity": 3,
> "name": "Batman",
> "id": 3
> }
> ],
> "timestamp": "2019-05-14T06:14:07.287187",
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?superhero.tag.strength.gt=80&@lookup=powers!:superhero.tag.superpower
>
>
>
> {
> "status": "error",
> "timestamp": "2019-05-14T06:14:07.296769",
> "message": "'amp'",
> "code": 400,
> "api_version": "0.1"
> }
>
> /superheroes/default/api.json/superhero?superhero.tag.superpower.description=Flight
>
>
>
> {
> "count": 1,
> "status": "success",
> "code": 200,
> "items": [
> {
> "real_identity": 1,
> "name": "Superman",
> "id": 1
> }
> ],
> "timestamp": "2019-05-14T06:14:07.286926",
> "api_version": "0.1"
> }
>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/62647296-4877-4d3f-ad19-992bd8f32beb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
