时间戳

首页 > 解决方案 > 使用 Flask-SQLAlchemy 优化 Flask 中的 ORM 查询

python - 使用 Flask-SQLAlchemy 优化 Flask 中的 ORM 查询

问题描述

我使用 Django 和 Flask 编写了相同的项目。整个代码都可以在我的 Github 帐户上找到。该网站是一个基于问答的小型问答网站（CTF 格式，问题非常简单）。以下是链接：

我的问题是关于在 SQLAlchemy 或 Flask-SQLAlchemy 中优化 ORM 查询。

为了更好地理解，我将尽我所能编写表的架构。

Teams (id, team_name, email, phone, password)
Questions (id, name, body, hint, answer, points, visible)
Submissions (id, team(fk), question(fk), timestamp)

如果你们中的任何人想查看实际代码，它们是：
For Django - Question & Submission , Team
For Flask - Question , Team , Submission

对于其中两条路线/submissions和/leaderboard，我必须使用 ORM 编写某些查询。这是页面的样子： 排行榜 提交

对于 Django，查询看起来相当不错（或者至少我是这么认为的：P）

def submissions(request):
    all_submissions = Question.objects \
        .values('id', 'name') \
        .order_by('id') \
        .annotate(submissions=Count('submission'))

    print(all_submissions.query)

    return render(request, 'questions/submissions.html', {
        'submissions': all_submissions
    })

def leaderboard(request):
    team_scores = Team.objects \
        .values('team_name') \
        .order_by('team_name') \
        .annotate(score=Coalesce(Sum('submission__question__points'), 0)) \
        .order_by('-score')

    print(team_scores.query)

    return render(request, 'questions/leaderboard.html', {
        'team_scores': team_scores,
    })

原始 SQL 查询如下所示：

SELECT "teams_team"."team_name", COALESCE(SUM("questions_question"."points"), 0) AS "score" FROM "teams_team" LEFT OUTER JOIN "questions_submission" ON ("teams_team"."id" = "questions_submission"."team_id") LEFT OUTER JOIN "questions_question" ON ("questions_submission"."question_id" = "questions_question"."id") GROUP BY "teams_team"."team_name" ORDER BY "score" DESC

SELECT "questions_question"."id", "questions_question"."name", COUNT("questions_submission"."id") AS "submissions" FROM "questions_question" LEFT OUTER JOIN "questions_submission" ON ("questions_question"."id" = "questions_submission"."question_id") GROUP BY "questions_question"."id", "questions_question"."name" ORDER BY "questions_question"."id" ASC

这是对我的问题的一个非常冗长的介绍。

我的问题从这里开始，我似乎无法使用 SQLAlchemy ORM 编写这个或类似的查询，并且 PyCharm 没有提供正确的代码完成/建议。

对于 Flask，这些是我的函数的样子：

def get_team_score(team):
    team_submissions = Submission.query.filter_by(team_id=team.id)
    score = sum(
        submission.question.points
        for submission in team_submissions
    )

    return score

@question_blueprint.route('/submissions')
def submissions():
    all_submissions = [
        {
            'id': q.id,
            'name': q.name,
            'submissions': Submission.query.filter_by(question_id=q.id).count()
        }
        for q in Question.get()  # fetch all Question rows
    ]
    return render_template('submissions.html', **{
        'submissions': all_submissions
    })

@question_blueprint.route('/leaderboard')
def leaderboard():
    team_scores = [
        {
            'team_name': team.team_name,
            'score': get_team_score(team)
        }
        for team in Team.query.filter_by()
    ]
    return render_template('leaderboard.html', **{
        'team_scores': team_scores
    })

查询没有优化，我想知道是否可以编写像 django-orm 这样的优雅查询，而无需编写原始 SQL 语句。如果可能的话，我想对这个问题中提到的这两条路线进行一些很好的优化查询。

呸。

标签： pythondjangoflaskflask-sqlalchemydjango-orm

解决方案

您可以像这样更改上面的代码。

@question_blueprint.route('/leaderboard')
def leaderboard():
    team_scores =\
        Team.query.join(
            Submission,
        ).join(
            Question,
        ).with_entities(
            Team.team_name,
            func.sum(Question.points).label("score")
        ).all()

    return render_template('leaderboard.html', **{
        'team_scores': team_scores
    })

在下面的这个片段中，你加入 Team -> Submission -> Question，然后因为你只需要团队名称和基于每次提交的问题的总分，所以你只需要with_entities。

@question_blueprint.route('/submissions')
def submissions():
    all_submissions = Question.query.join(
        Submission
    ).with_entities(
        Question.id,
        Question.name,
        func.count().label("submissions")
    ).all()

    return render_template('submissions.html', **{
        'submissions': all_submissions
    })

要查询提交的问题，你可以像我上面添加的那样做类似的查询，但不是计算你只想计算行的总和，所以你只需将其更改为 func.sum

推荐阅读