Sqlalchemy 简明教程

SQLAlchemy Core - Using Aliases

SQL 中的别名对应于表的“重命名”版本或 SELECT 语句，该语句在您输入“SELECT * FROM table1 AS a”时随时发生。AS 为表创建了一个新名称。别名允许通过唯一名称引用任何表或子查询。

The alias in SQL corresponds to a “renamed” version of a table or SELECT statement, which occurs anytime you say “SELECT * FROM table1 AS a”. The AS creates a new name for the table. Aliases allow any table or subquery to be referenced by a unique name.

在表的场合，这允许在“FROM”子句中多次命名相同的表。它为该语句表示的列提供了一个父名称，允许相对于该名称对列进行引用。

In case of a table, this allows the same table to be named in the FROM clause multiple times. It provides a parent name for the columns represented by the statement, allowing them to be referenced relative to this name.

在 SQLAlchemy 中，可以使用 From Clause.alias() 方法将任何 Table 型、select() 型或其他可选择对象转换为别名，该方法生成 Alias 型。sqlalchemy.sql 模块中的 alias() 函数表示别名，该别名通常使用 AS 关键字应用于 SQL 语句中的任何表或子查询。

In SQLAlchemy, any Table, select() construct, or other selectable object can be turned into an alias using the From Clause.alias() method, which produces an Alias construct. The alias() function in sqlalchemy.sql module represents an alias, as typically applied to any table or sub-select within a SQL statement using the AS keyword.

from sqlalchemy.sql import alias
st = students.alias("a")

现在可以在 select() 型中使用此别名来引用 students 表 -

This alias can now be used in select() construct to refer to students table −

s = select([st]).where(st.c.id>2)

这转换成的 SQL 表达式如下 -

This translates to SQL expression as follows −

SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2

现在我们可以使用连接对象的 execute() 方法来执行此 SQL 查询。完整的代码如下 -

We can now execute this SQL query with the execute() method of connection object. The complete code is as follows −

from sqlalchemy.sql import alias, select
st = students.alias("a")
s = select([st]).where(st.c.id > 2)
conn.execute(s).fetchall()

在执行上述代码行时，它会生成以下输出 -

When above line of code is executed, it generates the following output −

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