Pony ORM - Use Pure Python to Speak to Your Data

> why such an obsession with "pure" Python?

With this statement we are trying to say Pony is not just to offer some syntax sugar. The end goal of Pony ORM is to understand the semantic of each Python generator which can be translated to database query and then do translation accordingly.

The direct benefit of this is that generator query are more high-level then resulted SQL, and can be much easier to understand and refactor. The simplest example is an automatic converting of attribute path traversing such as grade.student.group.department.name to a minimal set of SQL joins. Pony also can use attribute path in reverse direction, from “one” to “many”, such as department.groups.students.gpa, which in this case resulted in a multiset of all ‘gpa’ values for this department. Also you can see examples of query optimization in the documentation, for example when the subquery is converted to LEFT JOIN with GROUP BY, where it can give performance benefit.

There are more distant benefits of the semantic translation. In future, we plan to add support of denormalized database schemas. Such denormalization will take place on a physical level (i.e. in the database) while on the logical level (in Python) all queries and logic will remain the same. But first of all we are going to add the migration support.

Another future plan is to add support of MongoDB. This is a distant plan, but I think Pony architecture will allow this. After that, migration of PonyORM-based project from relational databases to noSQL and vice versa will be possible.

> An ORM like Django's offers a higher level of abstraction

I disagree with this. I think, any Django query can be written in PonyORM much more concisely, and there are easy-to-understand PonyORM queries which cannot be expressed in Django easily.

Also, Pony ORM completely eliminates N+1 select problem.

On one extreme, of course, there are spaghetti apps that mix PHP, HTML, CSS, JS, shell, and SQL in the same file. We all know and hate those apps. But is there any reason to jump to the other extreme and turn as much as possible into "pure" $LANGUAGE ?

As soon as your query gets moderately complicated, you're still littering Python with SQL keywords like order_by(), desc(), select(), and commit(). The GROUP BY section of the documentation just reads like a rough translation of SQL into Python, the only difference being the syntax. It's like watching a first-week ESL student try to construct sentences in English.

The documentation advertises that "Pony allows any programmer to write complex and effective queries against a database, even without being an expert in SQL." I don't think this is true for any ORM I've seen so far, whether in Python or in any other popular language. Beyond a certain level of complexity, you need to know SQL in order to write complex queries. But if you already know SQL, why translate long GROUP BY ... HAVING queries into Python only to have the ORM translate it back into SQL? Why are we trying so hard to avoid writing SQL? What are we going to do next? Write a library that translates pure Python into Lua scripts for your Redis server?

I like ORMs because they simplify frequent tasks, like grabbing a dozen items from the database and filtering them by a couple of columns. I also like them because they often come with caching and effective protections against SQL injection attacks. But I also think that purity is overrated. Both web apps and native apps are already a mixture of several different languages, both on the frontend and on the backend. Don't be afraid to add SQL to your belt, it's just another language.

(By the way, why is there an order_by() method and a separate orderby() method?)

It seems from this thread not many people appreciate the parity of:

    list(o for o in Item if o.price>3)

    select(o for o in Item if o.price>3)

After just helping someone get started that was new to Python and Django, it was weird helping them learn list comprehensions and generators and then having to teach such a divergent form of syntax to work with the Django ORM even though the a lot of the concepts should be similar.

In competition with Django ORM and Sqlalchemy, both of which are open source and battle tested, they will have to do some cutting edge marketing to justify the cost "per process".

The idea is great. We might see something like this pop up in Sqlalchemy or Django real soon...

As much as I dislike things like this that translate a language into another, but not quite right, as you have one syntax trying to mimic a completely different one, you do (or could) get the benefit of the possibility of autocompletion, syntax highlighting and lint checks.

I know that adding something like LINQ in Python is completely out of the question, as you'd have a special syntax case, and I agree with that.

For some time I've wondered how hard would it be to reverse traverse the Python AST to find literal strings that are being passed to a dbapi2 interface, and syntax highlight/lint/autocomplete the SQL on those strings. This gets there by bypassing the "Language is in a string" issue.

i'm not opposed to commercial licensing, it's just that the existing combination of options doesn't offer clear legal guarantees.

if you go to the website, the split-licensing is apparent - yet has issues with, what seems like, improper use of Creative Commons licensing.

if you go to the github repo, the only license presented is AGPLv3.

are people who find the repo on github directly at risk of violating the license?

  SELECT "c"."id"
  FROM "Customer" "c"
    LEFT JOIN "Order" "order-1"
      ON "c"."id" = "order-1"."customer"
  GROUP BY "c"."id"
  HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000

  select(c for c in Customer
           if sum(c.orders.price) > 1000)

I am not sure what flavor of SQL is beind used, but why are the table names and the field names surrounded by double quotes? They make the query look unnecesierily hairy.

Good job otherwise. Always glad to see people contributing to the community.

[1] https://github.com/coleifer/peewee

But then I thought screw that... SQL is a pretty good language so why not just make writing real SQL easier: https://github.com/agentgt/jirm/tree/master/jirm-core#sql-pl...

If you set up the an SQL Alchemy properly you can achieve the same thing.

If you wish complain about django then yes this sort of fixes it but it may break more than it fixes.

SQL Alchemy is free and better.

http://sqlkorma.com/

In my opinion, this is the way to write the core of an ORM and it should allow to use this abstraction layer too! It's somewhat between SQL queries as strings and ORM-level, I think.