To query the database, mbpy needs the data imported first, which can be accomplished with the Importing commands.
Extracting
The extract command has a variety of means to load data, either from the API by querying it directly, or by querying the SQlite database. We can even read in CSV files.
mbpy extract --help
Usage: mbpy extract [OPTIONS] COMMAND [ARGS]...
Start ELT sequence. See mbpy extract --help for extaction commands
╭─ Commands ────────────────────────────────────────────────────────────────────────╮
│ from-api Pass a name like students, classes, academic_info_grades │
│ from-csv Define the path to csv file │
│ from-db Select entities (and associated entities), fields │
│ from-db-connect use raw sql │
│ from-excel Define the path to csv file │ │
│ from-external-api Call out to a list endpoint. Default values work for OA V3 │
│ students list endpoint. For OA, use --bearer and pass in the │
│ bearer via environment variable token after /oauth/token call. │
│ FIXME: Add this here │
│ from-pipeline Use pipelines as an extraction tool for more sophisticated │
│ data management. │
╰───────────────────────────────────────────────────────────────────────────────────╯
We'll use the from-db command to demonstrate how it works, but as you can see there are some other powerful features.
There is extensive help documentation included with mbpy, available here:
Let's find out how to use this command:
mbpy extract from-db --help
╭─ Additional Options ──────────────────────────────────────────────────────────────╮
│ * --table -t (academic_terms|academic_years|classes|class_attendance_byterm|c │
│ lass_attendance_bydate|class_timetable|hr_attendance_byterm|hr_a │
│ ttendance_bydate|memberships|modified_since_dates|parents|parent │
│ child_relationships|school|school_subjects|students|subject_grou │
│ p|tasks|task_results|teachers|term_grades|year_groups) │
│ [required] │
│ --fields -f Fields of the entity to include │
│ (TEXT) │
│ --limit -l Define small number for faster dev │
│ (INTEGER) │
│ --help Show this message and exit. │
╰───────────────────────────────────────────────────────────────────────────────────╯
We just have to pass it a --table option that includes the name of the entity we are seeking, in this case classes. Since we are starting to build complicated commands, let's use the \ so we can format using line breaks and spaces, making it easier to read:
The above output is the default when plain target INFO is used. It selected all the classes that have been imported and converted it into a pandas dataframe. Since we haven't told it to do anything other than that, the default action of displaying info about the dataframe, such as the column names, types, and count of non-null, is shown. The values for the first selected item is shown in "row 0" column.
There's simply too much information, so let's limiting the columns we are displaying, and filtering for just diploma classes:
Okay, what about querying to find all the high-level classes? The subject column has the key hl, but how do we access to query it?
This is where the power of tranformations comes in. Just like the query command after the chain command, mbpy has a slew of operations that can be performed on the query. There is a spread command that will make a new column for every key, and each column will be called subject.hl and subject.levels etc.
So now we can filter for hl classes, and let's arrange the columns in the order we wish to be output, and limit them to just the ones we're interested in:
Not only did we get this output in the terminal, but it saved to CSV as well.
To learn more about what kinds of commands are available, use the --help feature. The following shows the help files for every command issued on this page: