I'm trying to write a RESTful API to publish some data sets. Doing this in Python and wanting to follow OData standards. Goal is to be able to open the data from within Excel (Data > New query > OData).
First attempts seem hopeful: I've produced a Flask api that connects to a mysql database and returns the desired rows. What doesn't work yet unfortunately is opening from Excel. It errors out on the metadata descriptor, saying it encountered an unexpected property name of 'Article name' (alphabetic first column of my dataset) where it expected only 'name' and 'url' in a service document.
I can't seem to figure out what's wrong with the metadata that's being produced, so any help is most appreciated.
app.py:
from flask import Flask, jsonify, request, make_response
from flask_restful import Resource, Api, reqparse
import pandas as pd
import mysql.connector as sql
import ast
import xml.etree.ElementTree as ET
app = Flask(__name__)
api = Api(app, default_mediatype='application/json')
config = {
'host': '127.0.0.1',
'port': '3306',
'user': '***',
'passwd': '***',
'database': '***',
'charset': 'utf8mb4',
'raise_on_warnings': True
}
class FinancieleInstrumenten(Resource):
def get(self):
parser = reqparse.RequestParser()
parser.add_argument('begrotingsjaar', required=False)
parser.add_argument('begrotingshoofdstuk', required=False)
args = parser.parse_args() # parse arguments to dictionary
try:
cnx = sql.connect(**config)
# Build query depending on input variables
if not args['begrotingsjaar'] and not args['begrotingshoofdstuk']:
qry = "SELECT * FROM FinancieleInstrumenten LIMIT 25;"
res = pd.read_sql(qry, cnx)
elif not args['begrotingshoofdstuk']:
qry = """SELECT * FROM FinancieleInstrumenten WHERE Begrotingsjaar = {} LIMIT 25;"""
res = pd.read_sql_query(qry.format(args['begrotingsjaar']), cnx)
elif not args['begrotingsjaar']:
qry = """SELECT * FROM FinancieleInstrumenten WHERE Begrotingshoofdstuk = {} LIMIT 25;"""
res = pd.read_sql_query(qry.format(args['begrotingshoofdstuk']), cnx)
else:
qry = """SELECT * FROM FinancieleInstrumenten WHERE Begrotingsjaar = {} AND Begrotingshoofdstuk = {} LIMIT 25;"""
res = pd.read_sql_query(qry.format(args['begrotingsjaar'], args['begrotingshoofdstuk']), cnx)
# Build response with OData header
resp = make_response({
'@odata.context': 'https://stukkenparser.gitlab-minfin.nl/financiele-instrumenten/$metadata',
'value': res.to_dict('records')
})
resp.headers['OData-Version'] = '4.0'
return resp
except Exception as e:
print(str(e))
finally:
if cnx.is_connected():
cnx.close()
def put(self):
return {'message': 'Only method GET allowed.'}, 405
def post(self):
return {'message': 'Only method GET allowed.'}, 405
def patch(self):
return {'message': 'Only method GET allowed.'}, 405
def delete(self):
return {'message': 'Only method GET allowed.'}, 405
api.add_resource(FinancieleInstrumenten, '/financiele-instrumenten')
@app.route("/financiele-instrumenten/$metadata", methods=['GET'])
def index():
if request.method=='GET':
root = ET.parse('financiele-instrumenten.metadata.xml').getroot()
return app.response_class(ET.tostring(root), mimetype='application/xml')
if __name__ == '__main__':
app.run(debug=True, host='127.0.0.1', port=1337)
financiele-instrumenten.metadata.xml:
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
<edmx:DataServices>
<Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="NL.MinFin.OData.FinancieleInstrumenten">
<EntityContainer Name="FinancieleInstrumentenEntities">
<EntitySet Name="FinancieleInstrumentenSet" EntityType="NL.MinFin.OData.FinancieleInstrumenten.FinancieleInstrumentenType" />
</EntityContainer>
<EntityType Name="FinancieleInstrumentenType">
<Property Name="Begrotingsjaar" Type="Edm.String" Nullable="false" />
<Property Name="Begrotingshoofdstuk" Type="Edm.String" Nullable="false" />
<Property Name="Begrotingsnaam" Type="Edm.String" Nullable="false" />
<Property Name="Artikelnummer" Type="Edm.String" Nullable="true" />
<Property Name="Artikelnaam" Type="Edm.String" Nullable="true" />
<Property Name="Artikelonderdeel" Type="Edm.String" Nullable="true" />
<Property Name="Instrument" Type="Edm.String" Nullable="true" />
<Property Name="Regeling" Type="Edm.String" Nullable="true" />
<Property Name="Ontvanger" Type="Edm.String" Nullable="true" />
<Property Name="KvK-nummer" Type="Edm.String" Nullable="true" />
<Property Name="Bedrag" Type="Edm.Int64" Nullable="false" />
</EntityType>
</Schema>
</edmx:DataServices>
</edmx:Edmx>
short answer:
your medatata is incorrect, because your entitytype misses a key
long answer:
from: https://learn.microsoft.com/en-us/odata/concepts/data-model:
Entity types Entity types are named structured types with a key. They define the named properties and relationships of an entity. Entity types may derive by single inheritance from other entity types.
The key of an entity type is formed from a subset of the primitive properties (e.g. CustomerId, OrderId, LineId, etc.) of the entity type.
--
So, the essence of an entity is, that you can uniquely identitfy it, to reach it from the outside our be source for navigation properties. Therefore, you will have to define an entity key, so that, for example, when ODATA wants to navigate to one of them, it can do that, for example /FinancieleInstrumentenEntities(key)/.
Your edmx should look something like this ( i took a guess about the key, hope its unique ) :
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
<edmx:DataServices>
<Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="NL.MinFin.OData.FinancieleInstrumenten">
<EntityContainer Name="FinancieleInstrumentenEntities">
<EntitySet Name="FinancieleInstrumentenSet" EntityType="NL.MinFin.OData.FinancieleInstrumenten.FinancieleInstrumentenType" />
</EntityContainer>
<EntityType Name="FinancieleInstrumentenType">
<Key>
<PropertyRef Name='Begrotingsjaar'/>
<PropertyRef Name='Begrotingshoofdstuk'/>
<PropertyRef Name='Begrotingsnaam'/>
</Key>
<Property Name="Begrotingsjaar" Type="Edm.String" Nullable="false" />
<Property Name="Begrotingshoofdstuk" Type="Edm.String" Nullable="false" />
<Property Name="Begrotingsnaam" Type="Edm.String" Nullable="false" />
<Property Name="Artikelnummer" Type="Edm.String" Nullable="true" />
<Property Name="Artikelnaam" Type="Edm.String" Nullable="true" />
<Property Name="Artikelonderdeel" Type="Edm.String" Nullable="true" />
<Property Name="Instrument" Type="Edm.String" Nullable="true" />
<Property Name="Regeling" Type="Edm.String" Nullable="true" />
<Property Name="Ontvanger" Type="Edm.String" Nullable="true" />
<Property Name="KvK-nummer" Type="Edm.String" Nullable="true" />
<Property Name="Bedrag" Type="Edm.Int64" Nullable="false" />
</EntityType>
</Schema>
</edmx:DataServices>
</edmx:Edmx>
As you can see, for composite keys you can add multiple property refs. You need at least one .
The properties you use as key must be not nullable, and it is important that the combination is unique. (im not sure about that in that case)
If you don't have items that are uniquely identifyable, you can try complex types instead of entities, which you attach as property to a root entity (which still needs a key).