ACME PowerSchool

version 0.1

The ACME PowerSchool module provides two interfaces for interacting with a PowerSchool instance: the REST API and an Oracle ODBC connection.

Installation

• Copy the acme_powerschool-0.1-py3-none-any.whl file from the dist folder to any folder on your machine
• Issue the following command from a bash, command, or PowerShell prompt:
  pip install acme_powerschool-0.1-py3-none-any.whl
• All dependencies should be automatically downloaded and installed with the wheel file

REST API

This client provides a wrapper for the Python Requests library with some PowerSchool-specific pieces abstracted out. It balances the familiarity of Requests, the challenges of the PowerSchool API, and the flexibility of needing to perform both simple and complex tasks with the same library.

General Usage

>>> import acme_powerschool
>>> ps = acme_powerschool.api('applegrove.powerschool.com', client_id='7ebf0a8e-f760-01fd-7812-37c81a3b0cf0', client_secret='1f8c6f57-2ced-179b-664f-e227ae4dc312')
>>> response = ps.get('ws/v1/student/2')
>>> response
<Response [200]>

Consider what this request would require without the library:

>>> import requests
>>> import base64
>>> import json
>>> authorization = (base64.b64encode(('7ebf0a8e-f760-01fd-7812-37c81a3b0cf0:1f8c6f57-2ced-179b-664f-e227ae4dc312').encode('utf-8'))).decode('utf-8')
>>> headers={"Authorization": f"Basic {authorization}", "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8"}

>>> response = requests.post('https://applegrove.powerschool.com/oauth/access_token', data='grant_type=client_credentials', headers=headers)
>>> access_token = json.loads(response.text)['access_token']

>>> headers={'Authorization': f'Bearer {access_token}', 'Content-Type': 'application/json'}

>>> response = requests.get('https://applegrove.powerschool.com/ws/v1/student/2', headers=headers)
>>> response
<Response [200]>

The object returned by a request is actually a Requests.response object. This means that the following methods and attributes from Requests are available:

>>> response.status_code
200

>>> response.text
'{"student":{"@expansions":"demographics, addresses, alerts, phones, school_enrollment, ethnicity_race, contact, contact_info, initial_enrollment, schedule_setup, fees, lunch","@extensions":"c_studentlocator,s_stu_crdc_x,s_stu_x,activities,u_tech,u_students_extension,s_stu_ncea_x,s_stu_edfi_x,studentcorefields","id":2,"local_id":3,"student_username":"ag1student1","name":{"first_name":"Brandon","last_name":"Adair"}}}'

>>> response.json()
{'student': {'@expansions': 'demographics, addresses, alerts, phones, school_enrollment, ethnicity_race, contact, contact_info, initial_enrollment, schedule_setup, fees, lunch',
  '@extensions': 'c_studentlocator,s_stu_crdc_x,s_stu_x,activities,u_tech,u_students_extension,s_stu_ncea_x,s_stu_edfi_x,studentcorefields',
  'id': 2,
  'local_id': 3,
  'student_username': 'ag1student1',
  'name': {'first_name': 'Brandon', 'last_name': 'Adair'}}}

Creating a PowerSchool API Object

ps = acme_powerschool.api(base_url=None, client_id=None, client_secret=None, access_token=None, credential_file=None, plugin=None)

There are four patterns available for creating an instance of a PowerSchool acme_powerschool.api. base_url (the first argument) is required regardless of pattern choice.

client_id and client_secret

This is the most likely scenario for a beginning developer. Supplying both the client_id and client_secret will automatically generate the bearer token when the first real request is made.

>>> ps = acme_powerschool.api('applegrove.powerschool.com', client_id='7ebf0a8e-f760-01fd-7812-37c81a3b0cf0', client_secret='1f8c6f57-2ced-179b-664f-e227ae4dc312')

access_token

During development, it might be handy to use a known bearer token to make requests. This is available for developers who understand OAuth well and are rapidly developing prototypes.

>>> ps = acme_powerschool.api('applegrove.powerschool.com', access_token='65908f8c-c4ff-25fe-4770-7bcd3c1d095e')

credential_file

This pattern is intended to be used in production when the client_id and client_secret need to be abstracted out of the script itself, perhaps in a file or folder that are inaccessible to users other than the script developer. This pattern serves the purpose of not only keeping these credentials outside the script but also allowing for automatic caching of the access_token.

ps = acme_powerschool.api('applegrove.powerschool.com', credential_file='../credentials.json')

The structure of a credential file is JSON:

{
    "client_id": "7ebf0a8e-f760-01fd-7812-37c81a3b0cf0",
    "client_secret": "1f8c6f57-2ced-179b-664f-e227ae4dc312"
}

Using this pattern, when an access_token is retrieved from the PowerSchool server (either because one was not previously specified or it has expired), the access_token will automatically be saved in the credential file:

{
    "client_id": "7ebf0a8e-f760-01fd-7812-37c81a3b0cf0",
    "client_secret": "1f8c6f57-2ced-179b-664f-e227ae4dc312",
    "access_token": "65908f8c-c4ff-25fe-4770-7bcd3c1d095e"
}

This allows subsequent calls of the script to continue using this access_token without re-requesting it, and until it expires.

plugin (keyring)

This pattern is is the most secure way to store credentials and should be used whenever possible. It leverages keyring, which is a cross-platform Python library for interacting with the system’s keyring service (Credential Locker on Windows, Keychain on MacOS). It requires the keyring library to be installed via shell:

$ pip install keyring

ps = acme_powerschool.api('applegrove.powerschool.com', plugin='My Plugin Name')

The value of the plugin argument does not need to match the name of the plugin that has been installed to PowerSchool, but it is encouraged. When the plugin argument is used, the library will retreive the client_id, client_secret, and current access_token (if available) from the system’s keyring. If either client_id or client_secret is blank, the library will prompt the user to enter them via a call to getpass. For this reason, it is recommended to run a production script from shell first before scheduling a task to run at a specified time.

If the client_id and client_secret change (such as when a plugin is uninstalled and reinstalled), it will be necessary to flush these credentials out of the keyring service. This can be performed outside of the script by issuing the following shell command:

$ python -m keyring set [base_url] [plugin name]

If the plugin name has spaces in it, use quotes to contain it:

$ python -m keyring set applegrove.powerschool.com "My Plugin Name"
Password for 'My Plugin Name' in 'applegrove.powerschool.com':

Leave the password blank and hit Enter/Return to clear the value. The next execution of the script will prompt the user for a new client_id and client_secret.

Using this pattern, when an access_token is retrieved from the PowerSchool server (either because one was not previously specified or it has expired), the access_token will automatically be saved in the system’s keyring service. This allows subsequent calls of the script to continue using this access_token without re-requesting it until it expires.

Default Session Settings

By default, the acme_powerschool.api.session object will be configured to send and receive JSON data by setting the following headers:

acme_powerschool.api.session.headers = {
    'Content-Type': 'application/json',
    'Accept': 'application/json'
}

If you prefer to work in XML, this can be overridden by accessing the headers attribute in acme_powerschool.api.session:

acme_powerschool.api.session.headers['Accept'] = 'application/xml'

Other session headers can be set using this same attribute.

High-Level Methods and Attributes (Porcelain)

Methods

In general, developers will use these four methods to make PowerSchool API requests. These are provided as helper methods that call the acme_powerschool.api.request() method with the appropriate HTTP method passed as an argument.

acme_powerschool.api.get(url, **kwargs)

acme_powerschool.api.post(url, **kwargs)

acme_powerschool.api.put(url, **kwargs)

acme_powerschool.api.delete(url, **kwargs)

• url: the PowerSchool API resource being requested
• **kwargs: keyword arguments that are passed to the generic acme_powerschool.api.request() method, which are then passed to the Session.request() method.

Request

The generic, medium-level method used to make PowerSchool API requests.

acme_powerschool.api.request(method, url, **kwargs)

• method: any of the HTTP Methods (GET, POST, PUT, DELETE).

• url: the PowerSchool API resource being requested. If the URL begins with the base_url or a leading slash, it will be automatically trimmed. This means the following requests are all equivalent:

  >>> response = ps.get('ws/v1/student/2')
  >>> response = ps.get('/ws/v1/student/2')
  >>> response = ps.get('https://applegrove.powerschool.com/ws/v1/student/2')

• **kwargs: keyword arguments that are passed to the Requests Session.request() method. This allows for any Requests-compatible arguments to be used when making a PowerSchool API request.

The request method automatically handles 401 Unauthorized responses by requesting a new access_token and making the request again.

A failure to request a new token due to missing or bad credentials will raise a RuntimeError.

If the credential_file or plugin method was used to create the client object, the new token will automatically be cached using the corresponding method.

