Howto read odata into pandas with python

Question

I'm trying to load odata into

This is my code:

import pandas as pd
import pyodata
import requests
SERVICE_URL = 'http://services.odata.org/V2/Northwind/Northwind.svc/'
HTTP_LIB = requests.Session()
northwind = pyodata.Client(SERVICE_URL, HTTP_LIB)
df = pd.DataFrame(northwind)

I get this error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\KrestenSkovstedBuch\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\frame.py", line 730, in __init__
    raise ValueError("DataFrame constructor not properly called!")
ValueError: DataFrame constructor not properly called!

I think I need something in between my pyodata object and my DataFrame. What would that be?

Answer 1

Here is an adaptation of a code I did. It uses pyodata package. I didn't run it, but any bug would be simple to adapt.

I think there must be a way to list all queried properties of an Entity but I don't know how. That's why I'm querying all properties.

import pyodata
import requests
import pandas as pd

SERVICE_URL = 'http://services.odata.org/V2/Northwind/Northwind.svc/'
HTTP_LIB = requests.Session()
northwind = pyodata.Client(SERVICE_URL, HTTP_LIB)

# query everything of entity set Customers
customers = northwind.entity_sets.Customers.get_entities().execute()

schema = service.schema
entity_type = next(et for et in schema.entity_types if et.name == "Customer")
properties = entity_type.properties()

def monta_prop_dict(obj, properties):
    d = {}
    for prop in properties:
        d[prop.name] = getattr(obj, prop.name)
    return d

data = []
for c in customers:
    data.append(monta_prop_dict(c, properties))

df = pd.DataFrame(data)

You would probably need to convert some values for the correct type. For me just datetimes are being converted. Everything else comes as string.