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
。
? 这让 rp
(ResultProxy
)返回所有行。
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
对象。
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()
方法来获得列名列表。在本章的其余部分中,我们将使用 first
、scalar
、fetchone
和 fetchall
方法以及 ResultProxy
可迭代对象。
最佳实践
在编写生产代码时,应该遵循如下指导方针。
- 获取单条记录时,要多用 first 方法,尽量不要用
fetchone
和scalar
方法,因为对程序员来说,first
方法更加清晰。- 尽量使用可迭代对象
ResultProxy
,而不要用fetchall
和fetchone
方法,因为前者的内存效率更高,而且我们往往一次只对一条记录进行操作。- 避免使用
fetchone
方法,因为如果不小心,它会一直让连接处于打开状态。- 谨慎使用
scalar
方法,因为如果查询返回多行多列,就会引发错误,多行多行在测试过程中经常会丢失。
在前面的示例中,我们每次查询数据库时都会得到各条记录的所有列。通常我们只需要使用这些列中的一部分。如果这些额外列中的数据量很大,就会导致应用程序运行速度变慢,消耗的内存远超预期。尽管 SQLAlchemy 不会向查询或 ResultProxys
添加大量开销,但是,如果查询占用了太多内存,首先要考虑从查询返回的数据是否有问题。接下来看看如何对查询返回的列数进行限制。
为了限制查询返回的列数,需要以列表的形式把要查询的列传递给 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
语句返回结果的方式。首先学习如何改变返回结果的顺序。
如果查看示例 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()
用作函数。
如果应用程序只需要返回结果的一部分,可以对返回的结果数量进行限制。
在前面的示例中,我们使用 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 函数。
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
我们已经学习了如何限制从数据库返回的列数或行数。接下来学习如何根据指定的条件对查询数据进行过滤。
对查询进行过滤是通过添加 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
及其提供的其他功能。
ClauseElement
是在子句中使用的实体,一般是表中的列。不过,与列不同的是,ClauseElement
拥有许多额外的功能。在示例 2-18 中,我们调用了 ClauseElement
的 like()
方法。此外还有许多其他方法可供选用,如表 2-1 所示。这些方法的结构和标准 SQL 语句类似。你会在本书中找到各种各样的例子。
表2-1:ClauseElement
方法
方法 | 用途 |
---|---|
| 查找在 |
| 连接列 |
| 查找列的唯一值 |
| 查找列在列表中的位置 |
| 查找列 |
| 查找包含 |
| 查找以 |
| 查找与 |
| 查找以 |
| 查找与 |
这些方法也存在相反的版本,例如
notlike
和notin_()
。not<方法>
这种命名约定的唯一例外是不带下划线的isnot()
方法。
除了使用表 2-1 中列出的方法外,还可以在 where
子句中使用运算符。大多数运算符的工作方式和你预想的一样。接下来详细地讲解一下运算符,它们之间存在一些差异。
到目前为止,我们用来过滤数据的方法有两种:一是利用列是否等于某个值,二是使用 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
SQLAlchemy 还支持 SQL 布尔运算符 AND、OR 和 NOT,它们用位运算符(&
、|
和 ~
)来表示。受 Python 运算符优先级规则的影响,在使用 AND、OR 和 NOT 重载运算符时一定要特别小心。比如,&
比 << code=""> 优先级高,所以当你写
A < B & C < D
时,你想得到的是 (A < B) &(C < D)
,而实际得到的却是 A < (B&C) < D
。请尽量使用连接词(conjunction),不要用这些重载运算符,因为连接词会让你的代码更清晰易懂。
通常,我们希望用包含和排除的方式把多个 where()
子句链接在一起,这可以通过使用连接词来完成。
为了实现某种期望的效果,我们既可以把多个 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_()
函数的工作方式与其他连接词类似,用来选择那些与指定条件不匹配的记录。到这里,我们已经可以轻松地查询数据了,接下来该学习如何更新现有数据了。