Sqlalchemy 简明教程

SQLAlchemy Core - Using Conjunctions

联接是 SQLAlchemy 模块中的函数，它实现了 SQL 表达式的 WHERE 子句中使用的关系运算符。运算符 AND、OR、NOT 等用于形式一个复合表达式，它将两个单独的逻辑表达式相结合。在 SELECT 语句中使用 AND 的一个简单示例如下 −

Conjunctions are functions in SQLAlchemy module that implement relational operators used in WHERE clause of SQL expressions. The operators AND, OR, NOT, etc., are used to form a compound expression combining two individual logical expressions. A simple example of using AND in SELECT statement is as follows −

SELECT * from EMPLOYEE WHERE salary>10000 AND age>30

SQLAlchemy 函数 and_(), or_() and not_() 分别实现 AND、OR 和 NOT 运算符。

SQLAlchemy functions and_(), or_() and not_() respectively implement AND, OR and NOT operators.

and_() function

它生成通过 AND 连接的表达式的联接。下面给出了一个示例以便更好地理解 −

It produces a conjunction of expressions joined by AND. An example is given below for better understanding −

from sqlalchemy import and_

print(
   and_(
      students.c.name == 'Ravi',
      students.c.id <3
   )
)

这转化为 −

This translates to −

students.name = :name_1 AND students.id < :id_1

要在 students 表上的 select() 构造中使用 and_()，请使用以下代码行 −

To use and_() in a select() construct on a students table, use the following line of code −

stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))

将构建以下性质的 SELECT 语句 −

SELECT statement of the following nature will be constructed −

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1

显示以上 SELECT 查询的输出的完整代码如下 −

The complete code that displays output of the above SELECT query is as follows −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

from sqlalchemy import and_, or_
stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))
result = conn.execute(stmt)
print (result.fetchall())

假设用前一个示例中使用的数据填充 students 表，则将选择以下行 −

Following row will be selected assuming that students table is populated with data used in previous example −

[(1, 'Ravi', 'Kapoor')]

or_() function

它生成通过 OR 连接的表达式的联接。我们将使用 or_() 替换以上示例中的 stmt 对象

It produces conjunction of expressions joined by OR. We shall replace the stmt object in the above example with the following one using or_()

stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3))

它将有效地等效于以下 SELECT 查询 −

Which will be effectively equivalent to following SELECT query −

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.name = :name_1
OR students.id < :id_1

一旦你进行了替换并运行上述代码，结果将是符合 OR 条件的两行 −

Once you make the substitution and run the above code, the result will be two rows falling in the OR condition −

[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]

asc() function

它生成一个 ORDER BY 升序子句。这个函数将列作为参数，以应用函数。

It produces an ascending ORDER BY clause. The function takes the column to apply the function as a parameter.

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))

以下语句实现了 SQL 表达式 −

The statement implements following SQL expression −

SELECT students.id,
   students.name,
   students.lastname
FROM students
ORDER BY students.name ASC

以下代码列出了 students 表中的所有记录，按 name 列升序排序 −

Following code lists out all records in students table in ascending order of name column −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))
result = conn.execute(stmt)

for row in result:
   print (row)

上述代码生成了以下输出 −

Above code produces following output −

(4, 'Abdul', 'Sattar')
(3, 'Komal', 'Bhandari')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')

desc() function

类似地，desc() 函数生成 ORDER BY 降序子句，如下所示 −

Similarly desc() function produces descending ORDER BY clause as follows −

from sqlalchemy import desc
stmt = select([students]).order_by(desc(students.c.lastname))

等价的 SQL 表达式是 −

The equivalent SQL expression is −

SELECT students.id,
   students.name,
   students.lastname
FROM students
ORDER BY students.lastname DESC

并且上述代码行的输出是 −

And the output for the above lines of code is −

(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')
(3, 'Komal', 'Bhandari')

between() function

它生成一个 BETWEEN 谓词子句。这通常用来验证特定列的值是否介于某个范围内。例如，以下代码选择 id 列介于 2 和 4 之间的行 −

It produces a BETWEEN predicate clause. This is generally used to validate if value of a certain column falls between a range. For example, following code selects rows for which id column is between 2 and 4 −

from sqlalchemy import between
stmt = select([students]).where(between(students.c.id,2,4))
print (stmt)

最终 SQL 表达式如下 −

The resulting SQL expression resembles −

SELECT students.id,
   students.name,
   students.lastname
FROM students
WHERE students.id
BETWEEN :id_1 AND :id_2

并且结果如下 −

and the result is as follows −

(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')