Other response codes must be caught and handled by the developer. Common exceptions and their causes include:

• 400 Bad Request - Sent a malformed request payload.
• 404 Not Found - Requested a resource that does not exist.
• 405 Method Not Allowed - Used GET instead of POST.
• 415 Unsupported Media Type - Sent a Content-Type: application/xml header when only application/json is allowed.
• 500 Internal Server Error - Something is wrong with the server.

Attributes

Session

acme_powerschool.api.session The Requests session object that is used for making every PowerSchool API request. It usually will not need to be accessed directly. Possible use cases include setting the default Accept header to application/xml to override the default choice of application/json:

ps.session.headers['Accept'] = 'application/xml'

Low-Level Methods and Attributes (Plumbing)

The following methods and attributes are used internally by the library to perform various functions necessary to interact with the PowerSchool API. They should not be needed by most developers but are documented here for advanced usage and exploration. #### Methods

Sets the access token and updates the Request Session's Authorization header

```acme_powerschool.api._obtain_access_token()```
Requests a bearer token from the server's ```/oauth/access_token``` URL.
Raises a <b>`RuntimeError`</b> if either a `client_id` or a `client_secret` is missing.
Raises a <b>`RuntimeError`</b> if an `access_token` cannot be retrieved using the provided `client_id` and `client_secret`.

#### Attributes

`acme_powerschool.api._base_url`
The domain without the scheme or path.  Ex: `applegrove.powerschool.com`

`acme_powerschool.api._client_id`
The `client_id`. Ex: `7ebf0a8e-f760-01fd-7812-37c81a3b0cf0`

`acme_powerschool.api._client_secret`
The `client_secret`. Ex: `1f8c6f57-2ced-179b-664f-e227ae4dc312`

`acme_powerschool.api._access_token`
The `access_token`. Ex: `65908f8c-c4ff-25fe-4770-7bcd3c1d095e`

`acme_powerschool.api._credential_file`
The path of the credential file used to hold the `client_id`, `client_secret`, and `access_token`.

`acme_powerschool.api._plugin`
The name of the plugin to be referenced when accessing the system's keyring service.

## Usage Examples

