students_db.py

# -*- coding: utf-8 -*-
"""students_DB.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1XxpYEyLKcMs32H1NKyJoaJPMQR-JaQxq

Creating a database and then loading the student.json dataset.
Inserting the students record into the collection.
"""

import pymongo
import json

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]



with open("students.json") as students:
  Data = [json.loads(i) for i in students]


mycol.insert_many(Data)

"""1)      Find the student name who scored maximum scores in all (exam, quiz and homework)?

"""

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]

query = mycol.aggregate([
    {"$unwind": "$scores"},
    {"$group": {"_id": "$_id", "name": {"$first": "$name"}, "Total": {"$sum": "$scores.score"}}},
    {"$sort": {"Total": -1}},
    {"$limit": 1}
])

for i in query:
    print(i)

"""2)      Find students who scored below average in the exam and pass mark is 40%?"""

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]

query = {'scores.type': 'exam', 'scores.score': {'$gt': 40, '$lt': 60}}

myquery = mycol.aggregate([
    {'$unwind': '$scores'},
    {"$match": query}
])

for i in myquery:
    print(i)

"""3)      Find students who scored below pass mark and assigned them as fail, and above pass mark as pass in all the categories."""

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]

myquery = mycol.aggregate(
    [{"$set":
          {"scores":
               {"$arrayToObject":
                    [{"$map":
                          {"input": "$scores",
                           "as": "s",
                           "in": {"k": "$$s.type", "v": "$$s.score"}}}]}}},
     {"$project":
         {
             "_id": 1,
             "name": 1,
             "result": {
                 "$cond":
                     {"if": {"$and": [{"$gte": ["$scores.exam", 40]}, {"$gte": ["$scores.quiz", 40]},
                                      {"$gte": ["$scores.homework", 40]}]
                             },
                      "then": "pass",
                      "else": "fail"
                      }
             }
         }
     }
     ])

for i in myquery:
    print(i)

"""4)       Find the total and average of the exam, quiz and homework and store them in a separate collection.

"""

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]

total_avg = db.total_avg

myquery = mycol.aggregate([
    {"$unwind": "$scores"},
    {"$group":
        {
            "_id": "$_id",
            "name": {"$first": "$name"}
            ,
            "Total": {"$sum": "$scores.score"},
            "Average": {"$avg": "$scores.score"}
        }
    },
    {"$sort": {"_id": 1}}

])

myquery1 = []
for i in myquery:
    myquery1.append(i)
    print(i)

total_avg.insert_many(myquery1)

"""
5)      Create a new collection which consists of students who scored below average and above 40% in all the categories."""

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]

Average_Candi = db.Average_Candi

myquery = mycol.aggregate(
    [{"$match":
          {"$expr":
               {"$and":
                    [{"$gt": [{"$min": "$scores.score"}, 40]},
                     {"$lt": [{"$max": "$scores.score"}, 70]}
                     ]
                }
           }
      }])

myquery1 = []
for i in myquery:
    myquery1.append(i)
    print(i)

Average_Candi.insert_many(myquery1)

"""
6)      Create a new collection which consists of students who scored below the fail mark in all the categories.
"""

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]

fail = db.fail

query = {}
data = mycol.aggregate(
    [{"$match":
          {"$expr":

               {"$lt": [{"$max": "$scores.score"}, 40]}

           }
      }])

faila = []
for i in data:
    faila.append(i)
    print(i)

fail.insert_many(faila)

"""
7)      Create a new collection which consists of students who scored above pass mark in all the categories."""

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017")
db = myclient["mydb"]
mycol = db["customers"]

passed = db.passed

query = {}
myquery = mycol.aggregate(
    [{"$match":
          {"$expr":

               {"$gt": [{"$min": "$scores.score"}, 40]}

           }
      }])

passed1 = []
for i in myquery:
    passed1.append(i)
    print(i)

passed.insert_many(passed1)