# pyODK : $expand parameter seems to not work

**URL:** <https://forum.getodk.org/t/pyodk-expand-parameter-seems-to-not-work/39748>\
**Category:** Support\
**Created:** [December 1, 2022, 11:54am UTC](https://forum.getodk.org/t/pyodk-expand-parameter-seems-to-not-work/39748 "2022-12-01T11:54:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\
**Post date:** [December 1, 2022, 11:54am UTC](https://forum.getodk.org/t/pyodk-expand-parameter-seems-to-not-work/39748/1 "2022-12-01T11:54:50Z")

</div>

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

```python
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 :

```python
from pyodk.client import Client

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

```

the error message :

```txt
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]>)

```

---

<div class="post-metadata">

**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\
**Post date:** [December 7, 2022, 12:09pm UTC](https://forum.getodk.org/t/pyodk-expand-parameter-seems-to-not-work/39748/2 "2022-12-07T12:09:34Z")

</div>

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

```python
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="*")

```

---

<div class="post-metadata">

**Author:** ![LN](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ln/32/1605_2.png) [@LN](https://forum.getodk.org/u/LN)\
**Post date:** [December 7, 2022, 7:48pm UTC](https://forum.getodk.org/t/pyodk-expand-parameter-seems-to-not-work/39748/3 "2022-12-07T19:48:31Z")

</div>

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](https://github.com/getodk/pyodk/blob/master/examples/working-with-repeats.ipynb). 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.

---

<div class="post-metadata">

**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\
**Post date:** [December 7, 2022, 8:44pm UTC](https://forum.getodk.org/t/pyodk-expand-parameter-seems-to-not-work/39748/4 "2022-12-07T20:44:38Z")

</div>

> [@LN](#):
>
> I would imagine that it would be easier to work with each data table separately like what is shown [in the first repeat example](https://github.com/getodk/pyodk/blob/master/examples/working-with-repeats.ipynb)

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.

---

<div class="post-metadata">

**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\
**Post date:** [April 5, 2023, 2:03pm UTC](https://forum.getodk.org/t/pyodk-expand-parameter-seems-to-not-work/39748/5 "2023-04-05T14:03:12Z")

</div>

Hi @Matthew_White and @LN,

few days later 🙂 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 😉  
Name of the project may be changed if too confusing.

-\> [https://github.com/mathieubossaert/pl-pyodk](https://github.com/mathieubossaert/pl-pyodk)

I'll post a dedicated topic in the showcase.