# Querying via Script 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: {% content-ref url="querying-the-database" %} [querying-the-database](https://guide.fariaedu.com/professional-learning-community-mbpy/6VBx07Zj330EYGI5zHno/querying-and-tranformations/querying-the-database) {% endcontent-ref %} 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.