如果仅用于原始sql查询，SQLAlchemy仍然是推荐的吗？ [英] Is SQLAlchemy still recommended if only used for raw sql query?

问题描述

使用Flask，我很想知道是否SQLAlchemy仍然是用原始SQL查询我的数据库的最好方法（直接 SELECT x FROM table WHERE ... ），而不是使用ORM，或者如果有一个更简单但功能强大的替代方案？

感谢您的回复

$ b 主要优点：它可以最好地防范SQL注入攻击。 SQLAlchemy做了正确的事情，无论你抛出的任何参数。

我发现它的工作奇迹根据条件调整生成的SQL。显示上面有多个过滤器控件的结果集？只需在一组if / elif / else结构中构建你的查询，你就知道你的SQL仍然是黄金。

下面是一些现场代码摘录版本，所以语法可能会有所不同）：

 ＃从表单
＃拉取开始和结束日期... 
＃如果已经设置了`start`和/或`end`，则建立一个约束。 
 created = None 
如果开始和结束：
 created = sa.sql.between（msg.c.create_time_stamp，
 start.replace（hour = 0，minute = 0，秒= 0），
 end.replace（小时= 23，分钟= 59，秒= 59））
 elif start：
 created =（msg.c.create_time_stamp> = 
 start.replace（hour = 0，minute = 0，second = 0））
 elif end：
 created =（msg.c.create_time_stamp< = 
 end.replace（hour = 23，分钟= 59，秒= 59））
 
＃更复杂的`from_`对象在这里建立，例如消失
＃[...] 
＃最终查询建立
 query = sa.select（[unit.c.eli_uid]，from_obj = [from_]）
 query = query.column（count（msg.c.id）.label（'sent'） ）
 query = query.where（current_store）
如果已创建：
 query = query.where（已创建）
  

来自这个代码的代码要复杂得多，但我想在这里突出显示日期范围代码。如果我不得不使用字符串格式来构建SQL，那么我可能会在某个地方引入一个SQL注入漏洞，因为忘记引用值会容易得多。

Using Flask, I'm curious to know if SQLAlchemy is still the best way to go for querying my database with raw SQL (direct SELECT x FROM table WHERE ...) instead of using the ORM or if there is an simpler yet powerful alternative ?

Thank for your reply.

解决方案

I use SQLAlchemy for direct queries all the time.

Primary advantage: it gives you the best protection against SQL injection attacks. SQLAlchemy does the Right Thing whatever parameters you throw at it.

I find it works wonders for adjusting the generated SQL based on conditions as well. Displaying a result set with multiple filter controls above it? Just build your query in a set of if/elif/else constructs and you know your SQL will be golden still.

Here is an excerpt from some live code (older SA version, so syntax could differ a little):

# Pull start and end dates from form
# ...
# Build a constraint if `start` and / or `end` have been set.
created = None
if start and end:
    created = sa.sql.between(msg.c.create_time_stamp, 
        start.replace(hour=0, minute=0, second=0),
        end.replace(hour=23, minute=59, second=59))
elif start:
    created = (msg.c.create_time_stamp >= 
               start.replace(hour=0, minute=0, second=0))
elif end:
    created = (msg.c.create_time_stamp <= 
               end.replace(hour=23, minute=59, second=59))

# More complex `from_` object built here, elided for example
# [...]
# Final query build
query = sa.select([unit.c.eli_uid], from_obj=[from_])
query = query.column(count(msg.c.id).label('sent'))
query = query.where(current_store)
if created:
    query = query.where(created)

The code where this comes from is a lot more complex, but I wanted to highlight the date range code here. If I had to build the SQL using string formatting, I'd probably have introduced a SQL injection hole somewhere as it is much easier to forget to quote values.

这篇关于如果仅用于原始sql查询，SQLAlchemy仍然是推荐的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！