Thursday, July 17, 2014

Accessing PowerSchool Extension Table (custom fields)

In the past, It was not easy to get the custom fields data from PowerSchool's Oracle database because of the way it architected and organized custom fields data (they are stored as views in Oracle). With the newer version of PowerSchool (v7.9 and above), you can create custom fields to the extension database table. You can even migrate all the legacy custom fields (students and teachers/ school staffs) to that new location. It makes my life much easier when I want to manipulate those custom fields data because from now on, all the students custom fields data will be stored in 1 place, and the same for teachers's custom fields.

* Student custom fields data will be stored in U_STUDENTSUSERFIELDS table

* Teacher custom fields data will be stored in U_SCHOOLSTAFFUSERFIELDS table

For example, you can use this script (python, SQLAlchemy, cx_Oracle) to export students data from PowerSchool along with any custom fields data you want:




PowerSchool DATABASE Connector: