Create a NimbusDatabase function to handle statistical/aggregation questions

[mfekadu]

Objective

Create a NimbusDatabase function to handle statistical/aggregation questions like the following...
How many sections of CSC 480 are offered this quarter?
How many teachers are interested in Artificial Intelligence?

Examples of aggregations

• total count ("how many of X?")
• unique count ("how many kinds of X?")
• date range ("Between what times does X happen?")
• earliest date ("When is the earliest section of COURSE?")
• latest date ("When is the latest section of COURSE?")
• boolean count (total count true / total count false) ("How often is X true?")
• boolean satisfiability ("Is X always true?")

Key Result

Commit code to the NimbusMySQLAlchemy class in database_wrapper.py that can generally answer any aggregation question

Details

Relevant Code

How many courses are there in the database? = 178

>>> from database_wrapper import NimbusMySQLAlchemy
>>> db = NimbusMySQLAlchemy()
initialized database session
initialized NimbusMySQLAlchemy
NimbusMySQLAlchemy closed
>>> db.session.query(db.Courses).count()
178

How many UNIQUE courses are there in the database? = 178

>>> from database_wrapper import NimbusMySQLAlchemy
>>> db = NimbusMySQLAlchemy()
initialized database session
initialized NimbusMySQLAlchemy
NimbusMySQLAlchemy closed
>>> db.session.query(db.Courses).distinct().count()
178

What is the deptartment, courseNum, units of any course with the most units?

>>> db.session.query(db.Courses.dept).add_column(db.Courses.courseNum).add_column(db.Courses.units).distinct().order_by(db.Courses.units.desc()).first()
('CPE', 494, '6')

What is the deptartment, courseNum, units of any course with the least units?

>>> db.session.query(db.Courses.dept).add_column(db.Courses.courseNum).add_column(db.Courses.units).distinct().order_by(db.Courses.units.asc()).first()
('CPE', 100, '1')

What are the CSC480 sections?

>>> db.session.query(db.Sections.section_name).add_column(db.Sections.instructor).filter(db.Sections.section_name.contains("480")).all()
[('CSC 480_06', 'Kauffman, Daniel Alexander')]

How many?

>>> db.session.query(db.Sections.section_name).add_column(db.Sections.instructor).filter(db.Sections.section_name.contains("480")).count()
1