Querying via Script

Creating convenient scripts

The prevoius section showed how to use the command line to query the database and get some output on the terminal and save it as a CSV:

Querying the Database

However, re-using the query later would be quite a hassle to have to type it again. In this section, we will use the mbpy script group of commands save our query and easily re-execute it.

mbpy scripts --help

╭─ Commands ────────────────────────────────────────────────────────────────────────╮
│ list  Show all saved queries                                                      │
│ run   Execute a saved query                                                       │
╰───────────────────────────────────────────────────────────────────────────────────╯

By default, it comes with a few useful scripts, which we can view:

mbpy scripts list

╭─ Scripts ─────────────────────────────────────────────────────────────────────────╮
│ students_id --group health-checks                                                 │
│ students_parents --group health-checks                                            │
│ students_grade_year_group --group health-checks                                   │
│ reflections --group export-helpers                                                │
│ search-students-by-name                                                           │
│ school                                                                            │
╰───────────────────────────────────────────────────────────────────────────────────╯

The last one outputs this:

mbpy scripts run school

       │ timezone                           │ subdomain          │ name
╶──────┼────────────────────────────────────┼────────────────────┼──────────────────╴
     0 │ Asia/Kuala_Lumpur                  │ f1                 │ FariaOne

The search-students-by-name looks interesting, let's try it:

mbpy scripts run search-students-by-name Happy

                              Column info for dataframe
                        ╷         ╷                ╷
                        │ type    │ count non-null │ row 0
╶───────────────────────┼─────────┼────────────────┼────────────────────────────────╴
                   role │ object  │ 1              │ Student
               timezone │ object  │ 1              │ Asia/Kuala_Lumpur
           graduated_on │ object  │ 0              │ None
           withdrawn_on │ object  │ 0              │ None
              full_name │ object  │ 1              │ Happy Student
             student_id │ object  │ 1              │ HappyStudent
                  state │ object  │ 0              │ None
           display_name │ object  │ 1              │ Student, Happy
          year_group_id │ float64 │ 1              │ 12014897.0
             first_name │ object  │ 1              │ Happy
        graduating_year │ int64   │ 1              │ 2023
             updated_at │ object  │ 1              │ 2022-11-16T16:21:45.000+08:00

These scripts we are running are actually simple text files whose contents have queries built just like in the previous section. For example, the search-students-by-name script is this:

mbpy scripts run search-students-by-name Happy

executes this:

mbpy \
  EXTRACT from-db --table students \
  PRINT \
    query --where 'display_name.str.contains("{0}")'

The above also shows an additional instrumentation that is available for these saved queries, which is that you can use the {0} and {1} to refer to additional arguments passed to the script. If you pass don't pass any, or there is some other problem, mbpy will show you with a useful error message:

mbpy scripts run search-students-by-name

mbpy \
        EXTRACT from-db --table students \
        PRINT \
                query --where 'display_name.str.contains("{0}")'

╭─ Index Error ─────────────────────────────────────────────────────────────────────╮
│ The script is expecting arguments. Please review the code above.                  │
╰───────────────────────────────────────────────────────────────────────────────────╯

You can also associate multiple scripts with one command, using the --group feature. For example, there is a "health check" group that performs three queries, and outputs the results. You can execute all of them this way:

mbpy scripts run health-checks

                             Students without student_id

╶───────────────────────────────────────────────────────────────────────────────────╴
  Empty table

                              0 records with 5 columns

                      Active students with no parents assigned
       ╷          ╷              ╷          ╷                          ╷
       │ mb_id    │ grade_number │ archived │ student                  │ has_parent
╶──────┼──────────┼──────────────┼──────────┼──────────────────────────┼────────────╴
   173 │ 14351728 │ 2            │ False    │ Rogers, Norville         │ False
  1914 │ 14553487 │ 2            │ False    │ Student1, Test (Wally)   │ False
       │          │              │          │ Walter                   │
       ╵          ╵              ╵          ╵                          ╵
                              2 records with 5 columns

           Students with either no grade, no year group, or no hr teacher
      ╷          ╷          ╷              ╷              ╷           ╷
      │ mb_id    │ archived │ student      │ has_hr_teac… │ has_grade │ has_year_g…
╶─────┼──────────┼──────────┼──────────────┼──────────────┼───────────┼─────────────╴
    1 │ 14235240 │ True     │ Student,     │ False        │ True      │ False
      │          │          │ Happier      │              │           │
  958 │ 14553487 │ False    │ Student1,    │ False        │ True      │ False
      │          │          │ Test (Wally) │              │           │
      │          │          │ Walter       │              │           │
      ╵          ╵          ╵              ╵              ╵           ╵

It output enough information to let me see that every student has a student_id assigned, but there are two students in Grade 1, that aren't archived, that do not have any parents associated. Also, there are two other students that don't have a homeroom teacher, nor a year group associated. Better fix that.

In order to add a script to our command, we simply navigate to the src/scripts directory in the codebase, and add the text file there. If you want to group more than one command under one group, simply create a directory.

This is the current structure:

mbpy/
├─ scripts/
│  ├─ school.mbpy
│  ├─ search-students-by-name.mbpy
│  ├─ health-checks/
│  │  ├─ students_grade_year_group.mbpy
│  │  ├─ students_id.mbpy
│  │  ├─ students_parents.mbpy

Each script needs to end with .mbpy in order to be detected by the application.