new DBAPI in pydal for both web2py and web3py

[Massimo Di Pierro]

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" }

[Massimo Di Pierro]

For more examples look at the tests:

https://github.com/web2py/pydal/blob/master/tests/dbapi.py

[Anthony]

Looks great. You might want to consider an alternative name to avoid confusion with the Python DB-API specification (maybe just call the class API -- i.e., pydal.API rather than pydal.DBAPI).

[Massimo Di Pierro]

We can change the name but api is too ambiguous. Any other idea?

[Anthony]

On Wednesday, May 15, 2019 at 12:53:27 AM UTC-4, Massimo Di Pierro wrote:

We can change the name but api is too ambiguous. Any other idea?

Not sure. RESTAPI? HTTPAPI? 

[Carlos Costa]

Aweome!

I made a similar API for my apps, but is simpler.

I like this one more as it is more complete.

Mine is called Consult (because we call query as consulta in Portuguese)

This is very useful to integrate ui components such as datatable, or anything.

Maybe dal-api should be a good name.

If you want to use Consult, it is ok if it make sense for you, lol.

[黄祥]

for naming perhaps python module : acronym can give any idea:
pip3 install acronym

acronym "database abstraction layer application programming interface"

put whatever you want

best regards,

stifan

[Massimo Di Pierro]

how about we simply call it "Access"?

[Val K]

What about 'Bus'?

[Massimo Di Pierro]

Bus is a good name, but good for something else. For example a pubsub serverside service.

[Daniel Guilhermino]

My 20 cents...  Why not Aditus?

Em terça-feira, 14 de maio de 2019 03:18:42 UTC-3, Massimo Di Pierro escreveu:

[Massimo Di Pierro]

I feel like I am missing something. This is the first entry from google: https://en.wikipedia.org/wiki/Aditus_to_mastoid_antrum

[Daniel Guilhermino]

I feel like noob ... I suggested because Aditus in Latin is "Access".

And my google search (Brazilian) don't show anything like this on 2 firsts pages.

:(

mirimQL

mirim = small in "Tupi language"

Old Tupi or classical Tupi is an extinct Tupian language which was spoken by the native Tupi people of Brazil, mostly those who inhabited coastal regions in South and Southeast Brazil.

https://en.wikipedia.org/wiki/Tupi_language

[Val K]

Maybe 'Gate' or 'Gateway'

[villas]

acquisAPI

acquis Shortened from acquis communautaire: French acquis (“that which has been acquired or obtained”) + communautaire (“of the community”).

[António Ramos]

there we go again about naming something ... 

Jut call it Gluino! to glue data to the app..

--
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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/b89a5a76-8692-40f9-b797-4eebfec67cca%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Carlos Costa]

I liked it, sounds Italian.

Sometimes when you have a restriction it inspires creativity. 

For example, if we would have a rule to give only Italian (Massimo origin is the reason here) names to Web2py stuff (web2py itself included) we could come up with more natural and interesting names.

Like Django. It sounds natural and cool. But I don't like the framework. But I believe it's one thing the helps spread the word and make it well known.

I love web2py and I am already loving web3py but surely do not love these names, neither the fact web2py is little known these days.

I really hope that web3py will be the next top fullstack web framework, and will contribute for that. But this name does not help.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/CAEM0BxM9zSuGORmypOr2Tw6BCnYpidNZegG%3DP0J2EpMO0DCBaA%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

At.

Carlos J. Costa

--------------------------------------------------------------

Cientista da Computação - Esp. Gestão em Telecom

[Massimo Di Pierro]

I am embarrassed I have forgotten all about latin. :-(

The italian equivalent would be "accesso" but does not sound nice to me.

I am not too concerned about naming conflicts as long as the name is simple to remember and conveys what it does.

After all it is the name of a file and a class, not a package.

I do not think Gate/ Bus, etc convey it. {something}QL would be nice but {anything simple}QL appears to be taken.

Also this is not just a QL as it supports all rest methods with policies.

Massimo

[John Bannister]

How about Despresso (for database express O ).  J

--

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+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/059b32ee-e5f0-45d4-800f-cfee8d353202%40googlegroups.com.

[Massimiliano]

DALQL

--
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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/059b32ee-e5f0-45d4-800f-cfee8d353202%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Massimiliano

[John Bannister]

As you are da man I would suggest DaQL .. DALQL is a bit of a mouth full J

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/CANPTPxJumAdQ3veA%3Dw76xbrAD-aFLDZoyrzhTQJ9Nu88HzZt9Q%40mail.gmail.com.

[Massimo Di Pierro]

How about restapi or friendlyql?

I do not want to tie this to web3py or dal too much. there is no reason this syntax cannot be ported to other ORMs

On Friday, 17 May 2019 10:02:24 UTC-7, John Bannister wrote:

As you are da man I would suggest DaQL .. DALQL is a bit of a mouth full J

 

From: web...@googlegroups.com [mailto:web2py@googlegroups.com] On Behalf Of Massimiliano
Sent: 17 May 2019 18:56
To: web...@googlegroups.com
Subject: Re: [web2py] Re: new DBAPI in pydal for both web2py and web3py

 

DALQL

 

 

On Fri, May 17, 2019 at 5:42 PM Massimo Di Pierro <massimo....@gmail.com> wrote:

I am embarrassed I have forgotten all about latin. :-(

 

The italian equivalent would be "accesso" but does not sound nice to me.

 

I am not too concerned about naming conflicts as long as the name is simple to remember and conveys what it does.

After all it is the name of a file and a class, not a package.

 

I do not think Gate/ Bus, etc convey it. {something}QL would be nice but {anything simple}QL appears to be taken.

Also this is not just a QL as it supports all rest methods with policies.

 

Massimo

 

 

--
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+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/059b32ee-e5f0-45d4-800f-cfee8d353202%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

 

--

Massimiliano

--
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+unsubscribe@googlegroups.com.

[Massimiliano]

+1 for restapi :-) 

To unsubscribe from this group and stop receiving emails from it, send an email to web...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/059b32ee-e5f0-45d4-800f-cfee8d353202%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

 

--

Massimiliano

--
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 web...@googlegroups.com.

[villas]

restQL sounds OK.  Very generic, but it does what it says on the tin.

What about stupidQL?   :)  ok just kidding!

[Carlos Costa]

+1 restapi

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/dae7d0f9-f42e-47df-8eec-409f7b520710%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

[黄祥]

restQL sounds OK.  Very generic, but it does what it says on the tin.

What about stupidQL?   :)  ok just kidding!

like the idea, it can read as :

restQL = restkill
stupidQL = stupidkill

just my imagination by cranberries

best regards,

stifan

[Marco Mansilla]

This one sounds good to me, since we already have stupid.css

--

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+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/10a30a90-2c6b-4b33-9dfd-54e59c433d4f%40googlegroups.com.

[Massimo Di Pierro]

OK. RestAPI wins. I renamed it.

+1 restapi

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/dae7d0f9-f42e-47df-8eec-409f7b520710%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.