Python sqlalchemy.exc.SQLAlchemyError() Examples

def post(self):
        self.parser.add_argument("nickname", type=str, location="json", required=True)
        self.parser.add_argument("signature", type=str, location="json", required=True)
        args = self.parser.parse_args()

        profile = ProfilesModel(nickname=args.nickname, signature=args.signature)

        try:
            db.session.add(profile)
            db.session.commit()
        except SQLAlchemyError as e:
            current_app.logger.error(e)
            db.session.rollback()
            return pretty_result(code.DB_ERROR, '数据库错误！')
        else:
            return pretty_result(code.OK) 

def end(self) -> Query:
        """ Get the resulting sqlalchemy `Query` object """
        # The query
        q = self._from_query()

        # Apply every handler
        for handler_name, handler in self._handlers_ordered_for_end_method():
            if not handler.skip_this_handler:
                # Apply the handler
                try:
                    q = handler.alter_query(q, as_relation=self._as_relation)
                # Enrich SqlAlchemy errors with MongoSQL context (because it's very difficult to debug its cryptic messages)
                except sa_exc.SQLAlchemyError as e:
                    # Get model name by backtracing MongoQuery objects
                    model_name = []
                    mq = self
                    while mq is not None:
                        model_name.append(mq.bags.model_name)
                        mq = mq._parent_mongoquery
                    model_name = ' -> '.join(reversed(model_name))

                    # Finally, raise one rich error
                    raise RuntimeQueryError(f'Error processing MongoQuery({model_name}).{handler_name}: {e}') from e

        return q 

def confirm_user(token):
    """ Confirm a user account.
    """
    user_obj = pagure.lib.query.search_user(flask.g.session, token=token)
    if not user_obj:
        flask.flash("No user associated with this token.", "error")
    else:
        user_obj.token = None
        flask.g.session.add(user_obj)

        try:
            flask.g.session.commit()
            flask.flash("Email confirmed, account activated")
            return flask.redirect(flask.url_for("auth_login"))
        except SQLAlchemyError as err:  # pragma: no cover
            flask.flash(
                "Could not set the account as active in the db, "
                "please report this error to an admin",
                "error",
            )
            _log.exception(err)

    return flask.redirect(flask.url_for("ui_ns.index")) 

def reload_data(self):
        """Reloads the configuration from the database

        Returns:
            `None`
        """
        # We must force a rollback here to ensure that we are working on a fresh session, without any cache
        db.session.rollback()

        self.__data = {}
        try:
            for ns in db.ConfigNamespace.all():
                self.__data[ns.namespace_prefix] = {x.key: x.value for x in ns.config_items}

        except SQLAlchemyError as ex:
            if str(ex).find('1146') != -1:
                pass 

def upgrade():
    partitions = (
        'tags', 'resources', 'resource_properties', 'issues',
        'issue_properties', 'auditlog', 'logs', 'emails'
    )
    for table in partitions:
        try:
            op.execute('ALTER TABLE `{}` REMOVE PARTITIONING'.format(table))
        except SQLAlchemyError as ex:
            # Silently ignore errors about removing partitions from a table without partitions
            if str(ex).find('1505') != -1:
                pass

            raise

    # ### commands auto generated by Alembic - please adjust! ###
    op.create_foreign_key('fk_resource_mapping_child', 'resource_mappings', 'resources', ['child'], ['resource_id'], ondelete='CASCADE')
    op.create_foreign_key('fk_resource_mapping_parent', 'resource_mappings', 'resources', ['parent'], ['resource_id'], ondelete='CASCADE')
    op.create_foreign_key('fk_resource_property_resource_id', 'resource_properties', 'resources', ['resource_id'], ['resource_id'], ondelete='CASCADE')
    op.create_foreign_key('fk_resource_account_id', 'resources', 'accounts', ['account_id'], ['account_id'], ondelete='CASCADE')
    op.create_foreign_key('fk_resource_types_resource_type_id', 'resources', 'resource_types', ['resource_type_id'], ['resource_type_id'], ondelete='CASCADE')
    op.create_foreign_key('fk_tag_resource_id', 'tags', 'resources', ['resource_id'], ['resource_id'], ondelete='CASCADE')
    # ### end Alembic commands ### 

