Sqlalchemy 简明教程

SQLAlchemy ORM - Textual SQL

早些时候使用 text() 函数的文本 SQL 已从 SQLAlchemy 的核心表达式语言的角度进行了说明。现在我们将从 ORM 的角度对此进行讨论。

Earlier, textual SQL using text() function has been explained from the perspective of core expression language of SQLAlchemy. Now we shall discuss it from ORM point of view.

可以通过指定对 text() 结构的使用灵活地将文字字符串与查询对象一起使用。大多数适用的方法都会接受它。例如，filter() 和 order_by()。

Literal strings can be used flexibly with Query object by specifying their use with the text() construct. Most applicable methods accept it. For example, filter() and order_by().

在下面给出的示例中，filter() 方法将字符串“id<3”转换成 WHERE id<3

In the example given below, the filter() method translates the string “id<3” to the WHERE id<3

from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
   print(cust.name)

生成的原始 SQL 表达式显示了 filter 向 WHERE 子句的转换，其中代码如下所示 −

The raw SQL expression generated shows conversion of filter to WHERE clause with the code illustrated below −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE id<3

从 Customers 表中的示例数据，将选择两行并将 name 列打印如下 −

From our sample data in Customers table, two rows will be selected and name column will be printed as follows −

Ravi Kumar
Komal Pande

要使用基于字符串的 SQL 指定绑定参数，请使用冒号，并且要指定值，请使用 params() 方法。

To specify bind parameters with string-based SQL, use a colon,and to specify the values, use the params() method.

cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()

Python 控制台上显示的有效 SQL 如下 −

The effective SQL displayed on Python console will be as given below −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE id = ?

要使用完全基于字符串的语句, 可以将表示完整语句的 text() 构造传给 from_statement()。

To use an entirely string-based statement, a text() construct representing a complete statement can be passed to from_statement().

session.query(Customers).from_statement(text("SELECT * FROM customers")).all()

以上代码的结果将是一个基本的 SELECT 语句，如下所示：

The result of above code will be a basic SELECT statement as given below −

SELECT * FROM customers

很明显，将选择 customers 表中的所有记录。

Obviously, all records in customers table will be selected.

text() 结构允许我们将其文本 SQL 与核心或 ORM 映射列表达式按位置链接。我们可以通过将列表达式作为 TextClause.columns() 方法的位置参数来实现这一点。

The text() construct allows us to link its textual SQL to Core or ORM-mapped column expressions positionally. We can achieve this by passing column expressions as positional arguments to the TextClause.columns() method.

stmt = text("SELECT name, id, name, address, email FROM customers")
stmt = stmt.columns(Customers.id, Customers.name)
session.query(Customers.id, Customers.name).from_statement(stmt).all()

即使 SQLite 引擎执行 text() 方法中显示的所有列生成的以下表达式，也将选择所有行的 id 和 name 列：

The id and name columns of all rows will be selected even though the SQLite engine executes following expression generated by above code shows all columns in text() method −

SELECT name, id, name, address, email FROM customers