我想知道是否有一种方法可以用 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)
请您参考如下方法:
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.
您可能会找到 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。
