0

I have successfully set up the plugin I need in PowerSchool to query tables via Python as follows. I looked at the examples in the PowerSchool library documentation, but cannot successfully query a specific table (while applying any filters) with the same approaches. I can only get the entire table (when using the SQL query) or "HTTPError: 400 Client Error: Bad Request for url: ..." (when using params).

import powerschool
import pandas as pd

client_id =  'client id goes here'
client_secret = 'client secret goes here'
my_credentials = (client_id, client_secret)
host_name = 'host name goes here'
ps = powerschool.PowerSchool(host=host_name, auth=my_credentials)
st = ps.get_schema_table('students') 

# I have tried each of these and they all return the entire table
sql = '''SELECT * FROM STUDENTS WHERE last_name IN ('SMITH', 'JOHNSON')'''
params = {'last_name':'SMITH',
          'projection':'last_name'}

stData = st.query(**params)
df = pd.DataFrame.from_dict(stData)

stData2 = st.query(body=sql)
df = pd.DataFrame.from_dict(stData2)

Thanks in advance!

Dance Party
  • 3,459
  • 10
  • 42
  • 67

1 Answers1

0

The docs you linked give this example:

params = {
    'q': 'id=ge=10000',
    'projection': 'school_number,abbreviation',
}
schools_table.query(**params)

Your code does:

params = {'last_name':'SMITH',
          'projection':'last_name'}

stData = st.query(**params)
df = pd.DataFrame.from_dict(stData)

It seems to me that 'last_name':'SMITH' should be something closer to 'q': 'id=ge=10000'. I don't know PowerSchool but maybe 'q': 'last_name=SMITH'?

hkBst
  • 2,818
  • 10
  • 29