Sqlalchemy 简明教程

SQLAlchemy Core - Using Textual SQL

对于已知 SQL 的情况且无需语句支持动态特征的情况，SQLAlchemy 允许你仅使用字符串。text() 构造被用于编写传递到数据库的大致没有修改的文本语句。

SQLAlchemy lets you just use strings, for those cases when the SQL is already known and there isn’t a strong need for the statement to support dynamic features. The text() construct is used to compose a textual statement that is passed to the database mostly unchanged.

它构造一个新的 TextClause ，表示直接作为文本 SQL 字符串，如下面的代码所示：

It constructs a new TextClause, representing a textual SQL string directly as shown in the below code −

from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)

text() 相较于简单字符串提供的优势包括：

The advantages text() provides over a plain string are −

  1. backend-neutral support for bind parameters

  2. per-statement execution options

  3. result-column typing behaviour

text() 函数需要名结肠号格式的绑定参数。它们与数据库后端无关。要向参数发送值，我们以附加参数的形式将它们传递到 execute() 方法中。

The text()function requires Bound parameters in the named colon format. They are consistent regardless of database backend. To send values in for the parameters, we pass them into the execute() method as additional arguments.

以下示例在文本 SQL 中使用了绑定参数：

The following example uses bound parameters in textual SQL −

from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between :x and :y")
conn.execute(s, x = 'A', y = 'L').fetchall()

text() 函数构造 SQL 表达式，如下所示：

The text() function constructs SQL expression as follows −

select students.name, students.lastname from students where students.name between ? and ?

x = ’A’ 和 y = ’L’ 的值作为参数传递。结果是名字在“A”和“L”之间的行列表

The values of x = ’A’ and y = ’L’ are passed as parameters. Result is a list of rows with names between ‘A’ and ‘L’ −

[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]

text() 构造使用 TextClause.bindparams() 方法支持预先建立的边界值。也可以按以下方式明确设置参数的类型 -

The text() construct supports pre-established bound values using the TextClause.bindparams() method. The parameters can also be explicitly typed as follows −

stmt = text("SELECT * FROM students WHERE students.name BETWEEN :x AND :y")

stmt = stmt.bindparams(
   bindparam("x", type_= String),
   bindparam("y", type_= String)
)

result = conn.execute(stmt, {"x": "A", "y": "L"})

The text() function also be produces fragments of SQL within a select() object that
accepts text() objects as an arguments. The “geometry” of the statement is provided by
select() construct , and the textual content by text() construct. We can build a statement
without the need to refer to any pre-established Table metadata.

from sqlalchemy.sql import select
s = select([text("students.name, students.lastname from students")]).where(text("students.name between :x and :y"))
conn.execute(s, x = 'A', y = 'L').fetchall()

您还可以使用 and_() 函数组合在 WHERE 子句中创建的多个条件，该子句是在 text() 函数的帮助下创建的。

You can also use and_() function to combine multiple conditions in WHERE clause created with the help of text() function.

from sqlalchemy import and_
from sqlalchemy.sql import select
s = select([text("* from students")]) \
.where(
   and_(
      text("students.name between :x and :y"),
      text("students.id>2")
   )
)
conn.execute(s, x = 'A', y = 'L').fetchall()

上面的代码提取了 id 大于 2 的姓名介于“A”和“L”之间的行。代码的输出如下 -

Above code fetches rows with names between “A” and “L” with id greater than 2. The output of the code is given below −

[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]