SQLAlchemy技术文档（中文版）（上）

来源：互联网发布：录制声音的软件编辑：程序博客网时间：2024/05/21 17:49

原文链接：http://www.cnblogs.com/iwangzc/p/4112078.html（感谢作者的分享）

1.版本检查

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy</span></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">sqlalchemy</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">__version__</span></span></span>

2.连接

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">create_engine</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">engine </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span><span style="margin: 0px; padding: 0px; line-height: 1.8;">create_engine(</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'sqlite:///:memory:'</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">,echo</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">True</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">)</span></span></span>

echo参数为True时，会显示每条执行的SQL语句，可以关闭。create_engine()返回一个Engine的实例，并且它表示通过数据库语法处理细节的核心接口，在这种情况下，数据库语法将会被解释称Python的类方法。

3.声明映像

当使用ORM【1】时，构造进程首先描述数据库的表，然后定义我们用来映射那些表的类。在现版本的SQLAlchemy中，这两个任务通常一起执行，通过使用Declarative方法，我们可以创建一些包含描述要被映射的实际数据库表的准则的映射类。

使用Declarative方法定义的映射类依据一个基类，这个基类是维系类和数据表关系的目录——我们所说的Declarative base class。在一个普通的模块入口中，应用通常只需要有一个base的实例。我们通过declarative_base()功能创建一个基类：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy.ext.declarative</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">declarative_base</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">Base <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> declarative_base()</span></span></span>

有了这个base，我们可以依据这个base定义任意数量的映射类。一个简单的user例子：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">Column, Integer, String</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">class </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">User</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">(Base):</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">__tablename__<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'users'</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">id</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span><span style="margin: 0px; padding: 0px; line-height: 1.8;">Column(Integer, primary_key</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">True</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">)</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span>Column(String)</span></span></span>

用Declarative构造的一个类至少需要一个__tablename__属性，一个主键行。

4.构造模式（项目中没用到）

5.创建映射类的实例

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">ed_user <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Ed Jones'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'edspassword'</span>)</span></span></span>

6.创建会话

现在我们已经准备毫和数据库开始会话了。ORM通过Session与数据库建立连接的。当应用第一次载入时，我们定义一个Session类（声明create_engine()的同时），这个Session类为新的Session对象提供工厂服务。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy.orm </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">sessionmaker</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">Session <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> sessionmaker(bind<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span>engine)</span></span></span>

这个定制的Session类会创建绑定到数据库的Session对象。如果需要和数据库建立连接，只需要实例化一个Session：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> Session()</span></span></span>

虽然上面的Session已经和数据库引擎Engine关联，但是还没有打开任何连接。当它第一次被使用时，就会从Engine维护的一个连接池中检索是否存在连接，如果存在便会保持连接知道我们提交所有更改并且/或者关闭session对象。

7.添加新对象（简略）

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">ed_user <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>, fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Ed Jones'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'edspassword'</span>)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>add(ed_user)</span></span></span>

至此，我们可以认为，新添加的这个对象实例仍在等待中；ed_user对象现在并不代表数据库中的一行数据。直到使用flush进程，Session才会让SQL保持连接。如果查询这条数据的话，所有等待信息会被第一时间刷新，查询结果也会立即发行。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>commit()</span></span></span>

通过commit()可以提交所有剩余的更改到数据库。

8.回滚

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>rollback()</span></span></span>

9.查询

通过Session的query()方法创建一个查询对象。这个函数的参数数量是可变的，参数可以是任何类或者是类的描述的集合。下面是一个迭代输出User类的例子：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span>instance <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>order_by(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id):</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print </span></span>instance<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name,instance<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>fullname</span></span></span>

Query也支持ORM描述作为参数。任何时候，多个类的实体或者是基于列的实体表达都可以作为query()函数的参数，返回类型是元组：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span>name, fullname <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name,User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>fullname): <br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /></span></span></span><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32); font-family: 'Liberation Mono', monospace; line-height: 1.5;">print </span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">name, fullname</span></span></span>