### Basic student info
```python
>>> response = ps.get('ws/v1/student/2')
>>> response.json()
{'student': {'@expansions': 'demographics, addresses, alerts, phones, school_enrollment, ethnicity_race, contact, contact_info, initial_enrollment, schedule_setup, fees, lunch',
  '@extensions': 's_il_stu_earlychildhood_x,s_il_stu_preid_x,s_il_stu_plan504_x,c_studentlocator,s_il_stu_homeless_x,s_il_stu_ell_x,s_stu_crdc_x,s_stu_x,activities,u_tech,u_students_extension,s_il_stu_x,s_il_stu_demographics_x,s_stu_ncea_x,s_il_stu_collegecareer_x,s_stu_edfi_x,s_il_stu_birthto3_x,studentcorefields',
  'id': 2,
  'local_id': 3,
  'student_username': 'ag1student1',
  'name': {'first_name': 'Brandon', 'last_name': 'Adair'}}}

Adding the contact_info expansion

>>> response = ps.get('ws/v1/student/2?expansions=contact_info')
>>> response.json()
{'student': {'@expansions': 'demographics, addresses, alerts, phones, school_enrollment, ethnicity_race, contact, contact_info, initial_enrollment, schedule_setup, fees, lunch',
  '@extensions': 'c_studentlocator,s_stu_crdc_x,s_stu_x,activities,s_stu_ncea_x,s_stu_edfi_x,studentcorefields',
  'id': 2,
  'local_id': 3,
  'student_username': 'ag1student1',
  'name': {'first_name': 'Brandon', 'last_name': 'Adair'},
  'contact_info': {'email': 'Brandom.Adair@powerschool.com'}}}

Updating a student’s email address

A POST body is passed as the data argument (see https://docs.python-requests.org/en/latest/api/#requests.post).

For the PowerSchool API, the value of a POST body needs to be a string representation of a JSON object. Wrap a Python dict in json.dumps() to construct this string:

>>> response = ps.post(
    'ws/v1/student',
    data=json.dumps({
        "students":{
            "student":[{
                "id": "2",
                "client_uid": "2",
                "action": "UPDATE",
                "contact_info": {
                    "email": "foo@bar.com'
                }
            }]
        }
    })
)
>>> response.json()
{'results': {'insert_count': 0,
  'update_count': 1,
  'delete_count': 0,
  'result': {'client_uid': 2,
   'status': 'SUCCESS',
   'action': 'UPDATE',
   'success_message': {'id': 2,
    'ref': 'https://applegrove.powerschool.com/ws/v1/student/2'}}}}

# Verify the update
>>> response = ps.get('ws/v1/student/2?expansions=contact_info')
>>> response.json()
{'student': {'@expansions': 'demographics, addresses, alerts, phones, school_enrollment, ethnicity_race, contact, contact_info, initial_enrollment, schedule_setup, fees, lunch',
  '@extensions': 'c_studentlocator,s_stu_crdc_x,s_stu_x,activities,s_stu_ncea_x,s_stu_edfi_x,studentcorefields',
  'id': 2,
  'local_id': 3,
  'student_username': 'ag1student1',
  'name': {'first_name': 'Brandon', 'last_name': 'Adair'},
  'contact_info': {'email': 'foo@bar.com'}}}

Retrieving School Information

JSON (the default for this module):

>>> response = ps.get('ws/v1/district/school')
>>> response.text
'{"schools":{"@expansions":"school_boundary, full_time_equivalencies, school_fees_setup","@extensions'
 # ...snipped...
 ',"principal":{"name":{"first_name":"Alex","middle_name":"McQuaid"},"email":"amcquaid@pssis.com"}}]}}'

XML (temporary override for only this request):

>>> response = ps.get('ws/v1/district/school', headers={'Accept': 'application/xml'})
>>> response.text
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?><schools expansions="school_boundary, full_ti'
 # ...snipped...'
 'le_name>McQuaid</middle_name></name><email>amcquaid@pssis.com</email></principal></school></schools>'

Advanced usage

Handling missing access_requests

A common PowerSchool API annoyance is the need to request access to every field referenced in a PowerQuery. If a field is missing from a plugin’s XML file, the API call will fail with a status_code of 403 Forbidden. In the body of the response, PowerSchool will provide a list of the missing fields. To facilitate PowerQuery development, when a 403 Forbidden error is encountered, this list of missing fields is parsed, converted into a list of <access_request> tags, and raised as a warning to the developer. These tags are also returned as an attribute of the response object and can be accessed programatically via response.access_requests:

>>> response = ps.post('ws/schema/query/com.pearson.core.attendance.daily_attendance_template', data='{"studentid": "2"}')
>>> response
<Response [403]>
PowerSchoolWarning: No access to field. access_requests: <field table="attendance" field="studentid" access="ViewOnly" />

>>> response.access_requests
'<field table="attendance" field="studentid" access="ViewOnly" />'

Oracle ODBC Connection

This helper method simplifies making a connection to the Oracle database by wrapping a call to the python_oracledb connect() method. It merely returns a oracledb.Connection object that can be used to execute queries against the database. The primary benefit of this method is that it encourages good password practices by leveraging the keyring library for credential storage and retrieval.

Making an Oracle connection over ODBC requires that the machine running the script has a route to the database server via the IP address or FQDN. This typically means access within a LAN or via a VPN connection. It also requires that the Oracle port (usually 1521) is open on the firewall.

General Usage

>>> import acme_powerschool
>>> with acme_powerschool.odbc( "applegrove.powerschool.com", "oracle_readonly_account" ) as ps:
>>> cursor = ps.cursor()
>>> cursor.execute("select school_number, name from schools")
>>> ps.commit()
>>>
>>> print( cursor.description )
>>>
>>> while True:
>>>     row = cursor.fetchone()
>>>     if row is None:
>>>         break
>>>     print(row)
(100, 'Apple Grove High School')
(600, 'Cherry Hill Middle School')
(700, 'Washington Elementary')

The metadata about the query can be retrieved via the cursor.description object:

>>> cursor.description
[('SCHOOL_NUMBER', <oracledb.DbType DB_TYPE_NUMBER>, 11, None, 10, 0, 1),
 ('NAME', <oracledb.DbType DB_TYPE_VARCHAR>, 60, 240, None, None, 1)]

The built-in oracledb methods for creating a cursor, executing a query, and working with the results can be be somewhat cumbersome. The Pandas library provides an extremely powerful and flexible interface for working with sets of data using a structure called a DataFrame and is the recommended approach.

The oracledb connection object returned by this helper method can be passed as an argument to the pandas.read_sql method to execute a query and parse it as a DataFrame

import acme_powerschool
import pandas as pd
>>> ps = acme_powerschool.odbc( "applegrove.powerschool.com", user="oracle_readonly_account" )
>>> df = pd.read_sql( "select school_number, name from schools where school_number in (100, 600, 700)", ps)
>>> df
|    |   SCHOOL_NUMBER | NAME                      |
|---:|----------------:|:--------------------------|
|  0 |             100 | Apple Grove High School   |
|  1 |             600 | Cherry Hill Middle School |
|  2 |             700 | Washington Elementary     |

Creating a PowerSchool ODBC Connection Object

An ODBC connection requires five pieces of information:

• user
• password
• server
• port
• database

These five pieces can be passed to the constructor in one of two ways: 1) a complete connection string or 2) some or all of a connection string’s pieces passed as arguments.

Complete Connection String

A complete connection string follows this structure:

# General form:
ps = acme_powerchool.odbc( connectionstring="user/password@applegrove.powerschool.com:1521/psproddb" )

# Real values:
ps = acme_powerchool.odbc( connectionstring="oracle_readonly_account/password@server:port/database" )

While this pattern can be used, it is intended more as an intermediate step in a script’s development process. This approach stores all of the connection details in plaintext, which is a security risk.

Arguments

The five parts can also be passed as arguments:

# General form:
ps = acme_powerchool.odbc( server="server", user="user", password="password", port="port", database="database" )

# Real values:
ps = acme_powerchool.odbc( server="applegrove.powerschool.com", user="oracle_readonly_account", password="mypassword", port="1521", database="psproddb" )

Only server and user are required. Not passing a port or database will result in the defaults of 1521 and psproddb being used, respectively.

The password argument is optional, but discouraged. Much as saving a complete connection string poses a security risk, so does passing it as a plaintext argument. Omitting the password argument will result in a value being queried from the system’s keyring store. More information about using keyring can be found below. If the password argument is used, consider passing an environment variable or some other value retrieved from another storage location.

Incorporating these default values and suggested practices, usage of the ODBC method should generally follow this pattern:

ps = acme_powerchool.odbc( server="applegrove.powerschool.com", user="oracle_readonly_account" )

• port defaults to 1521
• database defaults to psproddb
• password is queried from the system’s keyring store, using the provided server and user values

Keyring

It is strongly encouraged to omit the password argument to allow the value to be queried from the sytem’s keyring store.

On first execution, keyring will prompt the user for the Oracle account’s password on this server, cache it in the system’s keyring store, and use it on subsequent executions of the script.

If the password for this Oracle account is changed, it will be necessary to flush this password out of the keyring service. This can be performed outside of the script by issuing the following shell command:

$ python -m keyring set [server] [user]

An example:

$ python -m keyring set applegrove.powerschool.com oracle_readonly_account
Password for 'oracle_readonly_account' in 'applegrove.powerschool.com':

Leave the password blank and hit Enter/Return to clear the value. The next execution of the script will prompt the user for a new password.

Contributing

This is a community project, and pull requests are welcome. We have three requests when making contributions:

1. Please follow the Forking Workflow.
2. If you would like to contribute a new helper method for a specific API endpoint, please leverage one of the existing verb methods (or the generic acme_powerschool.api.request() method itself). This ensures that the low-level exception and access_token handling are used.
3. Please ensure that all unit tests pass for both clients, and add new unit tests for new methods. Examples of running tests are found in the Tests section.

Tests

API

$ cd tests
$ python api.py --base_url="applegrove.powerschool.com" --client_id="7ebf0a8e-f760-01fd-7812-37c81a3b0cf0" --client_secret="1f8c6f57-2ced-179b-664f-e227ae4dc312" --access_token="65908f8c-c4ff-25fe-4770-7bcd3c1d095e"
.............
----------------------------------------------------------------------
Ran [X] tests in [Y]s
OK

ODBC

$ cd tests
$ python odbc.py --server="applegrove.powerschool.com" --user="user" --password="password" --port="12345" --db="databasename"
.............
----------------------------------------------------------------------
Ran [X] tests in [Y]s

OK

Contact

Project Admin - Adam Larsen <adam@auroraedtech.com>

Acknowledgements

• Mike Gerszewski
• Vance Allen
• Roger Sprik
• Romy Backus
• Steve Stinson
• Tom Donovan