SQLAlchemy Examples

看 SQLAlchemy 自带的 zblog 的例子，可以看到 SQLAlchemy 一些非常有用的特性。

文章相关评论数统计

比如显示文章列表的同时我们希望获得相关文章的评论数，如果是用 django 那就只能放弃 ORM 的好处自己去执行 sql 语句了，否则就只会导致 n+1 条 SQL 语句的执行。 在 SQLAlchemy 中你可以把任意的 select 语句映射到一个 class ，这样就可以用一条 SQL 语句搞定，还能获得 ORM 的好处，下面是原封不动拷过来的代码（只调整了下格式）：

   # Post mapper, these are posts within a blog.
  # since we want the count of comments for each post,
  # create a select that will get the posts
  # and count the comments in one query.
  posts_with_ccount = select(
      [c for c in tables.posts.c if c.key != 'body'] + [
          func.count(tables.comments.c.comment_id).label('comment_count')
      ],
      from_obj = [
          outerjoin(tables.posts, tables.comments)
      ],
      group_by=[
          c for c in tables.posts.c if c.key != 'body'
      ]
      ) .alias('postswcount')

  # then create a Post mapper on that query.
  # we have the body as "deferred" so that it loads only when needed,
  # the user as a Lazy load, since the lazy load will run only once per user and
  # its usually only one user's posts is needed per page,
  # the owning blog is a lazy load since its also probably loaded into the identity map
  # already, and topics is an eager load since that query has to be done per post in any
  # case.
  mapper(Post, posts_with_ccount, properties={
      'id':posts_with_ccount.c.post_id,
      'body':deferred(tables.posts.c.body),
      'user':relation(user.User, lazy=True,
               backref=backref('posts', cascade="all, delete-orphan")),
      'blog':relation(Blog, lazy=True,
               backref=backref('posts', cascade="all, delete-orphan")),
      'topics':relation(TopicAssociation, lazy=False, private=True,
               association=Topic, backref='post')
  }, order_by=[desc(posts_with_ccount.c.datetime)])

树形评论 映射如下：

   # comment mapper.  This mapper is handling a hierarchical relationship on itself,
  # and contains
  # a lazy reference both to its parent comment and its list of child comments.
  mapper(Comment, tables.comments, properties={
      'id':tables.comments.c.comment_id,
      'post':relation(Post, lazy=True,
               backref=backref('comments', cascade="all, delete-orphan")),
      'user':relation(user.User, lazy=False,
               backref=backref('comments', cascade="all, delete-orphan")),
      'parent':relation(Comment,
               primaryjoin=tables.comments.c.parent_comment_id==tables.comments.c.comment_id,
               foreignkey=tables.comments.c.comment_id, lazy=True, uselist=False),
      'replies':relation(Comment,
               primaryjoin=tables.comments.c.parent_comment_id==tables.comments.c.comment_id,
               lazy=True, uselist=True, cascade="all"),
  })

很多时候我们需要一次性获取对应一个文章的所有评论，可以用一条 select 先把数据取出，然后手动建立树形结构：

# we define one special find-by for the comments of a post, which is going to make its own
# "noload" mapper and organize the comments into their correct hierarchy in one pass. hierarchical
# data normally needs to be loaded by separate queries for each set of children, unless you
# use a proprietary extension like CONNECT BY.
def find_by_post(post):
  """returns a hierarchical collection of comments based on a given criterion.
  uses a mapper that does not lazy load replies or parents, and instead
  organizes comments into a hierarchical tree when the result is produced.
  """
  q = session().query(Comment).options(noload('replies'), noload('parent'))
  comments = q.select_by(post_id=post.id)
  result = []
  d = {}
  for c in comments:
      d[c.id] = c
      if c.parent_comment_id is None:
          result.append(c)
          c.parent=None
      else:
          parent = d[c.parent_comment_id]
          parent.replies.append(c)
          c.parent = parent
  return result

Comment.find_by_post = staticmethod(find_by_post)