阿里云服务器ECS    
弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新 [咨询更多]
阿里云存储OSS
简单易用、多重冗余、数据备份高可靠、多层次安全防护安全性更强、低成本 [咨询更多]
阿里云数据库RDS
稳定可靠、可弹性伸缩、更拥有容灾、备份、恢复、监控、迁移等方面的全套解决方案 [咨询更多]
阿里云安全产品
DDoS高防IP、web应用防火墙、安骑士、sll证书、态势感知众多阿里云安全产品热销中 [咨询更多]
阿里云折扣优惠    
云服务器ECS、数据库、负载均衡等产品新购、续费、升级联系客服获取更多专属折扣 [咨询更多]
查询数据
2020-9-7    点击量:
查询数据:1 ResultProxy;2 控制查询中的列数;3 排序;4 限制返回结果集的条数;5 内置SQL函数和标签;6 过滤;7 ClauseElement;8 运算符;9 布尔运算符;10 连接词。构建查询时,要用到 select 函数,它类似于标准 SQL SELECT 语句。首先从 cookies 表获取所有记录(见示例 2-6)。

示例 2-6 简单的 select 函数

from sqlalchemy.sql import select
s = select([cookies]) 
rp = connection.execute(s)
results = rp.fetchall() 

❶ 请记住,可以使用 str(s) 查看数据库看到的 SQL 语句,本例中是 SELECT cookies.cookie_id, cookies.cookie_name, cookies.cookie_recipe_url, cookies.cookie_sku, cookies.quantity, cookies.unit_cost FROM cookies

❷ 这让 rpResultProxy)返回所有行。

results 变量现在包含一个列表,里面有 cookies 表中的所有记录:

[(1, u'chocolate chip', u'http://some.aweso.me/cookie/recipe.html', u'CC01',
  12, Decimal('0.50')),
 (2, u'dark chocolate chip', u'http://some.aweso.me/cookie/recipe_dark.html',
  u'CC02', 1, Decimal('0.75')),
 (3, u'peanut butter', u'http://some.aweso.me/cookie/peanut.html', u'PB01',
  24, Decimal('0.25')),
 (4, u'oatmeal raisin', u'http://some.okay.me/cookie/raisin.html', u'EWW01',
  100, Decimal('1.00'))]

在前面的示例中,我传递了一个包含 cookies 表的列表。select 方法需要一个列列表来进行选择,但为了方便起见,它还可以接受 Table 对象,并选择表中的所有列。也可以使用 Table 对象的 select 方法来实现这一点,如示例 2-7 所示。但是,我更喜欢示例 2-6 的写法。

示例 2-7 简单的 select() 方法

from sqlalchemy.sql import select
s = cookies.select()
rp = connection.execute(s)
results = rp.fetchall()

在深入学习查询之前,需要进一步了解一下 ResultProxy 对象。

1 ResultProxy

ResultProxy 是 DBAPI 游标对象的包装器,其主要目标是让语句返回的结果更容易使用和操作。比如,ResultProxy 允许使用索引、名称或 Column 对象进行访问,从而简化了对查询结果的处理。示例 2-8 演示了这三种方法。能够熟练地使用这三个方法来获取所需的列数据非常重要。

示例 2-8 使用 ResultProxy 处理行

first_row = results[0] 
first_row[1] 
first_row.cookie_name 
first_row[cookies.c.cookie_name] 

❶ 获取 ResultProxy 的第一行。

❷ 通过索引访问列。

❸ 通过名称访问列。

❹ 通过 Column 对象访问列。

上面三条语句的执行结果都是 'chocolate chip',它们引用的是 results 变量的第一条记录中完全相同的数据元素。这种访问灵活性只是 ResultProxy 强大功能的一部分。还可以把 ResultProxy 用作可迭代对象,对返回的每条记录执行一个操作,并且无须创建另一个变量来保存结果。例如,我们可能希望打印数据库中每种 cookie 的名称(见示例 2-9)。

示例 2-9 迭代 ResultProxy

rp = connection.execute(s) 
for record in rp:
    print(record.cookie_name)

➊ 这里重用了前面的 select 语句。返回结果如下:

chocolate chip
dark chocolate chip
peanut butter
oatmeal raisin

除了把 ResultProxy 用作可迭代对象和调用 fetchall() 方法之外,还有其他许多通过 ResultProxy 访问数据的方法。事实上,2.1 节中的所有 result 变量都是 ResultProxys。我们在该节中使用的 rowcount() 和 inserted_primary_key() 方法只是从 ResultProxy 获取信息的另外两种方法。还可以使用如下方法来获取结果。

first()

  若有记录,则返回第一个记录并关闭连接。

fetchone()

  返回一行,并保持光标为打开状态,以便你做更多获取调用。

scalar()

  如果查询结果是包含一个列的单条记录,则返回单个值。

如果想查看结果集中的多个列,可以使用 keys() 方法来获得列名列表。在本章的其余部分中,我们将使用 firstscalarfetchone 和 fetchall 方法以及 ResultProxy 可迭代对象。

最佳实践

在编写生产代码时,应该遵循如下指导方针。

  • 获取单条记录时,要多用 first 方法,尽量不要用 fetchone 和 scalar 方法,因为对程序员来说,first 方法更加清晰。
  • 尽量使用可迭代对象 ResultProxy,而不要用 fetchall 和 fetchone 方法,因为前者的内存效率更高,而且我们往往一次只对一条记录进行操作。
  • 避免使用 fetchone 方法,因为如果不小心,它会一直让连接处于打开状态。
  • 谨慎使用 scalar 方法,因为如果查询返回多行多列,就会引发错误,多行多行在测试过程中经常会丢失。

在前面的示例中,我们每次查询数据库时都会得到各条记录的所有列。通常我们只需要使用这些列中的一部分。如果这些额外列中的数据量很大,就会导致应用程序运行速度变慢,消耗的内存远超预期。尽管 SQLAlchemy 不会向查询或 ResultProxys 添加大量开销,但是,如果查询占用了太多内存,首先要考虑从查询返回的数据是否有问题。接下来看看如何对查询返回的列数进行限制。

2 控制查询中的列数

为了限制查询返回的列数,需要以列表的形式把要查询的列传递给 select() 方法。例如,你想运行一个查询,该查询只返回 cookie 的名称和数量,如示例 2-10 所示。

示例 2-10 只包含 cookie_name 和 quantity

s = select([cookies.c.cookie_name, cookies.c.quantity])
rp = connection.execute(s)
print(rp.keys()) 
result = rp.first() 

❶ 返回所选列的列表,在本例中是 ['cookie_name','quantity'](这只用来演示,不是必需的)。

❷ 请注意,这条语句只返回第一个结果。

结果如下:

(u'chocolate chip', 12),

我们已经可以构建简单的 select 语句了,接下来看看还可以做些什么来改变 select 语句返回结果的方式。首先学习如何改变返回结果的顺序。

3 排序

如果查看示例 2-10 的所有结果,而不仅仅是第一条记录,你会发现数据并不是按照特定顺序排列的。如果希望返回的列表有特定的顺序,可以使用 order_by() 语句,如示例 2-11 所示。示例中,我们希望结果按照现有 cookie 的数量进行排序。

示例 2-11 按 quantity 进行升序排列

s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
rp = connection.execute(s)
for cookie in rp:
    print('{} - {}'.format(cookie.quantity, cookie.cookie_name))

结果如下:

1 - dark chocolate chip
12 - chocolate chip
24 - peanut butter
100 - oatmeal raisin

我们先把 select 语句保存到变量 s 中,而后向变量 s 中添加 order_by 语句,再将其重新赋给 s 变量。这个示例演示了如何以生成式或分步方式编写语句。也可以把 select 和 order_by 语句放在一行中,如下所示:

s = select([...]).order_by(...)

然而,当 select 中有完整的列列表,order_by 语句中有 order 列时,它超过了 Python 每行 79 个字符的限制(这是在 PEP8 中建立的)。通过使用生成类型语句,可以保持不超出该限制。在本书中,我们将看到一些例子,其中生成类型语句可以带来额外的好处,例如有条件地向语句中添加内容。现在,尝试按照每行 79 个字符的限制分割语句,这将有助于提升代码的可读性。

如果你想按倒序或降序排列,可使用 desc() 语句。desc() 函数的作用是按降序包装你要排序的特定列,如示例 2-12 所示。

示例 2-12 按照 quantity 进行降序排列

from sqlalchemy import desc
s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(desc(cookies.c.quantity)) 

➊ 请注意,这一行中,我们使用 desc() 函数对 cookies.c.quantity 列进行了包装。

 desc() 函数还可以作为 Column 对象(比如 cookie.c.quantity.desc())的方法进行调用。但是,如果在长语句中这样使用,可能会造成阅读困难,所以我总是把 desc() 用作函数。

如果应用程序只需要返回结果的一部分,可以对返回的结果数量进行限制。

4 限制返回结果集的条数

在前面的示例中,我们使用 first() 或 fetchone() 方法仅获取一行。虽然 ResultProxy 提供了我们请求的那行,但查询实际运行时会访问所有结果,而不仅仅是单个记录。如果想对查询进行限制,可以使用 limit() 函数让 limit 语句成为查询的一部分。比如,你的时间只够制作两批 cookie,你想知道应该制作哪两种 cookie,那么你可以使用前面的有序查询,并添加 limit语句来返回最需要补充的两种 cookie(见示例 2-13)。

示例 2-13 两种库存量最少的 cookie

s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
s = s.limit(2)
rp = connection.execute(s)
print([result.cookie_name for result in rp]) 

➊ 这里,我们在列表中使用了 ResultsProxy 的可迭代功能。

结果如下:

[u'dark chocolate chip', u'chocolate chip']

现在,你已经知道需要烤哪两种 cookie 了,你可能还想知道当前库存中还有多少 cookie。

许多数据库都包含 SQL 函数,这些函数的设计目的是让某些操作可以直接在数据库服务器上使用,比如 SUM。接下来了解一下如何使用 SQL 函数。

5 内置SQL函数和标签

SQLAlchemy 还可以利用后端数据库中的 SQL 函数。两个非常常用的数据库函数是 SUM() 和 COUNT()。要使用这两个函数,需要导入 sqlalchemy.sql.func 模块。这些函数被包装在它们操作的列上。因此,要获得 cookie 的总数,可以使用示例 2-14。

示例 2-14 计算 cookie 的总数

from sqlalchemy.sql import func
s = select([func.sum(cookies.c.quantity)])
rp = connection.execute(s)
print(rp.scalar()) 

➊ 请注意,这里使用了 scalar(),它只返回第一个记录最左边的列。

结果如下:

137

 我总是导入 func 模块,因为直接导入 sum 可能会引起问题,而且还容易和 Python 内置的 sum 函数混淆。

接下来使用 count 函数查看一下 cookies 表中有几种 cookie(见示例 2-15)。

示例 2-15 统计库存中有几种 cookie

s = select([func.count(cookies.c.cookie_name)])
rp = connection.execute(s)
record = rp.first()
print(record.keys()) 
print(record.count_1) 

❶ 显示 ResultProxy 中的列。

❷ 自动生成列名,一般格式为:_

结果如下:

[u'count_1']
4

这个列名既烦人又麻烦。另外,如果查询中有多个统计,那我们必须知道它们在语句中出现的次数,并将其合并到列名,因此第四个 count() 函数将是 count_4。命名应该清晰、明确,特别是当周围有其他 Python 代码时。不过,值得庆幸的是,SQLAlchemy 提供了 label() 函数来解决这个问题。示例 2-16 执行的查询与示例 2-15 一样,但它通过调用 label() 函数为我们访问的列起了一个更有用的名字。

示例 2-16 使用 lable() 进行重命名

s = select([func.count(cookies.c.cookie_name).label('inventory_count')]) 
rp = connection.execute(s)
record = rp.first()
print(record.keys())
print(record.inventory_count)

➊ 请注意,只在要更改的列对象上调用 label() 函数即可。

结果如下:

[u'inventory_count']
4

我们已经学习了如何限制从数据库返回的列数或行数。接下来学习如何根据指定的条件对查询数据进行过滤。

6 过滤

对查询进行过滤是通过添加 where() 语句来完成的,和在 SQL 中一样。典型的 where() 子句包含一个列、一个运算符和一个值或列。可以把多个 where() 子句接在一起使用,功能就像传统 SQL 语句中的 AND 一样。在示例 2-17 中,我们将查找名为“chocolate chip”的 cookie。

示例 2-17 使用 cookie_name 进行过滤

s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')
rp = connection.execute(s)
record = rp.first()
print(record.items()) 

➊ 这里我调用了行对象的 items() 方法,该方法返回一个包含列名和列值的列表。

结果如下:

[
    (u'cookie_id', 1),
    (u'cookie_name', u'chocolate chip'),
    (u'cookie_recipe_url', u'http://some.aweso.me/cookie/recipe.html'),
    (u'cookie_sku', u'CC01'),
    (u'quantity', 12),
    (u'unit_cost', Decimal('0.50'))
]

还可以使用 where() 语句来查找所有包含“chocolate”这个词的 cookie 名(见示例 2-18)。

示例 2-18 查找包含 chocolate 的 cookie 名

s = select([cookies]).where(cookies.c.cookie_name.like('%chocolate%'))
rp = connection.execute(s)
for record in rp.fetchall():
    print(record.cookie_name)

结果如下:

chocolate chip
dark chocolate chip

在示例 2-18 的 where() 语句中,我们将 cookies.c.cookie_name 列用作一种 ClauseElement 来过滤结果。我们应该花一些时间了解一下 ClauseElement 及其提供的其他功能。

7 ClauseElement

ClauseElement 是在子句中使用的实体,一般是表中的列。不过,与列不同的是,ClauseElement 拥有许多额外的功能。在示例 2-18 中,我们调用了 ClauseElement 的 like() 方法。此外还有许多其他方法可供选用,如表 2-1 所示。这些方法的结构和标准 SQL 语句类似。你会在本书中找到各种各样的例子。

表2-1:ClauseElement方法

方法

用途

between(cleft, cright)

查找在 cleft 和 cright 之间的列

concat(column_two)

连接列

distinct()

查找列的唯一值

in_([list])

查找列在列表中的位置

is_(None)

查找列 None 的位置(通常用于检查 Null 和 None)

contains(string)

查找包含 string 的列(区分大小写)

endswith(string)

查找以 string 结尾的列(区分大小写)

like(string)

查找与 string 匹配的列(区分大小写)

startswith(string)

查找以 string 开头的列(区分大小写)

ilike(string)

查找与 string 匹配的列(不区分大小写)

 这些方法也存在相反的版本,例如 notlike 和 notin_()not<方法> 这种命名约定的唯一例外是不带下划线的 isnot() 方法。

除了使用表 2-1 中列出的方法外,还可以在 where 子句中使用运算符。大多数运算符的工作方式和你预想的一样。接下来详细地讲解一下运算符,它们之间存在一些差异。

8 运算符

到目前为止,我们用来过滤数据的方法有两种:一是利用列是否等于某个值,二是使用 ClauseElement 的方法,比如 like()。然而,还可以使用许多常见的运算符来过滤数据。SQLAlchemy 对大多数标准 Python 运算符做了重载,包括所有标准的比较运算符(==!=<< code="">、><=< code="">、>=),它们的功能和在 Python 语句中完全一样。在与 None 比较时,== 运算符被重载为 IS NULL语句。算术运算符(\+-*/ 和 %)还可以用来对独立于数据库的字符串做连接处理,如示例 2-19 所示。

示例 2-19 使用 \+ 连接字符串

s = select([cookies.c.cookie_name, 'SKU-' + cookies.c.cookie_sku])
for row in connection.execute(s):
    print(row)

结果如下:

(u'chocolate chip', u'SKU-CC01')
(u'dark chocolate chip', u'SKU-CC02')
(u'peanut butter', u'SKU-PB01')
(u'oatmeal raisin', u'SKU-EWW01')

运算符的另一个常见用法是根据多个列来计算值。在处理财务数据或统计数据的应用程序和报表中,你经常要这样做。示例 2-20 是计算库存价值的一个例子。

示例 2-20 计算各种 cookie 的库存价值

from sqlalchemy import cast 
s = select([cookies.c.cookie_name,
          cast((cookies.c.quantity * cookies.c.unit_cost),
               Numeric(12,2)).label('inv_cost')]) 
for row in connection.execute(s):
    print('{} - {}'.format(row.cookie_name, row.inv_cost))

❶ Cast() 是另一个允许做类型转换的函数。本例中,我们要获取 6.0000000000 这样的结果,因此需要进行强制类型转换,使其看起来像货币。在 Python 中,还可以使用 print('{} - {:.2f}'.format(row.cookie_name, row.inv_cost)) 完成相同的任务。

❷ 请注意,这里再次使用 label() 函数对列进行重命名。如果不进行重命名,列就会被命名为 anon_1,因为操作本身不会产生名字。

结果如下:

chocolate chip - 6.00
dark chocolate chip - 0.75
peanut butter - 6.00
oatmeal raisin - 100.00

2.2.9 布尔运算符

SQLAlchemy 还支持 SQL 布尔运算符 AND、OR 和 NOT,它们用位运算符(&| 和 ~)来表示。受 Python 运算符优先级规则的影响,在使用 AND、OR 和 NOT 重载运算符时一定要特别小心。比如,& 比 << code=""> 优先级高,所以当你写 A < B & C < D 时,你想得到的是 (A < B) &(C < D),而实际得到的却是 A < (B&C) < D。请尽量使用连接词(conjunction),不要用这些重载运算符,因为连接词会让你的代码更清晰易懂。

通常,我们希望用包含和排除的方式把多个 where() 子句链接在一起,这可以通过使用连接词来完成。

10 连接词

为了实现某种期望的效果,我们既可以把多个 where() 子句链接在一起,也可以使用连接词来实现,而且使用连接词的可读性更好、功能性更强。SQLAlchemy 中的连接词是 and_()or_() 和 not_()。如果想获得价格低于某个数且数量超过指定值的 cookie 列表,可以使用示例 2-21 中的代码。

示例 2-21 使用 and() 连接词

from sqlalchemy import and_, or_, not_
s = select([cookies]).where(
    and_(
        cookies.c.quantity > 23,
        cookies.c.unit_cost << span=""> 0.40
    )
)
for row in connection.execute(s):
    print(row.cookie_name)

or_() 函数的作用与 and_() 函数正好相反,只要记录满足其中一个条件,就会被选出来。如果想搜索库存中数量在 10 到 50 之间或名字包含 chip 的 cookie,可以使用示例 2-22 中的代码。

示例 2-22 使用 or() 连接词

from sqlalchemy import and_, or_, not_
s = select([cookies]).where(
    or_(
        cookies.c.quantity.between(10, 50),
        cookies.c.cookie_name.contains('chip')
    )
)
for row in connection.execute(s):
    print(row.cookie_name)

结果如下:

chocolate chip
dark chocolate chip
peanut butte

not_() 函数的工作方式与其他连接词类似,用来选择那些与指定条件不匹配的记录。到这里,我们已经可以轻松地查询数据了,接下来该学习如何更新现有数据了。

联系客服免费领取更多阿里云产品新购、续费升级折扣,叠加官网活动折上折更优惠