Really not trying to troll here, but I want to know why anyone uses a Python ORM here other than SQLAlchemy? It's been a while since I used Django's ORM but I recall the comparison between the two being very heavily in sqla's favor.
The Django community is focused on web apps, not e.g. off-line reporting, so a large percentage of the things which the ORM doesn't help with simply aren't a priority for most users. For something like 90% of the queries most people write, Django's ORM is easier to work with and the tight integration with model forms, the admin, etc. is a big selling point for the typical project on a deadline.
When you do need to do complex joins or use database-specific features, the .raw() / .extra() queryset methods handle enough to avoid it being a deal-breaker, particularly since the crazier your performance / feature requirements the more likely it is that you're going to need to ditch an ORM altogether.
EDIT: in case it wasn't clear, I have a ton of respect for SQLAlchemy. Nothing above should be seen as saying SQLA isn't good, merely that the choice of ORM usually isn't the most important factor in a project's decision.
The Django ORM is developed as part of the framework. As such, it benefits from the integration of other Django features. The Django pitch is: Django: The Web framework for perfectionists with deadlines. Meaning that everything is designed to get you up and running quickly. In the case of SQLAlchemy, that level of integration is not available as a result. Even though SQLAlchemy is much powerful, the benefits of the Django ORM outweigh any additional features provided by the former. Plus most web apps are simple cruds that will not require the level of fine tuning that SQLAlchemy provides.
Now, in case of APIs, I prefer Flask (if using Python) with SQLAlchemy (if not flat SQL). It is the inverse of Django. No additional features than the ones you need as a base. Build on top of it rather than build with.
I'm fairly new to Python (~6 months) and started with SQLAlchemy, but recently switched our app to Peewee (http://peewee.readthedocs.org) because of frustrations with the SQLAlchemy "session".
The standard method of scoped_session(sessionmaker(engine)) will return a thread-local session - so all data manipulation effectively shares a global session.
- Every change needs to be followed with a commit() or rollback(), or you'll end up with rubbish in the session that some later call will inadvertantly commit.
- If you want to do a general "update where" call, make sure you use "synchronize_session=False" and then session.expire_all(), otherwise the session will be out of date.
It felt like every time I had code working with data, I also had a non-trivial amount of session management. It wasn't abstracting the database access away, it was making everything very SQLAlchemy session specific.
To be honest I'm somewhat second guessing my decision, because Peewee is far from the Python standard. But it's simple, understandable and clear, and doesn't force a strange "don't forget to manage the global state" mindset onto everything.
FWIW I use Pyramid with SQLAlchemy and it comes with a transaction manager plugin which automates the begin, commit and rollback on error of transactions. 99% of the time I don't need to think about transactions, only during CLI tool development and only barely. YMMV
The documentation has a pretty good example where they bundle session related functionality into a reusable context manager (Python's `with` statement). I took this approach in one of my projects, and it worked out rather well.
So where do you use this "with session_scope() as session:" context wrapper? Do you wrap your entry point in it, so the entire app is implicitly using the session (as the example shows)? Or do you call it in each separate function that needs data access?
If the former, how do you handle commit() and rollback() for "local" (per-function) changes? It sounds like either "never rollback" or "always commit" is necessary. And I think "always commit" is more likely, because usually an entire "request" (whether we're talking web apps or not) isn't atomic.
So you've got this SQLAlchemy session context wrapper around the entry point, nice and separated from all the internal data functions. Except every data function actually needs that session to commit(), so it's either pulled from some global state, or passed around like a stinky baby.
But this can't be the case, as this inelegance is not pythonic, and SQLAlchemy is the Python ORM. What am I missing?
It has been sometime since I used either one really intensively (I still use Django's off and on in maintenance), but for a long period of time, despite the advantages for sqla, Django was still a lot more newbie friendly.
Not much fiddling with engines, metadata, etc. Not to mention knowing the form stuff was going to just work, compared to third-party solutions (as good as they are) with the other.
I use Django... The Django ORM is built in and does everything i need.
I use Salt... Which has native support for using the Django ORM which I'm already using.
And ... For some extra icing on the cake. I like doing things in an SOA which has a wonderful recipe in Django + Django REST Framework + Django ROA (The original https://bitbucket.org/david/django-roa/overview and one of its most promising forks https://github.com/charles-vdulac/django-roa)
With this, I can take a codebase, split off a particular chunk of the code, wrap it as an API with Django Rest Framework, then hook the rest of the service up to use the newly separated 'service' via Django ROA which lets Django work with the REST Api via the native ORM ... REST as a database!
SQLAlchemy is great for certain classes of applications but rarely are the advanced features needed for web applications. It's one of the few things I dislike about flask development in general. It's the standard ORM to roll with flask and compared to Django's ORM it's WAY over complicated. Flask and a NoSql db is a dream though.
SQLAlchemy's documentation makes the ORM seem a bit unapproachable, but Flask-SQLAlchemy does a good job of simplifying things: http://pythonhosted.org/Flask-SQLAlchemy/. With that documentation I don't think SQLAlchemy is much harder to use than Django's ORM.
I think SQLAlchemy's documentation is actually pretty good; it's extremely thorough. SQLAlchemy is also extremely well designed/written (as is Django), though it tends not to hide its complexity. SQLAlchemy's sessions are more complex than Django's equivalent. This is because SQLAlchemy assumes a more general case, where as Django assumes you're making a standard CRUD web application. They're both good at what they do.
