pyODK : $expand parameter seems to not work

I am playing with pyODK and plptyhon (PostgreSQL python procedural language) and it's promising :slight_smile:
I face a problem when I try to get expanded json :
this code is ok (last 10 submissions of the form Sicen_2022

from pyodk.client import Client

client = Client()
client.open()
client.submissions.get_table("Sicen_2022", "5", "Submissions", None, 10, None, None, None)

When I try to use the last parameter to gest expanded json, I face an error :

from pyodk.client import Client

client = Client()
client.open()
client.submissions.get_table("Sicen_2022", "5", "Submissions", None, 10, None, None, "*")

the error message :

PyODKError                                Traceback (most recent call last)
Cell In[6], line 5
      3 client = Client()
      4 client.open()
----> 5 client.submissions.get_table("Sicen_2022", "5", "Submissions", None, 10, None, None, '*')

File ~/jupyter/jupyter/lib/python3.8/site-packages/pyodk/endpoints/submissions.py:163, in SubmissionService.get_table(self, form_id, project_id, table_name, skip, top, count, wkt, filter, expand)
    161     raise err
    162 else:
--> 163     response = self.session.get_200_or_error(
    164         url=self.urls.get_table.format(
    165             project_id=pid, form_id=fid, table_name=table
    166         ),
    167         logger=log,
    168         params=params,
    169     )
    170     return response.json()

File ~/jupyter/jupyter/lib/python3.8/site-packages/pyodk/session.py:75, in Session.get_200_or_error(self, url, logger, *args, **kwargs)
     73 err = PyODKError(msg, response)
     74 logger.error(err, exc_info=True)
---> 75 raise err

PyODKError: ('The request to projects/5/forms/Sicen_2022.svc/Submissions failed. Status: 400, content: b\'{"message":"The OData filter expression you provided could not be parsed: Fail at 0","code":400.18,"details":{"reason":"Fail at 0"}}\'', <Response [400]>)

My function call was missing a parameter ! :grimacing:
Now I wil name them to avoid such problem and use only used ones

from pyodk.client import Client

client = Client()
client.open()
client.submissions.get_table(form_id="Sicen_2022", project_id="5", table_name="Submissions", top=10, expand="*")
1 Like

I'm glad you figured it out, I could not spot it. I agree that using named parameters is the best way to go. I think all of our examples use them but if you see some that don't, please let us know.

When going to PostgreSQL, I would imagine that it would be easier to work with each data table separately like what is shown in the first repeat example. Is using expand a good option for you because the library you're using already has a way to handle nested JSON? I'm asking because depending on your answer we may want to update some of the example and docs.

1 Like

Yes you're right Hélène. That's what I already do with central2pg. That way I can ask PostgreSQL to dynamically create the tables and add new attributes when forms evolve.

Using $expand make it impossible even if I flatten the json.
Our main form contains two nested repeat groups.
Plpython and pyODK might make the process lighter than using "copy from curl".
At the moment I can do with Python what I already do with central2pg.
But I need to find a way to only get recent data from subtables. As we discuss with @Matthew_White the easiest way for me would be to be able to filter subtables data on creation/submission date. Getting all data works really well but I am not satisfied to get each hour the 20000 datas collected since we published the form in March when only 100 were collected today.
I'll spend some more time on in a few days.

Hi @Matthew_White and @LN,

few days later :slight_smile: a first integration of some pyODK (a little) in pl/python functions is out and allow us to automatically get data from central, even using a filter filtered, and create, populate and update (even new columns) dedicated tables in a PostgreSQL database.

Test and feedback from PostgreSQL and/or python experienced users are welcome :wink:
Name of the project may be changed if too confusing.

-> https://github.com/mathieubossaert/pl-pyodk

I'll post a dedicated topic in the showcase.

2 Likes