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:
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 run school
│ timezone │ subdomain │ name
╶──────┼────────────────────────────────────┼────────────────────┼──────────────────╴
0 │ Asia/Kuala_Lumpur │ f1 │ FariaOne
The search-students-by-name looks interesting, let's try it:
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:
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.