def delete(id):
        id = hash_ids.decode(id)
        if not id: abort(404)

        try:
            profile = ProfilesModel.query.get(id[0])
            if not profile: abort(404)

            db.session.delete(profile)
            db.session.commit()
        except SQLAlchemyError as e:
            current_app.logger.error(e)
            db.session.rollback()
            return pretty_result(code.DB_ERROR, '数据库错误！')
        else:
            return pretty_result(code.OK) 

def put(self, id):
        self.parser.add_argument("nickname", type=str, location="json", required=True)
        self.parser.add_argument("signature", type=str, location="json", required=True)
        args = self.parser.parse_args()

        id = hash_ids.decode(id)
        if not id: abort(404)

        try:
            profile = ProfilesModel.query.get(id[0])
            if not profile: abort(404)

            profile.nickname = args.nickname
            profile.signature = args.signature

            db.session.add(profile)
            db.session.commit()
        except SQLAlchemyError as e:
            current_app.logger.error(e)
            db.session.rollback()
            return pretty_result(code.DB_ERROR, '数据库错误！')
        else:
            return pretty_result(code.OK) 

def get(id):
        id = hash_ids.decode(id)
        if not id: abort(404)

        try:
            profile = ProfilesModel.query.get(id[0])
            if not profile: abort(404)
        except SQLAlchemyError as e:
            current_app.logger.error(e)
            db.session.rollback()
            return pretty_result(code.DB_ERROR, '数据库错误！')
        else:
            item = {
                'id': hash_ids.encode(profile.id),
                'nickname': profile.nickname,
                'signature': profile.signature
            }
            return pretty_result(code.OK, data=item) 

def delete(self, *, auto_commit=False):
        """Removes a resource from the database

        Args:
            auto_commit (bool): Automatically commit the transaction. Default: `False`

        Returns:
            `None`
        """
        try:
            db.session.delete(self.resource)
            if auto_commit:
                db.session.commit()
        except SQLAlchemyError:
            self.log.exception('Failed deleting resource: {}'.format(self.id))
            db.session.rollback() 

def usergroup_create():
    """Add a usergroup in the database"""
    # Only POST data are handled
    if request.method != "POST":
        return utils.response("ERROR: POST method is required ", 405)

    # Simplification for the reading
    name = request.form["name"]
    comment = request.form["comment"]

    # Check for required fields
    if not name:
        return utils.response("ERROR: The name is required ", 417)

    # Check unicity for name
    query = db.session.query(usergroup.Usergroup.name)\
        .filter_by(name=name).first()

    if query is not None:
        return utils.response('ERROR: The name "' + name + \
                              '" is already used by another usergroup ', 417)

    g = usergroup.Usergroup(
        name=name,
        comment=comment)
    db.session.add(g)

    # Try to add the usergroup on the database
    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        return utils.response('ERROR: "' + name + '" -> ' + \
                              e.message, 409)

    return utils.response('OK: "' + name + '" -> created', 200) 

def relates_commit(session, username, commitid, issue, app_url=None):
    """ Add a comment to an issue that this commit relates to it. """

    url = "../%s" % commitid[:8]
    if app_url:
        if app_url.endswith("/"):
            app_url = app_url[:-1]
        project = issue.project.fullname
        if issue.project.is_fork:
            project = "fork/%s" % project
        url = "%s/%s/c/%s" % (app_url, project, commitid[:8])

    comment = """ Commit [%s](%s) relates to this ticket""" % (
        commitid[:8],
        url,
    )

    try:
        pagure.lib.query.add_issue_comment(
            session, issue=issue, comment=comment, user=username
        )
        session.commit()
    except pagure.exceptions.PagureException as err:
        print(err)
    except SQLAlchemyError as err:  # pragma: no cover
        session.rollback()
        _log.exception(err) 

def get(self):
        self.parser.add_argument("page_num", type=int, location="args", default=1)
        self.parser.add_argument("page_size", type=int, location="args", default=10)
        args = self.parser.parse_args()

        try:
            profiles = ProfilesModel.query.paginate(args.page_num, args.page_size, error_out=False)
        except SQLAlchemyError as e:
            current_app.logger.error(e)
            db.session.rollback()
            return pretty_result(code.DB_ERROR, '数据库错误！')
        else:
            items = []
            for i in profiles.items:
                items.append(
                    {
                        'id': hash_ids.encode(i.id),
                        'nickname': i.nickname,
                        'signature': i.signature
                    }
                )
            data = {
                'page_num': args.page_num,
                'page_size': args.page_size,
                'total': profiles.total,
                'items': items
            }
            return pretty_result(code.OK, data=data) 

def target_addusergroup():
    """Add a usergroup in the target in the database"""
    # Only POST data are handled
    if request.method != "POST":
        return utils.response("ERROR: POST method is required ", 405)

    # Simplification for the reading
    usergroupname = request.form["usergroupname"]
    targetname = request.form["targetname"]

    # Check for required fields
    if not usergroupname or not targetname:
        return utils.response("ERROR: The usergroupname and targetname are" + \
                              " required ", 417)

    # Usergroup and target have to exist in database
    ug = utils.get_usergroup(usergroupname)
    if not ug:
        return utils.response('ERROR: no usergroup "' + usergroupname + \
                              '" in the database ', 417)

    t = utils.get_target(targetname)
    if not t:
        return utils.response('ERROR: no target "' + targetname + \
                              '" in the database ', 417)

    # Now we can add the user
    t.addusergroup(ug)
    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        return utils.response('ERROR: "' + targetname + '" -> ' + \
                              e.message, 409)

    utils.notif("Users from group" + usergroupname + " can now access " + \
                targetname + ".\n\nAffected users:\n" + \
                str(ug.all_username_list()), "[PaSSHport] " + usergroupname + \
                " can now access " + targetname, request)
    return utils.response('OK: "' + usergroupname + '" added to "' + \
                          targetname + '"', 200) 

def update_user_settings():
    """ Update the user's settings set in the settings page.
    """
    if admin_session_timedout():
        if flask.request.method == "POST":
            flask.flash("Action canceled, try it again", "error")
        return flask.redirect(
            flask.url_for("auth_login", next=flask.request.url)
        )

    user = _get_user(username=flask.g.fas_user.username)

    form = pagure.forms.ConfirmationForm()

    if form.validate_on_submit():
        settings = {}
        for key in flask.request.form:
            if key == "csrf_token":
                continue
            settings[key] = flask.request.form[key]

        try:
            message = pagure.lib.query.update_user_settings(
                flask.g.session, settings=settings, user=user.username
            )
            flask.g.session.commit()
            flask.flash(message)
        except pagure.exceptions.PagureException as msg:
            flask.g.session.rollback()
            flask.flash(msg, "error")
        except SQLAlchemyError as err:  # pragma: no cover
            flask.g.session.rollback()
            flask.flash(str(err), "error")

    return flask.redirect(flask.url_for("ui_ns.user_settings")) 

def _update_db(self, ip, port, delay, alive):
        proxy_item = self.session.query(Proxy).filter(Proxy.ip == ip, Proxy.port == port).all()
        if len(proxy_item):
            # 数据库中已经有这个IP了，更新即可
            proxy_item = proxy_item[0]
            proxy_item.updated_time = datetime.datetime.now()
            proxy_item.times = delay
            proxy_item.is_alive = 1 if alive else 0
            try:
                self.session.add(proxy_item)
                self.session.commit()
            except SQLAlchemyError, e:
                logger.error("Error while update proxy information to database.")
                logger.error(e.message)
                sys.exit(1) 

