【SQLAlchemy】官方文档教程学习4：回滚和查询操作

Rolling Back

由于 "会话 "在事务中工作，我们也可以回滚所做的更改。让我们做两个变化，之后我们将恢复回之前的状态；ed_user的用户名被设置为Edwardo。

>>> ed_user.name = 'Edwardo'

我们再增加一个错误的用户，fake_user。

>>> fake_user = User(name='fakeuser', fullname='Invalid', nickname='12345')
>>> session.add(fake_user)

查询会话，我们可以看到它们被冲入了当前的事务中。

SQL>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()
[<User(name='Edwardo', fullname='Ed Jones', nickname='eddie')>, <User(name='fakeuser', fullname='Invalid', nickname='12345')>]

向后滚动，我们可以看到ed_user的名字又回到了ed，而fake_user已经被踢出了会话。

SQL>>> session.rollback()

SQL>>> ed_user.name
u'ed'
>>> fake_user in session
False

发出SELECT说明对数据库进行了修改。

SQL>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

Querying

使用 Session上的query()方法创建一个 Query对象。这个函数需要一个可变数量的参数，这些参数可以是类和类实例描述符的任意组合。下面，我们表示一个加载User实例的Query。当在迭代上下文中评估时，将返回存在的User对象列表。

SQL>>> for instance in session.query(User).order_by(User.id):
...     print(instance.name, instance.fullname)
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

Query也接受ORM说明的描述符作为参数。任何时候，当多个类实体或基于列的实体被表示为query()函数的参数时，返回的结果都表示为元组。

SQL>>> for name, fullname in session.query(User.name, User.fullname):
...     print(name, fullname)
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

Query返回的元组由Row类提供，可以像对待普通的Python对象一样对待。这些名称与属性的属性名和类的类名是一样的。

SQL>>> for row in session.query(User, User.name).all():
...    print(row.User, row.name)
<User(name='ed', fullname='Ed Jones', nickname='eddie')> ed
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> wendy
<User(name='mary', fullname='Mary Contrary', nickname='mary')> mary
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> fred

可以使用ColumnElement.label()构造来控制单个列表达式的名称，它可以从任何ColumnElement派生的对象中获得，也可以从任何被映射到的类属性中获得(如User.name)。

SQL>>> for row in session.query(User.name.label('name_label')).all():
...    print(row.name_label)
ed
wendy
mary
fred

假设在对Session.query()的调用中存在多个实体，可以使用aliased()控制给User等完整实体的名称。

>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')

SQL>>> for row in session.query(user_alias, user_alias.name).all():
...    print(row.user_alias)
<User(name='ed', fullname='Ed Jones', nickname='eddie')>
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

Query的基本操作包括发出LIMIT和OFFSET，最方便的是使用Python数组切片，通常与ORDER BY结合。

SQL>>> for u in session.query(User).order_by(User.id)[1:3]:
...    print(u)
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>

过滤结果，可以用filter_by()来完成，后者使用关键字参数。

SQL>>> for name, in session.query(User.name).\
...             filter_by(fullname='Ed Jones'):
...    print(name)
ed

filter()：

SQL>>> for name, in session.query(User.name).\
...             filter(User.fullname=='Ed Jones'):
...    print(name)
ed

Query对象是完全生成的，这意味着大多数方法调用都会返回一个新的Query对象，在这个对象上可以添加更多的标准。例如，要查询名为 "ed "的用户，其全名是 “Ed Jones”，你可以调用filter()两次，它使用AND将标准连接起来。

SQL>>> for user in session.query(User).\
...          filter(User.name=='ed').\
...          filter(User.fullname=='Ed Jones'):
...    print(user)
<User(name='ed', fullname='Ed Jones', nickname='eddie')>

filter_by() 和 filter() 的最主要的区别：

模块	语法	><（大于和小于）查询	and_和or_查询
filter_by()	直接用属性名，比较用=	不支持	不支持
filter()	用类名.属性名，比较用==	支持	支持

可以看出，filter_by() 只接受键值对参数，所以 filter_by() 不支持><（大于和小于）和 and_、or_查询

常见的过滤器操作

下面是 "filter() "中最常用的一些操作符的简介。

• ColumnOperators.__eq__():

  query.filter(User.name == 'ed')
  

• ColumnOperators.__ne__():

  query.filter(User.name != 'ed')
  

• ColumnOperators.like():

  query.filter(User.name.like('%ed%'))
  

Note
ColumnOperators.like()渲染LIKE运算符，该运算符在某些后台不区分大小写，在其他后台则区分大小写。对于保证不区分大小写的比较，使用ColumnOperators.ilike()。

• ColumnOperators.ilike() (case-insensitive LIKE):

  query.filter(User.name.ilike('%ed%'))
  

• ColumnOperators.in_():

  query.filter(User.name.in_(['ed', 'wendy', 'jack']))
  
  # works with query objects too:
  query.filter(User.name.in_(
      session.query(User.name).filter(User.name.like('%ed%'))
  ))
  
  # use tuple_() for composite (multi-column) queries
  from sqlalchemy import tuple_
  query.filter(
      tuple_(User.name, User.nickname).\
      in_([('ed', 'edsnickname'), ('wendy', 'windy')])
  )
  

