由于SQLAlchemy 中文资料比较少，所以根据官网给的tutorial外加其他大佬写的中文资料整合以后准备写一个SQLAlchemy 系列的基础入门教程。本系列可能会夹杂一些个人对于python 、SQLAlchemy 以及ORM的理解，如有出错部分，请指正我。

版本信息:

• SQLAlchemy 1.2.15

• Python 3.6+

• Mac OS 10.14

• DB基于SQLite

前序文章Python SQLAlchemy ORM教程(2)讲了怎么在你的数据库中查询内容，但是有些查询往往不尽然人意，或者效率低下。这个章节，我们主要来讲下，SQLAlchemy为你提供的快捷查询接口和高度自由化的复杂查询

Returning Lists and Saclars

在Query对象中提供了一些方法帮你快速整理已经被加载的数据。需要明确的知道

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n17" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)</pre>

返回的是对象而不是我们平常认知的list。尽管query对象可以迭代但是不代表他是list，list是一个可迭代对象但是不是迭代器，而query 对象是一个迭代器。关于迭代器和迭代对象的区别，我觉得一张图可以表达清楚，不再细讲

image

我们看下query究竟是一个list还是一个query对象

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n21" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)

type(query)
<class 'sqlalchemy.orm.query.Query'></pre>

SQLAlchemy提供了一个all()方法让你可以返回list

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n23" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> all_res=query.all()

type(all_res)
<class 'list'>
all_res
[<User(name='ed', fullname='Ed Jones', password='edspassword'>, <User(name='fred', fullname='Fred Flinstone', password='blah'>]</pre>

SQLAlchemy还提供了first()来把你快速的取出第一个元素

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n25" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> query.first()
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')></pre>

one() 会获取所有的数据，然后看是不是只有一个数据，如果有多于一个数据，将会返回MultipleResultsFound这个错误：

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n27" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> user = query.one()
Traceback (most recent call last):
...
MultipleResultsFound: Multiple rows were found for one()</pre>

如果没有任何数据，则会返回NoResultFound这个错误:

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n29" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> user = query.filter(User.id == 99).one()
Traceback (most recent call last):
...
NoResultFound: No row was found for one()</pre>

这个one()可以在你设计RESTful的时候提供高效的解决方式，比如当你NoResultFound的时候你可以直接返回404 当有多个结果被找到的时候你可以返回application error来防止用户获取他不改获取的数据，比如当用户请求自己主页数据的时候，应该有且仅有一行数据，当返回了多行数据的时候，就可以知道，程序员又写bug了，使用one()可以有效防止这种情况发生。

题外话:one_or_none()这个函数跟one()很像，但是one_or_none()并不会报错而是直接返回None，但是当数据多于一行的时候one_or_none()和one()一样会报错。

scalar() 会调用one()，他会返回一行数据

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" contenteditable="false" cid="n34" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> query = session.query(User.id).filter(User.name == 'ed').order_by(User.id)

query
<sqlalchemy.orm.query.Query object at 0x7fcce6caff98>
query.all()
[(1,)]
query.one()
(1,)
query.one()[0]
1
query.scalar()
1</pre>

Using Textual SQL

我不准备翻译官方的这个章节，因为我觉得作为新手来说，这个章节的自由度很高，但是操作难度也比较高，需要一定的数据库知识，如果有需要翻译的，麻烦在下面留言哦

Counting

计算数据数量，算是最普遍的需求了吧，SQLAlchemy肯定会提供这个方法的，这个方法叫做count()它是query对象中的一个方法

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n39" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> session.query(User).filter(User.name.like('%ed')).count()
2</pre>

func

从SQL角度来说上面的count()仅仅是进行了最简单的数量统计，等价于SELECT count(*) FROM table。程序员的要求各种各样，作为一个现代的ORM工具，SQLAlchemy提供了更为精细化的查询，这个查询需要使用 func对象

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n42" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> from sqlalchemy import func

session.query(func.count(User.name), User.name).group_by(User.name).all()
[(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]

我们来分析一下上面的语句，可以看到SQLAlchemy根据你的要求查询了有User.name的数量

session.query(func.count(User.name)).all()
2018-12-16 03:33:54,115 INFO sqlalchemy.engine.base.Engine SELECT count(users.name) AS count_1
FROM users
2018-12-16 03:33:54,115 INFO sqlalchemy.engine.base.Engine ()
[(4,)]
session.query(func.count(User.name)).scalar()
2018-12-16 03:34:01,138 INFO sqlalchemy.engine.base.Engine SELECT count(users.name) AS count_1
FROM users
2018-12-16 03:34:01,139 INFO sqlalchemy.engine.base.Engine ()
4</pre>

对于更像SQL查询的表达方式，如下

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="python" contenteditable="false" cid="n44" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">>>> session.query(func.count('*')).select_from(User).scalar()
4</pre>

下一个章节将会将关于数据库关系的建立