pydal

Massimo still maintans it at https://github.com/web2py/pydal and he's working on py4web at https://github.com/web2py/py4web. DAL was the first Python db abstraction I used, and I liked it well enough, but I've since used both Django and SQLAlchemy, and they both seem less opaque to me, maybe because not obscured by some of the weird things about web2py itself. I love the auto-generated backwards references you get in Django and SA when you use foreign key and many to many fields. They make life so much easier. I don't recall how that was achieved in DAL, I just remember it being... complicated.

It sounds like the poster would like to check out the python DAL library: https://github.com/web2py/pydal It's exactly what you want. It blew my mind, and taught me that python syntax can natively express SQL semantics. This gives me all that ORMs are really good at -- letting you avoid having to write one language in string-form inside of another language.

OP's frustrations are quite understandable but I can think of a couple of areas where ORM-like concepts have worked well. PyDAL[0] and Linq are both incredibly expressive and PyDAL is also low overhead (it's not an ORM, it's a data access language, which means it doesn't have the baggage of an object-oriented layer). Another reason to use a DAL like PyDAL is that, properly written, it can abstract away the data layer such that you can switch from (for example) MySQL to PostGres to Google Cloud SQL with essentially no retooling required. Finally, using a DAL can help keep inexperienced programmers on track with security, accessibility, and performance requirements. [0] https://github.com/web2py/pydal

I´m just a user and not a contributor (I don´t know the source code), but the project is following good webdev techniques * https://github.com/web2py/web2py/ * https://github.com/web2py/pydal

Have you checked web2py with its DAL? you can get it also as a standalone module https://github.com/web2py/pydal Also there is db.py from yhat.