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:
- 健壮性:您无需担心在为不同的数据库源编写 SQL 查询时可能会犯的语法错误.事实上，您不需要知道所有数据库源的语法.相同的 ORM 查询适用于所有人.无论是像 MySQL 这样的基于 SQL 的引擎，还是像 MongoDB 这样的基于 NoSQL 的引擎
- 可扩展性:随着业务需求或您处理的数据种类/数量的变化.更改数据库引擎是很常见的.您不必担心查询中的损坏，因为 ORM 会处理它.唯一的条件是您的 ORM 应该支持该数据源.
- 安全:您不必担心由于SQL 注入 等，因为 ORM 已经对它们起到了保护作用
- 信任:世界各地有大量聪明人致力于创建 ORM，以处理他们随着时间的推移所面临的场景和问题.我，作为一个单身人士，可能会错过其中的许多方面.因此，使用 ORM 不太容易出现我们可能面临的意外问题.(这并不意味着 ORM 是完美的，但它们不太容易出错)
- 时间:通过 ORM，您可以获得大量开源库的支持.例如，用于数据迁移、检查数据的门户网站、数据序列化程序等.因此，您可以节省时间做更重要的事情.
- 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
- 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 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)
- 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 scenario you always have the option to write raw SQL query to get the desired result.
因为我提到的优点，我喜欢 ORM.
I like ORMs due to the advantages I mentioned.
这篇关于SQLAlchemy (ORM) 与原始 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
