Python db_session.query函数代码示例

 def handleUpdateProjectDataRecord(self):
     '''
     更新研究项目中的一行数据
     :return:
     '''
     _data_dict = self.postParams("*")
     if "pid" in _data_dict and "row_id" in _data_dict:
         try:
             project_id = int(_data_dict.get("pid", 0))
             row_id = _data_dict.get("row_id", '')
         except Exception:
             project_id = 0
         if project_id > 0 and '' != row_id:
             try:
                 db_session.query(ProjectItem).filter(ProjectItem.proj_id==project_id).filter(ProjectItem.row_id==row_id).delete()
                 for _d in _data_dict:
                     if "pid" != _d and "row_id" != _d:
                         pproperty_item = db_session.query(ProjectProperty).filter(ProjectProperty.label==_d).first()
                         if pproperty_item is not None:
                             p_item = ProjectItem()
                             p_item.label = _d
                             p_item.value = _data_dict[_d]
                             p_item.row_id = row_id
                             p_item.proj_id = project_id
                             p_item.p_id = pproperty_item.id
                             db_session.add(p_item)
                 db_session.commit()
                 self.changeResponse2Success()
             except Exception as e:
                 db_session.rollback()
                 self.setFailureReason(str(e))
     else:
         self.setFailureReason('缺少关键参数！')

    def prepareForProjectDataTable(self):
        """
        为实验项目数据表格
        :return:
        """
        def _arrangeProjectDatas(_data_collection):
            _middle_arrange = {}
            for _raw_data in _data_collection:
                if not _raw_data.row_id in _middle_arrange:
                    _middle_arrange[_raw_data.row_id] = {}
                _middle_arrange[_raw_data.row_id].setdefault(_raw_data.label, _raw_data)

            for _middle_data in _middle_arrange:
                for _property in properties:
                    if not _property.label in _middle_arrange[_middle_data]:
                        _middle_arrange[_middle_data][_property.label] = None

            return _middle_arrange

        # TODO 组合项目数据表格，使body和head对应
        pid, = self.requestParams("pid")
        if self.checkParamsAvailable(pid):
            project = db_session.query(Project).filter(Project.id==pid).first()
            if project is not None:
                project_data = {}
                properties = db_session.query(ProjectProperty).filter(ProjectProperty.p_id==pid).all()
                data_records = db_session.query(ProjectItem).filter(ProjectItem.proj_id==pid).all()
                project_data.setdefault('project', project)
                project_data.setdefault('properties', properties)
                project_data.setdefault('records', _arrangeProjectDatas(data_records))

                self.setData(project_data)
                self.changeResponse2Success()
            else:
                self.setFailureReason("实验项目不存在！")

 def handleRegister(self):
     """
     处理用户注册
     :return:
     """
     name, email, pwd = self.postParams('name', 'email', 'pwd')
     if self.checkParamsAvailable(email, pwd):
         exists_query = db_session.query(User).filter(User.email==email).exists()
         if not db_session.query(exists_query).scalar():
             try:
                 set_user = User()
                 set_user.name = name
                 set_user.email = email
                 set_user.salt = User.genSalt()
                 set_user.pwd = User.genPassword(pwd, set_user.salt)
                 set_user.reg_ip = str(request.remote_addr)
                 db_session.add(set_user)
                 db_session.commit()
                 self.changeResponse2Success()
                 mailer = getMailSender()
                 mailer.setMailtoList([email])
                 mailer.setSubject("感谢注册 [史前-在线定量研究工具]")
                 _mail_content = render_template("noticer/email/_register.html", nickname=name)
                 mailer.setContent(_mail_content)
                 mailer.send()
             except Exception as e:
                 db_session.rollback()
                 self.setFailureReason(str(e))
         else:
             self.setFailureReason("该邮箱已经被注册，请更换邮箱申请或尝试找回密码！")

 def handleRemoveGeopointGroup(self):
     """
     删除指定的整个地理坐标集合
     需要校验地理坐标集合是否属于操作者本人
     :return:
     """
     gid, pwd = self.postParams("gid", "pwd")
     if self.checkParamsAvailable(gid, pwd):
         if self.userIdentification(pwd):
             current_user = UserIdentify()
             geo_group_info = db_session.query(GeoPointGroup).filter(GeoPointGroup.id==gid).first()
             if geo_group_info is not None:
                 if (geo_group_info.u_id == current_user.uid):
                     try:
                         # 删除集合下的所有观察子节点
                         db_session.query(GeoPointUnit).filter(GeoPointUnit.group_id==gid).delete()
                         db_session.delete(geo_group_info)
                         db_session.commit()
                         self.changeResponse2Success()
                     except Exception as e:
                         self.setFailureReason(str(e), redirect_url=url_for(".ViewGeoGroup", gid=gid))
                 else:
                     self.setFailureReason("该地理坐标点位集合不属于您，无法执行删除操作！", redirect_url=url_for(".ViewGeoGroup", gid=gid))
             else:
                 self.setFailureReason("指定的地理坐标点位集合不存在！", redirect_url=url_for(".ViewGeoGroup", gid=gid))
         else:
             self.setFailureReason("对不起，您不具备执行当前操作的权限！", redirect_url=url_for(".ViewGeoGroup", gid=gid))

 def loadPropertiesOfProject(self, pid):
     """
     读取指定id项目下的字段属性列表
     :param pid:
     :return:
     """
     project_exists = db_session.query(Project).filter(Project.id==pid).exists()
     return db_session.query(ProjectProperty).filter(ProjectProperty.p_id==pid).all() \
             if db_session.query(project_exists).scalar() else []

 def validate_verifier(self, client_key, resource_owner_key, verifier):
     try:
         db_session.query(RequestToken, Client).filter(
             Client.client_key == client_key,
             RequestToken.token == resource_owner_key,
             RequestToken.verifier == verifier
         ).one()
         return True
     except NoResultFound:
         return False

  def _whoami(
        self, name, alias_contain=True, forward_match=False,
        partial_match=False, backward_match=False, one=False):
    """
    nameからUserを探す。
    User.admin User.enable
    if True in [pr.active for pr in User.personal_records]:
    といった真偽値で、操作が可能かどうか判定するため、たいていの操作はここから始まる。

    forward_match, partial_match, backward_matchのどれかをTrueに指定する場合は、
    どれか1つだけにしなければならない。

    nameの末尾のアンダースコアは取り除かれる。
    IRCの事情をケアする仕様。
    """
    # 前方一致、部分一致、後方一致
    if forward_match or partial_match or backward_match:
      if forward_match: base = u"%s%%"
      if partial_match: base = u"%%%s%%"
      if backward_match: base = u"%%%s"
      users = db_session.query(User
          ).filter(User.enable==True
          ).filter(User.name.like(base % self._rstrip_underscore(name))
          ).all()
      if alias_contain:
        aliases = db_session.query(UserAlias
            ).filter(UserAlias.name.like(base % name)
            ).all()
        for alias in aliases:
          if alias.user.enable and (alias.user not in users):
            users.append(alias.user)
      # 唯一の該当者だった場合のみ結果がほしい場合
      if one:
        if len(users) == 1:
          return users[0]
        else:
          return None
      else:
        return users if users else None
    # 完全一致
    else:
      try:
        return db_session.query(User
            ).filter(User.name==self._rstrip_underscore(name)
            ).filter(User.enable==True
            ).one()
      except NoResultFound:
        pass
      if alias_contain:
        try:
          alias = db_session.query(UserAlias).filter(UserAlias.name==name).one()
          return alias.user if alias.user.enable else None
        except NoResultFound:
          pass
      return None

    def validate_access_token(self, client_key, resource_owner_key):
        try:
            db_session.query(AccessToken, Client).filter(
                Client.client_key == client_key,
                Client.id == AccessToken.client_id,
                AccessToken.token == resource_owner_key
            ).one()
            return True

        except NoResultFound:
            return False

    def validate_request_token(self, client_key, resource_owner_key):
        # TODO: make client_key optional
        if client_key:
            db_session.query(RequestToken, Client).filter(
                RequestToken.token == resource_owner_key,
                Client.client_key == client_key).one()
        else:
            RequestToken.query.filter_by(token=resource_owner_key).one()
        try:
            return True

        except NoResultFound:
            return False

 def handleDeleteProjectDataRecord(self):
     '''
     删除研究项目中的一行数据
     :return:
     '''
     pid, row_id = self.postParams("pid", "row_id")
     if self.checkParamsAvailable(pid, row_id):
         try:
             db_session.query(ProjectItem).filter(ProjectItem.proj_id==pid).filter(ProjectItem.row_id==row_id).delete()
             db_session.commit()
             self.changeResponse2Success()
         except Exception as e:
             db_session.rollback()

    def loadValueLabel(self):
        """
        获取有序变量的取值
        :return:
        """
        _lb = self.value
        property_rel = db_session.query(ProjectProperty).filter(ProjectProperty.id==self.p_id).first()
        if property_rel is not None and property_rel.type == ProjectProperty.TYPE_SEQUENCE:
            property_option = db_session.query(PropertyOptions).filter(PropertyOptions.p_id==self.p_id).\
                filter(PropertyOptions.label==self.value).first()
            if property_option is not None:
                _lb = property_option.name

        return _lb

  def fix_result(self, json):
    """勝敗をつけ直す。"""
    args = loads(json)
    print args
    user = self._whoami(args["caller"])
    if user is None:
      return dumps((False,))

    if type(args["room_id"]) == int:
      try:
        gr = db_session.query(GeneralRecord
          ).filter(GeneralRecord.id==args["room_id"]
          ).one()
      except NoResultFound:
        return dumps((False,))
      if gr.completed_at is None:
        return dumps((False,))
      # 管理者かホスト自身だけが結果を訂正できる
      if (not user.admin) and (user.name != gr.room_owner):
        return dumps((False,))
      owner_pr = self._get_owner_pr(gr)

    else:
      try:
        # UserのPersonalRecordのうち最新のレコードを取得
        pr_id = db_session.query(
          func.max(PersonalRecord.id)
          ).correlate(PersonalRecord
          ).filter(PersonalRecord.user_id==user.id).one()[0]
        pr = db_session.query(PersonalRecord
          ).filter(PersonalRecord.id==pr_id).one()
      except NoResultFound:
        return dumps((False,))
      gr = pr.general_record
      if (gr.room_owner != pr.user.name) or (gr.completed_at is None):
        return dumps((False,))
      owner_pr = pr

    self._save_result(owner_pr, args["won"], rollback=True)
    gr = owner_pr.general_record
    members = []
    for member in gr.personal_records:
      # 勝敗のついた参加者のみに限定する（入って抜けた人は除外）
      if member.won is not None:
        members.append(member)
    returns = self._construct_room_info(gr, user)
    returns.update({"members": [self._construct_member_info(member) for member in members]})
    return dumps((True, returns))

 def handleAddProjectDataRecord(self):
     _data_dict = self.postParams("*")
     if "pid" in _data_dict:
         try:
             project_id = int(_data_dict.get('pid'))
         except Exception as e:
             project_id = 0
         if project_id > 0:
             row_num = str(uuid1())
             try:
                 for _d in _data_dict:
                     if "pid" != _d:
                         pproperty_item = db_session.query(ProjectProperty).\
                             filter(ProjectProperty.label==_d).\
                             filter(ProjectProperty.p_id==project_id).first()
                         if pproperty_item is not None:
                             p_item = ProjectItem()
                             p_item.label = _d
                             p_item.value = _data_dict[_d]
                             p_item.row_id = row_num
                             p_item.proj_id = project_id
                             p_item.p_id = pproperty_item.id
                             db_session.add(p_item)
                 db_session.commit()
                 self.changeResponse2Success()
             except Exception as e:
                 db_session.rollback()
                 self.setFailureReason(str(e))

 def breakup(self, json):
   """解散、強制解散"""
   args = loads(json)
   print args
   user = self._whoami(args["caller"])
   if user is None:
     return dumps((False,))
   pr = self._is_room_owner(args["channel"], args["caller"])
   if (not pr) and (not args["force"]):
     return dumps((False,))
   if (not pr) and args["force"]:
     q = db_session.query(GeneralRecord
       ).filter(GeneralRecord.active==True
       ).filter(GeneralRecord.room_number==args["room_number"]
       ).filter(GeneralRecord.channel==args["channel"])
     try:
       gr = q.one()
     except NoResultFound:
       return dumps((False,))
   else:
     gr = pr.general_record
   members = self._execute_breakup(gr)
   returns = self._construct_room_info(gr, user)
   returns.update({"members": [self._construct_member_info(pr) for pr in members]})
   db_session.commit()
   return dumps((True, returns))

 def _diff_session(self, user):
   """
   make_room(新規ゲーム), join_room(参加表明)のとき、会員のFQDNとIPアドレスが
   前回の記録から変わっているかどうか調べて、
   変わっている場合には何から何に変わったのかを返す。
   """
   diff = {}
   diff_ = False
   sessions = db_session.query(Session
     ).filter(Session.user_id == user.id
     ).order_by(Session.id.desc()
     ).limit(2
     ).all()
   if len(sessions) == 2:
     last, prev = sessions
     if (not None in [last.ipaddr, prev.ipaddr]) and (last.ipaddr != prev.ipaddr):
       diff_ = True
     if last.hostname != prev.hostname:
       diff_ = True
   if diff_:
     diff.update({
       "username": user.name,
       "last_ipaddr": last.ipaddr,
       "last_hostname": last.hostname,
       "last_timestamp": last.timestamp,
       "prev_ipaddr": prev.ipaddr,
       "prev_hostname": prev.hostname,
       "prev_timestamp": prev.timestamp,
     })
   return diff