• ColumnOperators.not_in():

  query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
  

• ColumnOperators.is_():

  query.filter(User.name == None)
  
  # alternatively, if pep8/linters are a concern
  query.filter(User.name.is_(None))
  

• ColumnOperators.is_not():

  query.filter(User.name != None)
  
  # alternatively, if pep8/linters are a concern
  query.filter(User.name.is_not(None))
  

• AND:

  # use and_()
  from sqlalchemy import and_
  query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
  
  # or send multiple expressions to .filter()
  query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
  
  # or chain multiple filter()/filter_by() calls
  query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
  

• OR:

  from sqlalchemy import or_
  query.filter(or_(User.name == 'ed', User.name == 'wendy'))
  

• ColumnOperators.match():

  query.filter(User.name.match('wendy'))
  

返回列表和标量

Query上的一些方法会立即发出SQL，并返回一个包含加载数据库结果的值。下面简单介绍一下。

• Query.all() 返回列表:

  >>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
  SQL>>> query.all()
  [<User(name='ed', fullname='Ed Jones', nickname='eddie')>,
        <User(name='fred', fullname='Fred Flintstone', nickname='freddy')>]
  

• Query.first() 将第一个结果作为标量返回。

  SQL>>> query.first()
  <User(name='ed', fullname='Ed Jones', nickname='eddie')>
  

• Query.one()完全获取所有的行，如果结果中不是正好有一个对象标识或复合行，就会引发一个错误。随着多行的发现。

  >>> user = query.one()
  Traceback (most recent call last):
  ...
  MultipleResultsFound: Multiple rows were found for one()
  

  没有找到:

  >>> user = query.filter(User.id == 99).one()
  Traceback (most recent call last):
  ...
  NoResultFound: No row was found for one()
  

  Query.one()方法对于那些期望以不同方式处理 "未找到项目 "和 "找到多个项目 "的系统来说是非常好的；比如一个RESTful Web服务，当没有找到结果时，它可能会提出 “404未找到”，但当找到多个结果时，会提出一个应用程序错误。

• Query.one_or_none()与Query.one()一样，只是如果没有找到结果，它不会引发错误；它只是返回None。然而，与Query.one()一样，如果找到多个结果，它确实会引发错误。

• Query.scalar()调用Query.one()方法，成功后返回行的第一列。

  >>> query = session.query(User.id).filter(User.name == 'ed').\
  ...    order_by(User.id)
  SQL>>> query.scalar()
  1
  

使用文本SQL

字符串可以通过text()结构来灵活地与Query一起使用，大多数适用的方法都接受这种结构。例如，Query.filter()和Query.order_by()。

>>> from sqlalchemy import text
SQL>>> for user in session.query(User).\
...             filter(text("id<224")).\
...             order_by(text("id")).all():
...     print(user.name)
ed
wendy
mary
fred

绑定参数可以用基于字符串的SQL，使用冒号来指定。要指定值，使用Query.params()方法。

SQL>>> session.query(User).filter(text("id<:value and name=:name")).\
...     params(value=224, name='fred').order_by(User.id).one()
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

要使用完全基于字符串的语句，可以将代表完整语句的text()构造传递给Query.from_statement()。无需进一步说明，ORM将根据列名将ORM映射中的列与SQL语句返回的结果进行匹配。

SQL>>> session.query(User).from_statement(
...  text("SELECT * FROM users where name=:name")).params(name='ed').all()
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

为了更好地将映射的列定向到文本SELECT，以及为了以任意顺序匹配特定的列子集，单个映射的列按所需顺序传递给TextClause.columns()。

>>> stmt = text("SELECT name, id, fullname, nickname "
...             "FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id, User.fullname, User.nickname)
SQL>>> session.query(User).from_statement(stmt).params(name='ed').all()
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

当从text()结构中选择时，Query仍然可以指定要返回哪些列和实体；我们也可以像其他任何情况一样，代替query(User)单独要求列。

>>> stmt = text("SELECT name, id FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id)
SQL>>> session.query(User.id, User.name).\
...          from_statement(stmt).params(name='ed').all()
[(1, u'ed')]

计数

Query包括一个称为Query.count()的方便计数方法。

SQL>>> session.query(User).filter(User.name.like('%ed')).count()
2

计算 “count()”。

Query.count()过去是一个非常复杂的方法，当它试图猜测是否需要围绕现有的查询建立一个子查询时，在一些特殊的情况下，它不会做正确的事情。现在它每次都会使用一个简单的子查询，只有两行，而且总是返回正确的答案。如果某条语句绝对不能容忍子查询的存在，就使用func.count()。

Query.count()](https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.count)方法用来确定SQL语句会返回多少行。从上面生成的SQL来看，SQLAlchemy总是把我们要查询的内容放到一个子查询中，然后从这个子查询中计算行数。在某些情况下，这可以简化为更简单的 “SELECT count(*) FROM table”，然而现代版本的SQLAlchemy并不试图猜测什么时候适合这样做，因为确切的SQL可以使用更明确的方式发出。

对于需要特别指明 "要计数的东西 "的情况，我们可以直接使用表达式func.count()来指定 "count "函数，可以从expression.func构造中获得。下面我们用它来返回每个不同用户名的数量。

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