python - 请求中的条件

Question

我想知道是否有一种方法可以用 python 编写的函数来过滤请求给出的结果。类似的东西:

SELECT id, name, path IF verifAccess(path)

在我的示例中，verifAcces 将是我编写的函数。如果路径可访问，则返回 True，否则返回 False。

谢谢。

这就是我需要过滤的请求:

def displayWaitingList(self):

    self.button_waiting.setEnabled(False)
    self.showing_waiting = True

    try:
        self.waiting_list
    except AttributeError:
        return

    self.query = QtSql.QSqlQuery()

    requete = "SELECT * FROM videos WHERE id IN ("

    for each_id in self.waiting_list:
        if self.waiting_list.index(each_id) != len(self.waiting_list) - 1:
            requete = requete + str(each_id) + ", "
        else:
            requete = requete + str(each_id) + ")"

    self.query.prepare(requete)
    self.query.exec_()

    self.modele.setQuery(self.query)

    self.proxy.setSourceModel(self.modele)
    self.tableau.setModel(self.proxy)

Answer 1

SQL 查询是在数据库服务器中执行的，所以如果你有支持 Python 的后端，你可以用 Python 编写存储过程和函数。

PostgreSQL 有 PL/Py :

Pure Python: All code, at first, is written in pure Python so that py-postgresql will work anywhere that you can install Python 3. Optimizations in C are made where needed, but are always optional.

Prepared Statements: Using the PG-API interface, protocol-level prepared statements may be created and used multiple times. db.prepare(sql)(*args)

COPY Support: Use the convenient COPY interface to directly copy data from one connection to another. No intermediate files or tricks are necessary.

Arrays and Composite Types: Arrays and composites are fully supported. Queries requesting them will returns objects that provide access to the elements within.

Quick Console: Get a Python console with a connection to PostgreSQL for quick tests and simple scripts.

source: http://python.projects.pgfoundry.org/

您可能会找到 Pony ORM很有意思。它允许使用纯 Python 而不是 SQL 查询数据库:

select(c for c in Customer if sum(c.orders.price) > 1000)

上述语句生成以下查询:

SELECT "c"."id"
FROM "Customer" "c"
  LEFT JOIN "Order" "order-1"
    ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"
HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000

[更新]

Okay, I'm going to have a look at it. Thanks. But nothing native ? – user1585507

native 是指“仅使用核心库”吗？不，没有。如果您不能使用 PL/Py，那么您最好的选择是像 SQLAlchemy 这样的 ORM(在 SQL 方面表现力很强)，或者像 Pony 这样的 ORM(在 Python 方面表现力更强)。两者都可以让您轻松地重用和组合查询。

如果您要让用户构建复杂的查询条件，并试图避免使用字符串插值和连接来编写 SQL 查询的痛苦，我建议 SQLAlchemy核心。

如果您的查询很简单，您只想尽可能避免 Python 和 SQL 之间的阻抗不匹配，那么请使用 Pony。