Sqlalchemy 简明教程

SQLAlchemy Core - Selecting Rows

在本章中，我们将讨论表对象中选择行的概念。

In this chapter, we will discuss about the concept of selecting rows in the table object.

表对象的 select() 方法使我们能够 construct SELECT expression 。

The select() method of table object enables us to construct SELECT expression.

s = students.select()

select 对象转换成 SELECT query by str(s) function ，如下所示：

The select object translates to SELECT query by str(s) function as shown below −

'SELECT students.id, students.name, students.lastname FROM students'

我们可以使用此 select 对象作为连接对象的 execute() 方法的参数，如下面的代码所示：

We can use this select object as a parameter to execute() method of connection object as shown in the code below −

result = conn.execute(s)

当执行上述语句时，Python 外壳将显示以下等效 SQL 表达式：

When the above statement is executed, Python shell echoes following equivalent SQL expression −

SELECT students.id, students.name, students.lastname
FROM students

结果变量相当于 DBAPI 中光标。我们现在可以使用 fetchone() method 提取记录。

The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method.

row = result.fetchone()

在下表中，所有选定的行都可以通过 for loop 打印出来，如下所示：

All selected rows in the table can be printed by a for loop as given below −

for row in result:
   print (row)

从学生表中打印所有行的完整代码如下所示：

The complete code to print all rows from students table is shown below −

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

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

s = students.select()
conn = engine.connect()
result = conn.execute(s)

for row in result:
   print (row)

在 Python shell 中显示的输出如下：

The output shown in Python shell is as follows −

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

SELECT 查询的 WHERE 子句可通过使用 Select.where() 应用。例如，如果要显示 id >2 的行

The WHERE clause of SELECT query can be applied by using Select.where(). For example, if we want to display rows with id >2

s = students.select().where(students.c.id>2)
result = conn.execute(s)

for row in result:
   print (row)

这里 c attribute is an alias for column 。将在 shell 上显示以下输出：

Here c attribute is an alias for column. Following output will be displayed on the shell −

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

这里，我们需要注意，select 对象也可以在 sqlalchemy.sql 模块中通过 select() 函数获取。select() 函数需要 table 对象作为参数。

Here, we have to note that select object can also be obtained by select() function in sqlalchemy.sql module. The select() function requires the table object as argument.

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)