def new_repo_hook_token(repo, username=None, namespace=None):
    """ Re-generate a hook token for the present project.
    """
    if not pagure_config.get("WEBHOOK", False):
        flask.abort(404)

    repo = flask.g.repo

    form = pagure.forms.ConfirmationForm()
    if not form.validate_on_submit():
        flask.abort(400, description="Invalid request")

    try:
        repo.hook_token = pagure.lib.login.id_generator(40)
        flask.g.session.commit()
        flask.flash("New hook token generated")
    except SQLAlchemyError as err:  # pragma: no cover
        flask.g.session.rollback()
        _log.exception(err)
        flask.flash("Could not generate a new token for this project", "error")

    return flask.redirect(
        flask.url_for(
            "ui_ns.view_settings",
            repo=repo.name,
            username=username,
            namespace=namespace,
        )
        + "#privatehookkey-tab"
    ) 

def revoke_api_user_token(token_id):
    """ Revoke a user token (ie: not project specific).
    """
    if admin_session_timedout():
        flask.flash("Action canceled, try it again", "error")
        url = flask.url_for(".user_settings")
        return flask.redirect(flask.url_for("auth_login", next=url))

    token = pagure.lib.query.get_api_token(flask.g.session, token_id)

    if not token or token.user.username != flask.g.fas_user.username:
        flask.abort(404, description="Token not found")

    form = pagure.forms.ConfirmationForm()

    if form.validate_on_submit():
        try:
            if token.expiration >= datetime.datetime.utcnow():
                token.expiration = datetime.datetime.utcnow()
                flask.g.session.add(token)
            flask.g.session.commit()
            flask.flash("Token revoked")
        except SQLAlchemyError as err:  # pragma: no cover
            flask.g.session.rollback()
            _log.exception(err)
            flask.flash(
                "Token could not be revoked, please contact an admin", "error"
            )

    return flask.redirect(
        flask.url_for("ui_ns.user_settings") + "#nav-api-token"
    ) 

def targetgroup_addusergroup():
    """Add a usergroup in the targetgroup in the database"""
    # Only POST data are handled
    if request.method != 'POST':
        return utils.response("ERROR: POST method is required ", 405)

    # Simplification for the reading
    usergroupname = request.form["usergroupname"]
    targetgroupname = request.form["targetgroupname"]

    # Check for required fields
    if not usergroupname or not targetgroupname:
        return utils.response("ERROR: The usergroupname and " + \
                              "targetgroupname are required ", 417)

    # Usergroup and targetgroup have to exist in database
    ug = utils.get_usergroup(usergroupname)
    if not ug:
        return utils.response('ERROR: no usergroup "' + usergroupname + \
                              '" in the database ', 417)

    tg = utils.get_targetgroup(targetgroupname)
    if not tg:
        return utils.response('ERROR: no targetgroup "' + targetgroupname + \
                              '" in the database ', 417)

    # Now we can add the usergroup
    tg.addusergroup(ug)
    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        return utils.response('ERROR: "' + targetgroupname + '" -> ' + \
                              e.message, 409)

    utils.notif("Users from " + usergroupname + \
               " can now access to the targets from " + targetgroupname + ".",
                "[PaSSHport] " + usergroupname + " added to " + targetgroupname, request)
    return utils.response('OK: "' + usergroupname + '" added to "' + \
                          targetgroupname + '"', 200) 

def save(self, *, auto_commit=True):
        try:
            db.session.add(self.issue)
            if auto_commit:
                db.session.commit()
        except SQLAlchemyError as ex:
            self.log.exception('Failed updating issue: {}'.format(ex))
            db.session.rollback() 

