Flask-SQLAlchemy Sum of a Column of a Relationship

Question

I do have 2 classes like these =>

class User(db.Model):
    __tablename__ = "user"
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique=True, nullable=False)
    password = db.Column(db.String(77), unique=False, nullable=False)
    server_limit = db.Column(db.Integer, unique=False, nullable=False, server_default="4")
    servers = db.relationship('Server', backref='owner', lazy='dynamic')

class Server(db.Model):
    __tablename__ = "server"
    server_id = db.Column(db.Integer, primary_key=True)
    server_admin = db.Column(db.Integer, db.ForeignKey("user.user_id"))
    server_port = db.Column(db.Integer, unique=False, nullable=False)
    server_slot = db.Column(db.Integer, unique=False, nullable=False, server_default="32")

Now Im trying to get sum of server_slot column where for example user_id is 1.

I know there is questions with accepted answer about this but the difference is Im trying to do it with servers ( db.relationship ) that I assigned in my User class.

I did it with an alternative method that I created for User class =>

def used(self):
    return db.session.execute("SELECT SUM(server.server_slot) FROM server WHERE server_admin={}".format(self.user_id)).scalar()

How can I do it using db.session.query() ? Im looking for something that I can get it from db.session.query(User).all()

I dont want to use db.session.query(db.func.sum(Server.server_slot)).filter_by(server_admin=self.user_id).scalar() Cause Im passing a list to my Flask page, The list is made by db.session.query(User).all() so I can iterate over it using a for loop inside my Jinja2 Template and show each user information in a list like this =>

{% for user in users %}
    <td>user.username</td>
    <td>user.server_limit</td>
    <td>...</td>
    <td>user.used_slots()</td>
{% endfor %}

I can use user.servers.value("server_slot") but it returns only first server's server_slot, I also tried to iterate over user.servers.all() so I could sum their server_slot inside a nested loop, but I can't assign variables any value inside of a loop and get it outside the loop.

Let me know if my question is not clear enough (Cause I know it might be).

could you not do `db.session.query(Server).filter(server_admin==user_id).all()` ? — joppich, Jan 31 '18 at 10:14
@joppich no I want to get sum of server_slot for each user, using User's servers relation — DarkSuniuM, Jan 31 '18 at 10:36
Please don't use string formatting for passing values as arguments to SQL queries. — Ilja Everilä, Feb 01 '18 at 21:51
@IljaEverilä Im passing `users_list` to my flask page this query `db.session.query(User).all()`, In my flask page Im rendering users list with a for loop, like => `{% for user in users_list %}user.usernameuser.email...{% endfor %}`, somehow I need to get sum of server_slot column by `user` in the for loop, I could get first server only instead of sum of them, also I could do math in my loop to get the sum, a nested for loop inside of that for second query that u pointed to, the problem is I can't assign the variable new value and print it outside the loop. — DarkSuniuM, Feb 01 '18 at 23:58
(Probably) related: https://stackoverflow.com/questions/41152973/sqlalchemy-sum-all-child-column-attributes-while-querying-parent, https://stackoverflow.com/questions/21271382/sqlalchemy-join-with-sum-and-count-of-grouped-rows — Ilja Everilä, Feb 02 '18 at 11:06
Whatever you do, avoid def used(self): return db.session.execute("SELECT SUM(server.server_slot) FROM server WHERE server_admin={}".format(self.user_id)).scalar() Since you are creating a security hole in your application. A user could send a malformed user_id value and gain access to your DB. Read about SQL injection and avoid writing SQL by manufacturing strings. — Gastón Avila, Jul 08 '18 at 15:41
I was using it for test and it was just something in question that I didn't really used in my code. I just used to implent the question better — DarkSuniuM, Jul 08 '18 at 19:49
Please use params instead of formating a string then as it is misleading and could cause other people to use it. — Gastón Avila, Jul 10 '18 at 14:10

score 5 · Answer 1 · answered Feb 02 '18 at 13:50

Define a hybrid property/expression on your User model.

A simple self-contained example (I've simplified your models):

import random
from select import select
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
from sqlalchemy.ext.hybrid import hybrid_property

app = Flask(__name__)

# Create in-memory database
app.config['DATABASE_FILE'] = 'sample_db.sqlite'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + app.config['DATABASE_FILE']
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = "user"
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique=True, nullable=False)
    servers = db.relationship('Server', backref='owner', lazy='dynamic')

    @hybrid_property
    def server_slot_count(self):
        return sum(server.server_slot for server in self.servers)

    @server_slot_count.expression
    def server_slot_count(cls):
        return (
            select([func.sum(Server.server_slot)]).
            where(Server.server_admin == cls.user_id).
            label('server_slot_count')
        )


class Server(db.Model):
    __tablename__ = "server"
    server_id = db.Column(db.Integer, primary_key=True)
    server_admin = db.Column(db.Integer, db.ForeignKey("user.user_id"))
    server_slot = db.Column(db.Integer, unique=False, nullable=False, server_default="32")


@app.route('/')
def index():
    html = []
    for user in User.query.all():
        html.append('User :{user}; Server Count:{count}'.format(user=user.username, count=user.server_slot_count))

    return '<br>'.join(html)


def build_sample_db():
    db.drop_all()
    db.create_all()

    for username in ['DarkSuniuM', 'pjcunningham']:

        user = User(
            username=username,
        )
        db.session.add(user)
        db.session.commit()
        for slot in random.sample(range(1, 100), 5):
            server = Server(
                server_admin=user.user_id,
                server_slot=slot
            )
            db.session.add(server)

        db.session.commit()


if __name__ == '__main__':
    build_sample_db()
    app.run(port=5000, debug=True)

Your User model now has a property server_slot_count.

{% for user in users %}
    <td>user.username</td>
    <td>user.server_limit</td>
    <td>...</td>
    <td>user.server_slot_count</td>
{% endfor %}

It's exactly something like my alternative method that I pointed to, instead of a method, you used a wrapper, and instead of a SQL Query, you used sqlalchemy methods.. As I said I want to do is using `db.relationship` — DarkSuniuM, Feb 03 '18 at 20:03

DarkSuniuM · Accepted Answer · 2019-03-19T04:22:59.527

-3

Now I believe it can't be done in the way I wanted.

edited Mar 19 '19 at 04:22

answered Jul 01 '18 at 16:04

DarkSuniuM

2,523
2
26
43

Flask-SQLAlchemy Sum of a Column of a Relationship

2 Answers2