Pony ORM - Use Pure Python to Speak to Your Data
ponyorm.com
ponyorm.com
> 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.
Beware! CC licenses are not for software, and it actually makes it incompatible with GPL. Alexander and Alexey, please see http://wiki.creativecommons.org/Frequently_Asked_Questions#C...
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?)
One guess is that python is very popular among large non-developer communities, like GIS, Finance, and various groups doing numerical analysis. Many of these people aren't programmers and have no desire to become programs, they simply use python to automate tedious tasks and script the applications they use. If you tell them they have to learn a second language completely unrelated to the one they already know just to grab some data from a database, they'll probably push back. Tell them they can grab data from the database using the language they already know and they'll be much more interested in learning. Also the diagram editor is conceptually similar to many tools they might already be using and thus it might be easier for them to get a hang of it.
A few possible reasons I can think of;
- People prefer to only work in one language - you don't need to make the full mental shift
- It works better/looks better in Python editors
- It should be safer and cleaner that mashing SQL strings together.
- syntax highlighting - autocomplete - syntax check - query linting, finding common antipatterns - no SQL injections possible
And if you think about it, any library that was powerful enough for me to not write SQL anymore would basically have to rewrite the complete SQL spec using awkward hacks in a language that is totally unsuited for it.
And if you're writing an application of any complexity or size using an SQL database you need the full capacities of SQL. There are a lot of little features or quirks of SQL that these languges seem to overlook that you need to make your app perform.
Now I usually just write a couple functions like this:
query("SELECT one, two FROM mytable",
["WHERE weight > %f AND name = '%s'", weight, name]);
And as a plus, I don't have continuously look up/relearn each little feature of SQL in this little language, or if I write it this way is it actually going to compile into the SQL I want? It's pretty much inevitable that you're going to need to know/control the SQL that's being written sooner or later (usually sooner), so save yourself the extra headache and just go SQL from the start.
(o for o in Order if o.date_shipped.year == 2012)
.filter(extract("year", Order.date_shipped) == 2012)
I think it refers to the implementation, meaning the library has no C modules and is written 100% in Python.
This means it should be able to run on all Python implementations, including Jython, IronPython, PyPy, Google App Engine...
But in this case, they are referring to the fact that you don't have to write SQL and write your queries using Python syntax generators.
This minimizes the likelihood of users mistakenly continuing to use it despite its low quality, and thus maximizes global welfare.
> you're still littering Python with SQL keywords like order_by(), desc(), select(), and commit()
Well, I see them not as “SQL keywords” but as logical concepts
> The GROUP BY section of the documentation just reads like a rough translation of SQL into Python
Hmm, not sure I understand this. With PonyORM, GROUP BY section is generated automatically, and not present in Pony ORM queries. This eliminates the need to duplicate the same columns in SELECT and GROUP BY sections.
> Beyond a certain level of complexity, you need to know SQL in order to write complex queries.
I agree with this. But I hope PonyORM can lower entry barrier for new developers. Also, PonyORM allows developers to use pure SQL, if all they need from mapper is IdentityMap, caching, etc.
> By the way, why is there an order_by() method and a separate orderby() method?
Thanks for noticing this, it is a typo in documentation. The method was renamed from orderby() to order_by() recently.
select((item.order, item.order.total_price,
sum(item.price * item.quantity))
for item in OrderItem
if item.order.total_price < sum(item.price * item.quantity))I can understand some hesitation to learn something new, especially when there are so many new things to learn and the value of learning them is not always apparent...but I would assume that the benefits of knowing SQL would be readily apparent. I'm not even a developer and I would feel like a quadriplegic without it.
It also has remarkably poor tools for code reuse and abstraction. Have you ever worked with SPs that dynamically generate SQL code? I see it daily. Not only that, it's code written by professional database administrators.
That's relatively true of many languages. The reason SQL has been able to be optimized so heavily (and the reason why it's been adopted so broadly) is because it is so domain specific.
More specifically, it is optimized for expression set algebraic operations, and certain kinds of kernels to apply to resultant sets. It is most definitely NOT suited for most array or otherwise metrically-oriented data series, which is why you never see it used in HPC to express timeseries transformations or, say, 3D PDE diffusion simulations. For those applications, people have come up with different kinds of specialized languages which are rooted in an more appropriate data abstraction than merely set operations.
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...
https://github.com/ponyorm/pony/blob/orm/pony/orm/decompilin...
https://github.com/ponyorm/pony/blob/orm/pony/orm/asttransla...
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.
Having used Linq2SQL for a few years, I don't like this style of coding. Often, to optimize a query, we've had to look at the generated SQL and then reverse engineer it back to Linq to get the SQL that we wanted. While certain queries were really easy in Linq2SQL, the more complex ones were extremely difficult. I had to still understand and write the underlying SQL, then I had to figure out how to "translate" that into the Linq2SQL. In the long run, all time savings were nullified. I see the same with a DSL-type of wrapper like this.
Additionally, almost all good backend devs speak SQL, I can take a Java-person and without the Linq2SQL stuff, he could understand our backend, work on queries, etc. The Linq just added another layer of complexity for someone to think about.
(Granted, there's a database layer that we could truly put a lot of that stuff in, if our queries got really complex)
A team I'm a part of (language is C#) had to get rid of a library that handled SQL and do the SQL manually because the library was making a lot of inefficient calls. Sounds like a similar problem.
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?
> are people who find the repo on github directly at risk of violating the license?
Nope, why? Using it under AGPL is a valid option.
Given the queries I had to code lately, I'll take ARel and ActiveRecord scopes composability any day instead of mashing SQL fragment strings together and hoping for the best.
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.
According to SQL standard, aggregate functions must skip NULL values. The problem is with queries which return no rows. In this case, intuitive expected result of SUM aggregate function is zero, but, because of some quirk in SQL standard, SUM must return NULL if no rows were found.
Without coalesce function, result of SUM will be returned as None if no rows were found. Coalesce replaces this surprising None with zero.
Thanks.
For example, if I want to retrieve all pairs of different users with the same name, in Pony this query would look as follow:
select((u1, u2) for u1 in User for u2 in User if u1.name == u2.name and u1 != u2)
User2 = User.alias()
User.select(User, User2).join(User2).where((User.name == User2.name) & (User.id != User2.id))
staff_users = User.select().where(is_staff=True)
Tweet.select().where(~(Tweet.user << staff_users))
select(t for t in Tweet if t.user not in (u for u in User if u.is_staff))
select(t for t in tweet if not t.user.is_staff)
On there other side, it seems Peewee already has migration support, whereas in Pony migrations are not implemented yet, this is the next task.
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.
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.
Django:
Item.objects.filter(price__gt=3)
select(o for o in Item if o.price>3)
Item.select().where(Item.price>3)
$Item->where('price', '>', 3)->find();