What am I missing about SqlAlchemy?

Question

So I wanted to introduce a friend to the wonderful world of python ORM libraries, which I still know little about.

Our databases are extremely fragmented and at times under normalized (for efficiency) and over normalized. Making "entities" out of it at this point is too much work (we have lots of legacy php code working with this) so the best I could think of was using sqlsoup to introspect our tables so he didn't have to write any mappers which he hated.

We liked the fact that selecting all columns and records from tables was easier this way, updating single items was also very easy since it could be done with a single query.

But everything else seems to take more code than custom SQL code, he wasn't thrilled when I couldn't select just a few columns, but what really bothered me is that even simple filters are more verbose. Example:

soup.mytable.filter_by(and_(soup.mytable.foo < 'x', soup.mytable.foo >= 'y'))

Is not easier than raw SQL (plus a custom querying object that does auto-escaping and such)...

mydb("SELECT * FROM mytable WHERE foo < $x AND foo >= $y", x='x', y='y')

Specially considering that we don't have to learn every MySQL function again. So except for the database agnosticism that we don't use, how can SqlAlchemy (or any Python ORM) make my life easier?

Answer 1

Well, by abstracting away of a raw SQL code to the SQLAlchemy construct, you can support multiple RDBMS without worrying about compatibility.

On the other hand, the power of such ORMS unleashes when you start adding relations to your schema - in such case ORM code would be definitely shorter that it's JOIN-esque SQL equivalent.

Answer 2

Advantages of SqlAlchemy:

• You can easily build complex queries from code fragments. If you try the same with SQL, you'll run into all sorts of issues (adding parentheses in the right places, escaping values, mapping data from DB columns to something your code can use).
• You can run the same code against different databases (say your data is in a MySQL and an Oracle DB)

Of course, SA will not produce as compact code as an SQL expert. For people who are in the SQL mindset, using a tool that creates SQL under the hood, is always a horror. But SQL is harder to maintain than Python code. So if you're not a SQL pro, the result will be good enough and you will be much faster.

Lastly, the SA will help you avoid many silly mistakes and security holes which is very important if you write something for the web.

Answer 3

With relationships, it is much easier to access the related objects.

For instance, to give a raise to a whole group of staff who work at a given location:

for person in location.staff:
    person.salary *= 1.05

It is much easier to do complicated operations in python than in SQL.

I've also used SQLAlchemy to migrate data from one database to another, keeping relations intact.

Answer 4

Also, with SA, you can pass query from one function to another, filter it and so on. It can be very handy sometimes and it is completely ugly, if you tring to build RAW SQL in that way.

Answer 5

The point of SQLAlchemy is not code golf.

SQL wins code golf all the time.

If this is your metric "... take more code than custom SQL code, ... even simple filters are more verbose..." you're doing the wrong thing.

If you want the most compact representation possible, you MUST use SQL. No choices. SQL is always the most compact notation.

If, on the other hand, you value readability, maintainability, database neutrality, a way to migrate your schema without breaking your applications, then SQLAlchemy is for you.

If you value short SQL statements, do not waste time with ORM.

Answer 6

SQL Alchemy has the following advantages:

• Session management for transactions, rollback, etc, including scoped_session for a form of thread safety.
• You can write select statements that are close to SQL (you're not limited to filter_by as in your example). You're not forced to use the ORM.
• You don't have to use SQL Soup if you want to avoid writing mappers, BTW, as long as you're willing to at least specify the table names; just use autoload.
• Note also that you can select specific columns, contrary to what is stated in your question, even with the ORM (as of 0.5).

See also SQLAlchemy's author's most recent blog entry for some more reasons.

Answer 7

I'm quite good with SQL and I had a similar problem figuring out why on earth I'd want to learn a complex orm like sqlalchemy.

Bottom line it's harder to make security gaffes, it's easier to do complex things (most of the time), and it's just plain nice to work directly with objects.

I often spent quite a lot of work writing update and delete queries, but with an orm that's automatic and as natural as it can possibly be.

I also really liked the database agnostic part. I never thought it was an issue, we only used MySQL - until we switched to Postgres, then it saved the day.

And it is very nice to add methods and properties to the model classes, it makes using the data layer of the code very natural.

Answer 8

Yesterday I spend four hours for writing simple subquery in SQLAlchemy for SQLite then turn directly to pysqlite which allow me to wrote and execute simple human-understandable SQL request.

I mean that SQLAlchemy is a freaky way to using some specified database's SQL engine.