SQLAlchemy (ORM) versus raw SQL queries

Question

over the past month, I have devoted myself to studying Flask, the foundation of python for building a web application.

Following the various tutorials that I found on the Internet, I discovered SQLAlchemy.

Honestly, I find this difficult and not very useful, since I have pretty good knowledge of the SQL language.

I want to understand if there is any significant gain in using ORM, such as SQLAlchemy, which I am missing (maybe a security problem when using pure sql, which I do not know about?).

Also, I would appreciate it if you could advise me which is the best python library for working with pure SQL queries.

Answer 1

There's a lot. The biggest advantages that I see in using ORM instead of raw SQL queries are the following:

• Reliability You do not need to worry about syntax errors that can occur when writing an SQL query for different Databse sources. Infact you do not need to know the syntax of all database sources. The same ORM query works for everyone. Is it a SQL-based engine like MySQL or a NoSQL-based engine like MongoDB
• Scalability : with changing business requirements or the type / amount of data that you process. Very often the database engine changes. You do not need to worry about breaking down the request, as ORM handles this. The only condition is that your ORM must support this data source.
• Safety You do not need to worry about security breaches due to SQL Injections , etc., since ORM already has a firewall against them.
• Trust There are a huge number of intellectual minds in the world who have worked to create ORM, taking care of the scenarios and problems that they have encountered over time. I, as one person may miss many aspects of these. Consequently, using ORM is less prone to unexpected problems that we may encounter. (This does not mean that ORMs are ideal, but they are less error prone).
• Time . With ORM, you get support for a large number of open source libraries. For example, for data transfer, a web portal for checking data, data serializers, etc. Therefore, you can save time on something much more important.

Even though they have side effects:

• Speed : ORMs are slower as they act as middleware between your code and query execution. In fact, ORM internally creates the same raw query to get the desired result,
• Scope : ORM may limit the scope of your implementation. As I said, they serve as middleware. It is likely that your database engine supports some functions, but this was not implemented in ORM. But in such a scenario, you always have the opportunity to write a raw SQL query to get the desired result.

I like ORM because of the advantages mentioned above.