def star_project(repo, star, username=None, namespace=None):
    """ Star or Unstar a project

    :arg repo: string representing the project which has to be starred or
    unstarred.
    :arg star: either '0' or '1' for unstar and star respectively
    :arg username: string representing the user the fork of whose is being
    starred or unstarred.
    :arg namespace: namespace of the project if any
    """

    return_point = flask.url_for("ui_ns.index")
    if flask.request.referrer is not None and pagure.utils.is_safe_url(
        flask.request.referrer
    ):
        return_point = flask.request.referrer

    form = pagure.forms.ConfirmationForm()
    if not form.validate_on_submit():
        flask.abort(400)

    if star not in ["0", "1"]:
        flask.abort(400)

    try:
        msg = pagure.lib.query.update_star_project(
            flask.g.session,
            user=flask.g.fas_user.username,
            repo=flask.g.repo,
            star=star,
        )
        flask.g.session.commit()
        flask.flash(msg)
    except SQLAlchemyError:
        flask.flash("Could not star the project")

    return flask.redirect(return_point) 

def targetgroup_adduser():
    """Add a user in the targetgroup in the database"""
    # Only POST data are handled
    if request.method != "POST":
        return utils.response("ERROR: POST method is required ", 405)

    # Simplification for the reading
    username = request.form["username"]
    targetgroupname = request.form["targetgroupname"]

    # Check for required fields
    if not username or not targetgroupname:
        return utils.response("ERROR: The username and targetgroupname" + \
                              " are required ", 417)

    # Targetgroup and user have to exist in database
    u = utils.get_user(username)
    if not u:
        return utils.response('ERROR: no user "' + username + \
                              '" in the database ', 417)

    tg = utils.get_targetgroup(targetgroupname)
    if not tg:
        return utils.response('ERROR: no targetgroup "' + targetgroupname + \
                              '" in the database ', 417)

    # Now we can add the user
    tg.adduser(u)
    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        return utils.response('ERROR: "' + targetgroupname + '" -> ' + \
                              e.message, 409)

    utils.notif("User " + username + " is now in " + targetgroupname + ".",
                "[PaSSHport] " + username + " joins " + targetgroupname, request) 
    return utils.response('OK: "' + username + '" added to "' + \
                          targetgroupname + '"', 200) 

def update_quick_replies(repo, username=None, namespace=None):
    """ Update the quick_replies of a project.
    """

    repo = flask.g.repo

    if not repo.settings.get("pull_requests", True):
        flask.abort(
            404, description="Pull requests are disabled for this project"
        )

    form = pagure.forms.ConfirmationForm()

    if form.validate_on_submit():
        quick_replies = [
            w.strip()
            for w in flask.request.form.getlist("quick_reply")
            if w.strip()
        ]
        try:
            repo.quick_replies = quick_replies
            flask.g.session.add(repo)
            flask.g.session.commit()
            flask.flash("List of quick replies updated")
        except SQLAlchemyError as err:  # pragma: no cover
            flask.g.session.rollback()
            flask.flash(str(err), "error")

    return flask.redirect(
        flask.url_for(
            "ui_ns.view_settings",
            username=username,
            repo=repo.name,
            namespace=namespace,
        )
        + "#quickreplies-tab"
    ) 

def targetgroup_delete(name):
    """Delete a targetgroup in the database"""
    if not name:
        return utils.response("ERROR: The name is required ", 417)

    # Check if the name exists
    query = db.session.query(targetgroup.Targetgroup.name)\
        .filter_by(name=name).first()

    if query is None:
        return utils.response('ERROR: No targetgroup with the name "' + \
                              name + '" in the database.', 417)

    tg = db.session.query(
            targetgroup.Targetgroup).filter(
            targetgroup.Targetgroup.name == name)
    tg[0].prepare_delete()
    tg.delete()

    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        return utils.response('ERROR: "' + name + '" -> ' + \
                              e.message, 409)

    return utils.response('OK: "' + name + '" -> deleted', 200) 

def delete_report(repo, username=None, namespace=None):
    """ Delete a report from a project.
    """

    repo = flask.g.repo

    form = pagure.forms.ConfirmationForm()

    if form.validate_on_submit():
        report = flask.request.form.get("report")
        reports = repo.reports
        if report not in reports:
            flask.flash("Unknown report: %s" % report, "error")
        else:
            del reports[report]
            repo.reports = reports
            try:
                flask.g.session.add(repo)
                flask.g.session.commit()
                flask.flash("List of reports updated")
            except SQLAlchemyError as err:  # pragma: no cover
                flask.g.session.rollback()
                flask.flash(str(err), "error")

    return flask.redirect(
        flask.url_for(
            "ui_ns.view_settings",
            username=username,
            repo=repo.name,
            namespace=namespace,
        )
        + "#reports-tab"
    ) 

def remove_tag(repo, username=None, namespace=None):
    """ Remove the specified tag, associated with the issues, from the project.
    """
    repo = flask.g.repo

    form = pagure.forms.DeleteIssueTagForm()
    if form.validate_on_submit():
        tags = form.tag.data
        tags = [tag.strip() for tag in tags.split(",")]

        msgs = pagure.lib.query.remove_tags(
            flask.g.session, repo, tags, user=flask.g.fas_user.username
        )

        try:
            flask.g.session.commit()
            for msg in msgs:
                flask.flash(msg)
        except SQLAlchemyError as err:  # pragma: no cover
            flask.g.session.rollback()
            _log.error(err)
            flask.flash("Could not remove tag: %s" % ",".join(tags), "error")

    return flask.redirect(
        flask.url_for(
            "ui_ns.view_settings",
            repo=repo.name,
            username=username,
            namespace=repo.namespace,
        )
        + "#projecttags-tab"
    ) 

def delete(self, *, auto_commit=True):
        try:
            db.session.delete(self.issue)
            if auto_commit:
                db.session.commit()
        except SQLAlchemyError:
            self.log.exception('Failed deleting issue: {}'.format(self.id))
            db.session.rollback() 

def targetgroup_create():
    """Add a targetgroup in the database"""
    # Only POST data are handled
    if request.method != "POST":
        return utils.response("ERROR: POST method is required ", 405)

    # Simplification for the reading
    name = request.form["name"]
    comment = request.form["comment"]

    # Check for required fields
    if not name:
        return utils.response("ERROR: The name is required ", 417)

    # Check unicity for name
    query = db.session.query(targetgroup.Targetgroup.name)\
        .filter_by(name=name).first()

    if query is not None:
        return utils.response('ERROR: The name "' + name + \
                              '" is already used by another targetgroup ', 417)

    t = targetgroup.Targetgroup(
        name=name,
        comment=comment)
    db.session.add(t)

    # Try to add the targetgroup in the database
    try:
        db.session.commit()
    except exc.SQLAlchemyError as e:
        return utils.response('ERROR: "' + name + '" -> ' + \
                              e.message, 409)

    return utils.response('OK: "' + name + '" -> created', 200) 

def create(cls, account_id, resource_id, action, timestamp, metrics):
        """ Set properties for an enforcement action"""

        enforcement = Enforcements()
        enforcement.account_id = account_id
        enforcement.resource_id = resource_id
        enforcement.action = action
        enforcement.timestamp = timestamp
        enforcement.metrics = metrics

        try:
            db.session.add(enforcement)

        except SQLAlchemyError as e:
            logging.error('Could not add enforcement entry to database. {}'.format(e)) 

def delete(self, *, auto_commit=True):
        try:
            db.session.delete(self.account)
            if auto_commit:
                db.session.commit()
        except SQLAlchemyError:
            self.log.exception('Failed deleting account: {}'.format(self.id))
            db.session.rollback() 

def save(self, *, auto_commit=True):
        try:
            db.session.add(self.account)
            if auto_commit:
                db.session.commit()
        except SQLAlchemyError as ex:
            self.log.exception('Failed updating account: {}'.format(ex))
            db.session.rollback()