SQLAlchemy（ORM，声明式）：如何从字典中的键/值构建查询？

发布于 2024-11-24 19:29:53 字数 1155 浏览 0 评论 0原文

使用 SQLAlchemy ORM（声明式形式），如何以编程方式根据字典中的一组条件创建查询？

我希望在用户表中搜索与之前在字典中收集的某些条件相匹配的记录。我无法提前知道将使用哪些字段，并且必须能够处理某些字段是整数，某些字段是字符串，可能有很多不同的字段等。

示例：

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

已请求两个查询，导致以下字典：

q1_dict = {'id' : 177}

q2_dict = {'name' : 'Johnny', 'email' : '[email protected]'}

是否有任何简单/通用的方法可以从这两个字典创建查询，只需依赖键与 User 类的属性匹配的事实，同时正确处理类型，自动转义不安全值等？

我花了几个小时在谷歌上搜索这个问题，并浏览了 SQLAlchemy 文档，但似乎找不到任何好的答案/示例。

解决方案：

因此，在你们的帮助下，解决方案似乎很简单：

User.query.filter_by(**q1_dict)

User.query.filter_by(**q2_dict)

...获取示例中所需的两个查询。

我已经查看了您提供的链接，dagoof，但我想我的“python”还不够强大，无法独自找到解决方案。 :)

原文

Using the SQLAlchemy ORM (declarative form), how do you programatically create a query from a set of conditions in a dictionary?

I wish to search for those records in a users table that match some criteria previously collected in a dict. I can not know in advance which fields will be used, and must be able to handle that some fields are Integers, some are Strings, that there can be a lot of different fields, etc.

Example:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

Two queries has been requested, resulting in the following dicts:

q1_dict = {'id' : 177}

q2_dict = {'name' : 'Johnny', 'email' : '[email protected]'}

Are there any simple/generic way I can create my queries from those two dicts, simply relying on the fact that the keys match the attributes of the User class, while handling types correctly, autoescaping unsafe values, etc?

I've spent several hours googling this, and browsing the SQLAlchemy documentation, but can't seem to find any good answers/examples.

Solution:

So, after the help from you guys, the solution seems to be as simple as:

User.query.filter_by(**q1_dict)

User.query.filter_by(**q2_dict)

...to get to the two queries needed in the example.

I had already looked at the links you provided, dagoof, but I guess my "python" just wasn't strong enough to get to the solution on my own. :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

妄断弥空 2024-12-01 19:29:53

尝试以下操作，参考此处：查询， filter_by

session.query(User).filter_by(**q1_dict)

Try the following, references here: Query, filter_by

session.query(User).filter_by(**q1_dict)

回复收藏 0 原文

故事和酒 2024-12-01 19:29:53

一些示例代码：

100         COND = and_()
.....
216         if hidxs_sg:$
217             clause = [AM.hidx == hidx for hidx in hidxs_sg]$
218             COND.append(or_(*clause))$
....
300         query = session.query(AM).filter(COND)$

关键是使用 and_() 创建 AND 条件并将单个表达式添加到条件中......

Some example code:

100         COND = and_()
.....
216         if hidxs_sg:$
217             clause = [AM.hidx == hidx for hidx in hidxs_sg]$
218             COND.append(or_(*clause))$
....
300         query = session.query(AM).filter(COND)$

The key is to create an AND condition using and_() and adding the single expressions to the condition....

回复收藏 0 原文

~没有更多了~