SQLAlchemy(ORM)与原始SQL查询 [英] SQLAlchemy (ORM) vs. raw SQL queries
问题描述
上个月，我一直致力于研究Flask，这是一个用于构建Web应用程序的python框架.
during the last month i've dedicated myself to the study of Flask, a python framework for building web application.
在网上找到了不同的教程之后，我发现了SQLAlchemy.
Following different tutorials i've found online, I've discovered SQLAlchemy.
说实话，我发现它很复杂，并且没有什么用，因为我对SQL语言有很好的了解.
To be honest, i find it complicated and not really useful since i have a pretty good knowledge of SQL language.
我想了解的是，使用像我缺少的SQLAlchemy这样的ORM是否有重大收获(也许在使用我不了解的纯sql时存在一些安全问题?).
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?).
此外，如果您能向我建议使用纯SQL查询的最佳python库是什么，我将不胜感激.
Also, i would appreciate if you could advice me of what's the best python library for working with pure SQL queries.
推荐答案
有很多.我看到使用ORM代替原始SQL查询的最大优点是:
There are many. The biggest advantages I see of using ORM instead of raw SQL queries are:
- 健壮性:您不必担心在为不同的Databse源编写SQL查询时可能会犯的语法错误.实际上，您不需要了解所有数据库源的语法.相同的ORM查询适用于所有人.无论是基于SQL的引擎(如MySQL)还是基于NoSQL的引擎(如MongoDB)
- 可伸缩性:随着业务需求或您要处理的数据种类/数量的变化.更改数据库引擎是很常见的.您不必担心查询中的损坏，因为ORM会处理它.唯一的条件是您的ORM应该支持该数据源.
- 安全性:您不必担心由于 SQL注入 等，因为ORM已经对其采取了防护措施
- 信任:全世界有很多聪明的人致力于创建ORM，以照顾场景和随着时间的推移所面临的问题.我，一个人可能会错过许多方面.因此，使用ORM不太容易出现我们可能会遇到的意外问题. (这并不意味着ORM是完美的，但是不太容易出错)
- 时间:借助ORM，您将获得大量开放源代码库的支持.例如，用于数据迁移，用于检查数据的Web门户，数据序列化程序等.因此，您可以节省时间来处理更重要的事情.
- Robustness: You need not to worry about the syntax errors you might make in writting the SQL query for different Databse sources. Infact 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
- 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.
- 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
- Trust: There are huge bunch of intelligent minds arround 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)
- 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:
- 速度:ORM较慢，因为它们充当代码和查询执行之间的中间件.实际上，ORM在内部创建相同的原始查询以获得所需的结果，
- 范围:ORM可能会限制您的实现范围.正如我提到的，它们充当中间件.您的数据库引擎可能支持某些功能，但该功能未在ORM中实现.但是在这种情况下，您始终可以选择编写原始SQL查询来获得所需的结果.
- 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,
- 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 scenarion you always have the option to write raw SQL query to get the desired result.
由于我提到的优点，我喜欢ORM.
I like ORMs due the advantages I mentioned.
这篇关于SQLAlchemy(ORM)与原始SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
