database field created by a relational field in flask sqlalchemy models

Question

say...
Nation has states has cities has schools has students.

if my database is set-up like that, and I have a field in schools that is a boolean goodOrBadStudent, is there an easy way to calculate how many good students there are in each table?

NationGoodStudents=y
StateGoodStudents=x
and so on... ?

this is my real data but the idea is the same

class Projects(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    total = db.Column(db.Integer)
    percentDeadline = db.Column(db.Integer)
    percent = db.Column(db.Integer)
    name = db.Column(db.String(50))
    goals = db.relationship('Goals', backref='proj',
                                lazy='dynamic')
    def __repr__(self):
        return '<Project %r>' % (self.name)

class Goals(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    goal = db.Column(db.String(50))
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'))
    strategies = db.relationship('Strategies', backref='goa',
                                lazy='dynamic')

    def __repr__(self):
        return '<Goals %r>' % (self.goal)
class Strategies(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    strategy = db.Column(db.String(50))
    goal_id = db.Column(db.Integer, db.ForeignKey('goals.id'))
    tasks = db.relationship('Tasks', backref='strat',
                                lazy='dynamic')
    def __repr__(self):
        return '<Strategy %r>' % (self.strategy)

class Tasks(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    task = db.Column(db.String(50))
    note = db.Column(db.String(400))  
    complete = db.Column(db.Boolean())
    staff = db.Column(db.String(30))
    deadline = db.Column(db.Date)
    completeDate = db.Column(db.Date)
    created = db.Column(db.Date)
    strategy_id = db.Column(db.Integer, db.ForeignKey('strategies.id'))

    def __repr__(self):
        return '<Tasks %r>' % (self.task)

so where would i do the calculation? I hope not in the view, because that is cumbersome.

here is what i started doing to get the percents, but probably better to pass x y and total

P=models.Projects.query.all()
for p in P:  
    y=0     
    x=0
    total=0
    project=models.Projects.query.filter_by(name=p.name).first() 
    G=project.goals.all()
    for g in G:
        pgoal=models.Goals.query.filter_by(goal=g.goal).first() 
        S=pgoal.strategies.all()
        for s in S:
            pstrat=models.Strategies.query.filter_by(strategy=s.strategy).first() 
            T=pstrat.tasks.all()
            for t in T:
                if t.completeDate != None and t.deadline != None:
                    deadlineDiff= (t.deadline - t.completeDate) > datetime.timedelta(days=0)
                    print deadlineDiff, ' this is the deadlineDiff variable for ',t.task
                    if deadlineDiff==True:
                        y+=1
                total+=1
                if t.complete==True:
                    x+=1
    y=float(y*1.00)
    x=float(x*1.00)
    total=float(total*1.00)
    if x == 0 or total ==0:
        p.percent=0
        db.session.commit()
    else:
        p.percent=float(x/total*100.0)
        db.session.commit()   
    if y == 0 or x ==0:
        p.percentDeadline=0
        db.session.commit()
    else:
        p.percentDeadline=float(y/x*100.0)
        db.session.commit()

I guess i would commit x, y and total at each level?

van · Accepted Answer · 2014-05-14 04:51:14Z

1

In my opinion, such calculation is best performed directly on the SQL side. Using sqlalchemy query below shall return a list of tuples (project_id, x, y, total) as these are defined in your sample code. Given you have all proper indices on your database, this query should be very fast, and you could use this to calculate the progress of each given project on the fly without even storing the results in the database.

q_sum = (session.query(
            Projects.id.label("project_id"),
            func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"),
            func.sum(case([(and_(
                Tasks.deadline != None,
                Tasks.completeDate != None,
                Tasks.deadline > Tasks.completeDate), 1)],
                else_=0)).label("y"),
            func.count(Tasks.id).label("total"),
            )
        .join(Goals, Projects.goals)
        .join(Strategies, Goals.strategies)
        .join(Tasks, Strategies.tasks)
        .group_by(Projects.id)
        )

# (project_id, x, y, total)
for p in q_sum:
    print(p)

If you need to get this only for specific project, just add .filter(Project.id == my_project_id) to the query.

answered May 14, 2014 at 4:51

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chet Meinzer Over a year ago

love this code. i'm having trouble with implementation. is this code run with the model or view? how is it meant to be run (do i create a new engine and Session, or can i use session=Session()?) also, i need from sqlalchemy.sql import func & from sqlalchemy import and_. After all that, I’m failing with error "case not defined". i'm using sqlite. does that matter? Maybe you can point me to a doc?

van Over a year ago

1) model or view: more on the model (or controller); 2) how to run: use existing session (db.session instead of my session; as I did not use Flask for this test code, i do not have same namespaces as you); 3) yes, you need import all 3; but again, you can replace my code with namespaces for those provided by Flask: db.case(...), db.and_(...) and db.func.sum(...). After that it should work.

Collectives™ on Stack Overflow

database field created by a relational field in flask sqlalchemy models

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related