SQLAlchemy (ORM) vs. raw SQL queries [duplicate]

Question

during the last month i've dedicated myself to the study of Flask, a python framework for building web application.

Following different tutorials i've found online, I've discovered SQLAlchemy.

To be honest, i find it complicated and not really useful since i have a pretty good knowledge of SQL language.

What i want to understand is if there is any major gain in using ORM like SQLAlchemy that i'm missing (maybe some security issue in using pure sql that i don't know about?).

Also, i would appreciate if you could advice me of what's the best python library for working with pure SQL queries.

Answer 1

There are many. The biggest advantages I see of using ORM instead of raw SQL queries are:

1. Robustness: You need not to worry about the syntax errors you might make in writing the SQL query for different Database sources. In fact you do not need to know the syntax of all the DB sources. Same ORM query works for all. Whether it is SQL based engine like MySQL, or NoSQL based engine like MongoDB
2. Scalability: With change in business requirement, or kind/amount of data you are handling. It is very common to change the database engine. You need not to worry about the breakage in query, as ORM handles that. The only condition is your ORM should support that data source.
3. Security: You need not to worry about the security breaches due to SQL Injections etc as the ORM already acts a protective shield against them
4. Trust: There are huge bunch of intelligent minds around the world who worked on creating the ORM taking care of the scenarios and the issues they faced over time. I, as one single person may miss many aspects of those. Hence, using ORM is less prone to unexpected issues that we might face. (That doesn't mean ORM's are perfect, but those are less prone to errors)
5. Time: With ORMs you get support of large number of open-source libraries. For example for data migration, web portal to check data, data serializers, etc. Hence, you can save your time for something much more important.

Even though they have some side-effects as well:

1. Speed: ORMs are slower as they act as a middleware between your code and the query execution. In fact, ORMs internally creates a same raw query to get the desired result,
2. Scope: ORM may restrict the scope of your implementation. As I mentioned, they act as a middleware. There is a possibility that your database engine supports some functionality but that was not implemented in the ORM. But in such scenario you always have the option to write raw SQL query to get the desired result.

I like ORMs due to the advantages I mentioned.