Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

Using Peewee ORM to generate data for graphs

Course Queries Syllabus Queries
Max. 2000 characters
Replies

usr_profile.png
Arminder Gill

User

( 5 months ago )


I'm building a web app which allows professors to input assignments for students to follow along with, an interactive syllabus if you will. One of the sections of a course page displays progress.

I have built a pie graph component which I'd like to populate with data:

pie_chart(
    title, # name of chart
    percent, # percentage of assignments completed
    count, # how many assignments completed
    total # how many assignments total
)

I'm using the Peewee ORM to retrieve this data from my assignments table:

class Type(BaseModel):
    name = CharField() # Homework, Test, Final, etc.

class Assignment(BaseModel):
    name = CharField()
    due_date = DateField()
    type = ForeignKeyField(Type)
    course = ForeignKeyField(Course)

I need the following from the database and I'm not sure how to accomplish it with Peewee. Getting the name, and total should be simple. But I need to compare the due_date to today's date to see how many of the assignments are completed.

Name Total Completed

Final 2 0 Homework 23 12 Test 4 2

My pie chart output will look something like this if it matters:

Homework
|XXX-------|
3 of 10 complete

UPDATE

I've got a query that does almost everything I need. Can anyone help me take it the rest of the way?

Here's the query:

select t.name,
        count(a.type_id) as total,
        (
            select count(id)
            from assignment a
            where a.course_id = 7
            and a.user_id = 3
            and a.due_date < date()
            group by a.type_id
            order by a.type_id
        ) as completed
from assignment a
inner join type t on t.id = a.type_id
where a.course_id = 7
and a.user_id = 3
group by a.type_id
order by a.type_id

And here's the result using the sample data below:

Homework, 8, 6
Test, 4, 6
Final, 2, 6

This is really close, but I would expect the completed column to be specific to the assignment type.

Here's some sample data from the Assignment table

id name
------------------------
9, Chapter 1, 2014-11-01
10, Chapter 2, 2014-11-08
11, Test on chapter 1-2, 2014-11-15
12, Chapter 3, 2014-
								 								 
							usr_profile.png
							
Nageshwer Reddy

User

( 5 months ago )

The great thing about dates in python, and peewee in particular is that you can compare them in a pretty intuitive way. Behind the scenes dates are typically represented as number of seconds since some date in 1970. Here's an example of querying by date with peewee:

today = datetime.today()
query = Tweet.update(is_published=True).where(Tweet.creation_date < today)
query.execute()

In this example the Tweet class is just one with a boolean is_published attribute, and a datetime creation_date attribute.

Something closer to your example would be:

today = datetime.today()
query = Assignment.select().where(Assignment.due_date < today)

But I'll try not to completely give the answer away.

Hope that helps, let me know if you have any questions.

what's your interest