Query返回的元组被命名为KeyedTuple类的实例元组。并且可以把它当成一个普通的Python数据类操作。元组的名字就相当于属性的属性名，类的类名一样。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span>row <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User, User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all():<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32); line-height: 1.5;">print </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">row<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>User,</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">row<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Ed Jones'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'f8s7ccs'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span>ed</span></span></span>

label()不知道怎么解释，看下例子就明白了。相当于row.name

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span>row <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>label(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'name_label'</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all():<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span>(row<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name_label)</span></span></span></span></span></span>

aliased()我的理解是类的别名，如果有多个实体都要查询一个类，可以用aliased()

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(14, 132, 181);">sqlalchemy.orm </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">aliased</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">user_alias <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> aliased(User, name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'user_alias'</span>)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span>row <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in</span></span> session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(user_alias,user_alias<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all():<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32); line-height: 1.5;">print </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">row<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user_alias</span></span></span>

Query的基本操作包括LIMIT和OFFSET，使用Python数组切片和ORDERBY结合可以让操作变得很方便。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span>u <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>order_by(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id)[<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>:<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">3</span>]:<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Serif', serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">#</span></span>只查询第二条和第三条数据</span></span></span></span>

9.1使用关键字变量过滤查询结果，filter 和 filter_by都适用。【2】使用很简单，下面列出几个常用的操作：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>) #equals</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">!=</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>) #not equals</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>like(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(112, 160, 208);"><span style="margin: 0px; padding: 0px;">%e</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">d%'</span>)) #LIKE</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">uery<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>in_([<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'wendy'</span>, <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>])) #IN</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>in_(session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>like(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(112, 160, 208);"><span style="margin: 0px; padding: 0px;">%e</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">d%'</span>))#IN</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">~</span>User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>in_([<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'wendy'</span>, <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>]))#not IN</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">None</span>)#is None</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">!=</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">None</span>)#not None</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">and_</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">query</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">filter(and_(User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">name </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">,User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">fullname </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Ed Jones'</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">)) # and</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>fullname <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Ed Jones'</span>) # and</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>fullname <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Ed Jones'</span>)# and</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">or_</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">query</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">filter(or_(User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">name </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">, User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">name </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'wendy'</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">)) #or</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>match(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'wendy'</span>)) #match</span></span></span>

9.2.返回列表和数量（标量？）

all()返回一个列表：可以进行Python列表的操作。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>like(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(112, 160, 208);"><span style="margin: 0px; padding: 0px;">%e</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">d'</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>order_by(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all()</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">[<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'EdJones'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'f8s7ccs'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'fred'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'FredFlinstone'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'blah'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span>]</span></span></span>

<br style="margin: 0px; padding: 0px;" />

first()适用于限制一个情况，返回查询到的第一个结果作为标量？：好像只能作为属性，类

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>first()<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,</span></span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Ed </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">Jones'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'f8s7ccs'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span></span>

one()完全获取所有行，并且如果查询到的不只有一个对象或是有复合行，就会抛出异常。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(14, 132, 181);">sqlalchemy.orm.exc </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">MultipleResultsFound</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">user <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>one()</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">try</span></span>:<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">　　user </span></span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>one()</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">except</span></span>　　MultipleResultsFound, e:</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;">　　<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print </span></span>e</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">Multiple </span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">rows were found <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for</span></span> one()</span></span></span>

如果一行也没有：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy.orm.exc </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">NoResultFound</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">try</span></span>:<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">　　user </span></span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id </span></span></span><span id="__mceDel" style="margin: 0px; padding: 0px; line-height: 1.5;"><span id="__mceDel" style="margin: 0px; padding: 0px;"><span id="__mceDel" style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">99</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>one()</span></span></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">except</span></span>NoResultFound, e:</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">　　print </span></span>e</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">No row was found <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for</span></span> one()</span></span></span>

one()方法对于想要解决“no items found”和“multiple items found”是不同的系统是极好的。（这句有语病啊）例如web服务返回，本来是在no results found情况下返回”404“的，结果在多个results found情况下也会跑出一个应用异常。

scalar()作为one()方法的依据，并且在one()成功基础上返回行的第一列。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>scalar()<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /><span id="__mceDel" style="margin: 0px; padding: 0px; color: rgb(0, 0, 0); font-family: 'Courier New'; line-height: 1.5;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">7</span></span></span></span></span></span></span>

9.3.使用字符串SQL

字符串能使Query更加灵活，通过text()构造指定字符串的使用，这种方法可以用在很多方法中，像filter()和order_by()。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">text</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;">for </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">user </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;">in </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">session</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">.</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">query(User)</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">.</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(text(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"id<224"</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>order_by(text(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"id"</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all()</span></span></span>

绑定参数可以指定字符串，用params()方法指定数值。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(text(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"id<:value and name=:name"</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /></span></span></span></span><span id="__mceDel" style="margin: 0px; padding: 0px; line-height: 1.5;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">params(value<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">224</span>, </span></span></span><span id="__mceDel" style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'fred'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>order_by(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>one()</span></span></span></span></span>

<span id="__mceDel" style="margin: 0px; padding: 0px;"></span>

如果要用一个完整的SQL语句，可以使用from_statement()。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">ession<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>from_statement(</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Droid Sans Fallback', sans-serif;">text(</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"SELECT</span><span id="__mceDel" style="margin: 0px; padding: 0px; line-height: 1.5;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">* FROM users where name=:name"</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">params(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all()</span></span></span></span>

也可以用from_statement()获取完整的”raw”，用字符名确定希望被查询的特定列:

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"id"</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"name"</span>, <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"thenumber12"</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /></span></span></span></span><span id="__mceDel" style="margin: 0px; padding: 0px; font-family: verdana, Arial, Helvetica, sans-serif; line-height: 1.5;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Serif', serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(51, 51, 51);">from_statement(text(</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"SELECT id, name, 12 as "</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Serif', serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"thenumber12 FROM users where name=:name"</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(51, 51, 51);">))</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(51, 51, 51);">\</span></span></span></span></span>

　params(name='ed').all()

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">[(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">u'ed'</span>, <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">12</span>)]</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">感觉这个不太符合</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">ORM</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">的思想啊。。。</span></span></span>

<br style="margin: 0px; padding: 0px;" />

9.4 计数

count()用来统计查询结果的数量。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>like(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(112, 160, 208);"><span style="margin: 0px; padding: 0px;">%e</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">d'</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>count()</span></span></span></span>

func.count()方法比count()更高级一点【3】

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">func</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(func<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>count(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name),User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>group_by(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all() </span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Droid Sans Fallback', sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">[(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">u'ed'</span>), (<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">u'fred'</span>), (<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">u'mary'</span>), (<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">u'wendy'</span>)]</span></span></span></span>

为了实现简单计数SELECT count(*) FROM table，可以这么写：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(func<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>count(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'*'</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>select_from(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>scalar()</span></span></span>

如果我们明确表达计数是根据User表的主键的话，可以省略select_from(User):

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(func<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>count(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>scalar()</span></span></span>

上面两行结果均为4。

go to （下）

原文链接：http://www.cnblogs.com/iwangzc/p/4114913.html

10.建立联系（外键）

是时候考虑怎样映射和查询一个和Users表关联的第二张表了。假设我们系统的用户可以存储任意数量的email地址。我们需要定义一个新表Address与User相关联。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">ForeignKey<br style="margin: 0px; padding: 0px;" /><br style="margin: 0px; padding: 0px;" /></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">sqlalchemy.orm</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">relationship, backref</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">class </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">Address</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">(Base):</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">__tablename__ <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'addresses'</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">id</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span><span style="margin: 0px; padding: 0px; line-height: 1.8;">Column(Integer, primary_key</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">True</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">)</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">email_address <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> Column(String, nullable<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">False</span>)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">user_id <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> Column(Integer, ForeignKey(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'users.id'</span>))</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">user <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> relationship(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"User"</span>, backref<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span>backref(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'addresses'</span>,order_by<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">id</span>))</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">def</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(6, 40, 126);">__repr__</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">(</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">self</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">):</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">return</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"<Address(email_address='</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(112, 160, 208);"><span style="margin: 0px; padding: 0px;">%s</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">')>"</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">%</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">self</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">email_address</span></span></span></span>

构造类和外键简单，就不过多赘述。主要说明以下relationship()函数：这个函数告诉ORM，Address类应该和User类连接起来，通过使用addresses.user。relationship()使用外键明确这两张表的关系。决定Adderess.user属性是多对一的。relationship()的子函数backref()提供表达反向关系的细节：relationship()对象的集合被User.address引用。多对一的反向关系总是一对多。更多的细节参考Basic RelRational Patterns。

这两个互补关系：Address.user和User.addresses被称为双向关系。这是SQLAlchemy ORM的一个非常关键的功能。更多关系backref的细节参见Linking Relationships with Backref。

假设声明的方法已经开始使用，relationship()中和其他类关联的参数可以通过strings指定。在上文的User类中，一旦所有映射成功，为了产生实际的参数，这些字符串会被当做Python的表达式。下面是一个在User类中创建双向联系的例子：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(0, 112, 32);">class </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(14, 132, 181);"><span style="margin: 0px; padding: 0px;">User</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">(Base):</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">addresses <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span>relationship(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"Address"</span>, order_by<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"Address.id"</span>, backref<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">"user"</span>)</span></span></span>

一些知识：

在大多数的外键约束（尽管不是所有的）关系数据库只能链接到一个主键列，或具有唯一约束的列。

外键约束如果是指向多个列的主键，并且它本身也具有多列，这种被称为“复合外键”。

外键列可以自动更新自己来相应它所引用的行或者列。这被称为级联，是一种建立在关系数据库的功能。

外键可以参考自己的表格。这种被称为“自引”外键。

我们需要在数据库中创建一个addresses表，所以我们会创建另一个元数据，这将会跳过已经创建的表。

11.操作主外键关联的对象

现在我们已经在User类中创建了一个空的addresser集合，可变集合类型，例如set和dict，都可以用，但是默认的集合类型是list。

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">jack <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>, fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Jack Bean'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'gjffdd'</span>)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">jack</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">addresses</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">[]</span></span></span>

现在可以直接在User对象中添加Address对象。只需要指定一个完整的列表：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">jack<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span>[Address(email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack@google.com'</span>),Address(email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'j25@yahoo.com'</span>)]</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">当使用双向关系时，元素在一个类中被添加后便会自动在另一个类中添加。这种行为发生在</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">Python</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">的更改事件属性中而不是用</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">SQL</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">语句：</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>> </span>jack<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses[<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>]</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">Address(email_address</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'j25@yahoo.com'</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">)</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>> </span>jack<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses[<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">1</span>]<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>, fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Jack Bean'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'gjffdd'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">把</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">jack</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">提交到数据库中，再次查询</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">Jack</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">，（</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">No SQL is yet issued for Jack’s addresses:</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">）这句实在是翻译不了了，看看代码就明白是什么意思：</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>> </span>jack <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\<br style="margin: 0px; padding: 0px;" /></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">filter_by(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>one()</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>> </span>jack</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Jack Bean'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'gjffdd'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span></span>

<br style="margin: 0px; padding: 0px;" />

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span>jack<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses </span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">[<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>Address(email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack@google.com'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span>,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>Address(email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'j25@yahoo.com'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span>]</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">当我们访问</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">uaddresses</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">集合时，</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">SQL</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">会被突然执行，这是一个延迟加载（</span><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;"><a target=_blank target="_blank" href="http://docs.sqlalchemy.org/en/rel_0_9/glossary.html#term-lazy-loading" style="color: rgb(0, 0, 0); text-decoration: none; margin: 0px; padding: 0px; border-bottom-width: 1px; border-bottom-style: dotted; border-bottom-color: rgb(51, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;">lazy loading</span></span></span></span></span></a></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;">）关系的典型例子。现在</span></span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">addresses</span></span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;">集合加载完成并且可以像对待普通列表一样对其进行操作。以后我们会优化这种加载方式。</span></span></span></span>

<span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;">12.</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;">使用</span></span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">JOINS</span></span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px;">查询</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">现在我们有了两张表，可以进行更多的查询操作，特别是怎样对两张表同时进行查询，</span><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><a target=_blank target="_blank" href="http://en.wikipedia.org/wiki/Join_%28SQL%29" style="color: rgb(0, 0, 0); text-decoration: none; margin: 0px; padding: 0px; border-bottom-width: 1px; border-bottom-style: dotted; border-bottom-color: rgb(51, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(119, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;">Wikipediapage on SQL JOIN</span></span></span></span></a><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">提供了很详细的说明，其中一些我们将在这里说明。之前用</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">Query</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">.</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">filter</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">()</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">时，我们已经用过</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">JOIN</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">了，</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">filter</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">是一种简单的隐式</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">join</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">：</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"> u, a </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">session</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">query(User, Address)</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">(User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">id</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">Address</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">user_id)</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">(Address</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">email_address</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack@google.com'</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">)</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">all</span><span style="margin: 0px; padding: 0px; line-height: 1.5; font-family: 'Liberation Mono', monospace;">():   </span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> u</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> a</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'JackBean'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'gjffdd'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>Address(email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack@google.com'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">用</span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">Query.join()</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">方法会更加简单：</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>join(Address)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack@google.com'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">all</span>() </span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">[<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'JackBean'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'gjffdd'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span>]</span></span></span>

<span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">之所以</span></span><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;">Query.join()</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">知道怎么</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">join</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">两张表是因为它们之间只有一个外键。如果两张表中没有外键或者有一个以上的外键，当下列几种形式使用的时候，</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">Query.join()</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">可以表现的更好：</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>join(Address,</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span>Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user_id)</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);"><span style="margin: 0px; padding: 0px;"># </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);">明确的条件</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>join(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);"><span style="margin: 0px; padding: 0px;"># </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);">指定从左到右的关系</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>join(Address,User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses)    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);"><span style="margin: 0px; padding: 0px;">#</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);">同样</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);">，有明确的目标</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>join(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'addresses'</span>) <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);"><span style="margin: 0px; padding: 0px;"># </span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);">同样</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);">，使用字符串</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">outerjoin()</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">和</span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">join()</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">用法相同</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>outerjoin(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);"><span style="margin: 0px; padding: 0px;"># LEFT OUTER JOIN</span></span></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;">12.1</span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);">使用别名</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">当在多个表中查询时，如果同一张表需要被引用好几次，</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;">SQL</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">通常要求对这个表起一个别名，因此，</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;">SQL</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">可以区分对这个表进行的其他操作。</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;">Query</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">也支持别名的操作。下面我们</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;">joinAddress</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">实体两次，找到同时拥有两个不同</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;">email</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);">的用户：</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(14, 132, 181);">sqlalchemy.orm</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"> aliased</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span>adalias1 <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> aliased(Address)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">adalias2 </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span><span style="margin: 0px; padding: 0px; line-height: 1.8;">aliased(Address)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">username, email1, email2 </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name,adalias1<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address,adalias2<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    join(adalias1, User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    join(adalias2, User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(adalias1<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack@google.com'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(adalias2<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'j25@yahoo.com'</span>):</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> username, email1,email2      </span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">jackjack<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(85, 85, 85);"><span style="margin: 0px; padding: 0px;">@google.com</span></span> j25<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(85, 85, 85);"><span style="margin: 0px; padding: 0px;">@yahoo.com</span></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">12.1</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">使用子查询（暂时理解不了啊，多看代码研究吧：</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">(</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">）</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(14, 132, 181);">sqlalchemy.sql</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"> func</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">stmt <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user_id,func<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>count(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'*'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(198, 93, 9);"><span style="margin: 0px; padding: 0px;">...</span></span>        label(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'address_count'</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(198, 93, 9);"><span style="margin: 0px; padding: 0px;">...</span></span>        group_by(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user_id)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>subquery()</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for</span></span> u, count <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User,stmt<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>c<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>address_count)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    outerjoin(stmt, User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span>stmt<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>c<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user_id)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>order_by(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>id):</span></span></span>

<span id="__mceDel" style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> u, count</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'EdJones'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'f8s7ccs'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">None</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'wendy'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Wendy Williams'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'foobar'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">None</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'mary'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Mary Contrary'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'xxg527'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">None</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'fred'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Fred Flinstone'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'blah'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">None</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Jack Bean'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'gjffdd'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(32, 128, 80);">2</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;">12.2</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 51, 51);">从子查询中选择实体？</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">上面的代码中我们只返回了包含子查询的一个列的结果。如果想要子查询映射到一个实体的话，使用</span><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: Verdana, sans-serif;"><span style="margin: 0px; padding: 0px; line-height: 1.8;">aliased()</span></span></span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 0, 0);"><span style="margin: 0px; padding: 0px; line-height: 1.8;">设置一个要映射类的子查询别名：</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span>stmt <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(Address)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>     <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">!=</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'j25@yahoo.com'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>     subquery()</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span>adalias <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span> aliased(Address, stmt)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 102, 0);">#</span></span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(255, 102, 0);">？为什么有两个参数？</span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for</span></span> user, address <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User, adalias)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>        join(adalias, User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses): </span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> user</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> address</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>User(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>,fullname<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'Jack Bean'</span>, password<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'gjffdd'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);"><</span>Address(email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack@google.com'</span>)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">></span></span></span></span>

12.3使用EXISTS（存在？）

如果表达式返回任何行，EXISTS为真，这是一个布尔值。它可以用在jions中，也可以用来定位在一个关系表中没有相应行的情况：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">from </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; color: rgb(14, 132, 181);">sqlalchemy.sql</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">import</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"> exists</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">stmt </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">= </span><span style="margin: 0px; padding: 0px; line-height: 1.8;">exists()</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">where(Address</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">user_id</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">id)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">name, </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">session</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">query(User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">name)</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">filter(stmt):<br style="margin: 0px; padding: 0px;" /></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> name</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">jack</span></span></span>

等价于：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;"> name, </span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span><span style="margin: 0px; padding: 0px; line-height: 1.8;">session</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">query(User</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">name)</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span><span style="margin: 0px; padding: 0px; line-height: 1.8;">\</span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span> 　　<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>any()):  </span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> name</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">jack</span></span></span>

any()限制行匹配：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">for</span></span> name, <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">in </span></span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>   <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>any(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>like(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(112, 160, 208);"><span style="margin: 0px; padding: 0px;">%g</span></span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">oogle%'</span>))):  </span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>    <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);"><span style="margin: 0px; padding: 0px;">print</span></span> name</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">jack</span></span></span>

has()和any()一样在应对多对一关系的情况下（注意“～“意味着”NOT”）

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">>>> </span>session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(Address)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>\</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">...</span>        <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(0, 112, 32);">filter</span>(<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">~</span>Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>has(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'jack'</span>))<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>all()</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">[]</span></span></span>

12.4 常见的关系运算符

== ！= None 都是用在多对一中，而contains()用在一对多的集合中：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> someuser)</span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>contains(someaddress))</span></span></span>

Any()（用于集合中）：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>any(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>email_address <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">==</span> <span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'bar'</span>))</span></span></span><span id="__mceDel" style="margin: 0px; padding: 0px; line-height: 1.5;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">#</span></span></span><span id="__mceDel" style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 128, 144);"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">also takes keyword arguments:</span></span></span></span></span>

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(User<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>addresses<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>any(email_address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'bar'</span>))</span></span></span>

as()（用在标量？不在集合中）：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">query<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>filter(Address<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>user<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>has(name<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">=</span><span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'ed'</span>))</span></span></span>

Query.with_parent()（所有关系都适用）：

<span style="margin: 0px; padding: 0px; line-height: 1.8;"><span style="margin: 0px; padding: 0px; line-height: 1.8; font-family: 'Liberation Mono', monospace;"><span lang="en-US" style="margin: 0px; padding: 0px; line-height: 1.8;">session<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>query(Address)<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(102, 102, 102);">.</span>with_parent(someuser,<span style="margin: 0px; padding: 0px; line-height: 1.8; color: rgb(64, 112, 160);">'addresses'</span>)</span></span></span>

13 预先加载（跟性能有关）和lazy loading相对，建议直接查看文档吧

待补充。。。

0 0