# Python Microsoft Fabric - Building an API on top of Fabric data

Answer REST calls from Fabric tables, with results cached for a minute so that repeated calls do not run a Spark query.

This page exposes a lakehouse table through a REST API. A caller sends a location and a date range, and a service runs the query and returns the rows.

> **What you need from your Fabric admin**
>
> The workspace ID and the lakehouse ID, from the address bar when the lakehouse is open, and the tenant setting that lets apps run Spark queries - "Livy endpoint" under Developer settings in the Fabric admin portal.

## The query in Fabric {#the-query-in-fabric}

The query the service runs, run here in a notebook against the lakehouse, returns one row per location and time in the range:

## The service {#the-service}

`query` takes one SQL string with no bind parameters, so every value that goes into it is one the service produced itself - a location from its own list and dates that Python parsed and turned back into text. The service also caches the rows for a minute, because a lakehouse query runs on Spark and takes seconds.

```python
# -*- coding: utf-8 -*-

# stdlib
from datetime import date
from http.client import BAD_REQUEST
from json import dumps, loads

# Zato
from zato.server.service import Service

_locations = 'Riverside', 'Oak Hill', 'Maple Grove'
_cache_ttl = 60

class GetOccupancy(Service):

    name = 'portal.get-occupancy'
    input = 'location', 'date_from', 'date_to'

    def handle(self):

        location = self.request.input.location
        date_from = self.request.input.date_from
        date_to = self.request.input.date_to

        # Only the locations the table has ..
        if location not in _locations:
            self.response.status_code = BAD_REQUEST
            self.response.payload = {'error': 'Unknown location'}
            return

        # .. only valid dates, which the SQL receives in ISO format ..
        try:
            date_from = date.fromisoformat(date_from)
            date_to = date.fromisoformat(date_to)
        except ValueError:
            self.response.status_code = BAD_REQUEST
            self.response.payload = {'error': 'Invalid date'}
            return

        # .. the rows from the cache if the same query ran in the last minute ..
        cache_key = f'occupancy:{location}:{date_from}:{date_to}'

        if cached := self.cache.get(cache_key):
            rows = loads(cached)
            self.response.payload = {'rows': rows}
            return

        # .. otherwise query the lakehouse ..
        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

        conn = self.microsoft.fabric['My Fabric']

        sql = f'''
        select location, as_of, occupied_beds, available_beds
        from occupancy
        where location = '{location}'
          and as_of between '{date_from}' and '{date_to}'
        order by as_of
        '''

        rows = conn.query(workspace_id, lakehouse_id, sql)

        # .. and cache the rows for a minute.
        data = dumps(rows)
        self.cache.set(cache_key, data, ex=_cache_ttl)

        self.response.payload = {'rows': rows}
```

## The channel {#the-channel}

Under `Connections → REST → Channels`, create a channel with the URL path `/api/occupancy`, the service `portal.get-occupancy` and an API key security definition that callers will send. In enmasse YAML:

```yaml
security:
  - name: Portal API Key
    type: apikey
    username: portal
    password: Zato_Enmasse_Env.PortalApiKey

channel_rest:
  - name: api.occupancy
    service: portal.get-occupancy
    url_path: /api/occupancy
    security: Portal API Key
    data_format: json
```

## Calling it {#calling-it}

A caller, here `curl`, gets the same rows the notebook showed:

```bash
curl -H "X-API-Key: <the key>" \
  "https://api.example.com/api/occupancy?location=Riverside&date_from=2026-03-11&date_to=2026-03-12"
```

```json
{
  "rows": [
    {"location": "Riverside", "as_of": "2026-03-11T10:00:00Z", "occupied_beds": 97, "available_beds": 23}
  ]
}
```

The first call for a location and range takes a few seconds, every call in the following minute takes milliseconds.

## The Spark session {#the-spark-session}

The connection opens one Spark session per lakehouse the first time `query` runs and keeps it for later queries, which is why the first call is the slow one. Fabric closes an idle session after a while, and the connection then opens a new one on the next query. When a service will not query a lakehouse again for a long time, `conn.close_spark_session(workspace_id, lakehouse_id)` closes the session right away instead of leaving it to time out.

## See also {#see-also}

- [Looking up Fabric data from your services](https://zato.io/docs/dev/examples/cloud/fabric/looking-up-data.html) - Caching tables that many services read
- [Sending reports on a schedule](https://zato.io/docs/dev/examples/cloud/fabric/scheduled-reports.html) - Query results delivered as a CSV file
- [Queries](https://zato.io/docs/dev/examples/cloud/fabric/api/queries.html) - query, run\_spark and Spark sessions in detail

## Learn more {#learn-more}

- [Development documentation](https://zato.io/docs/dev/) - Everything about writing services, in one place
- [Requests and responses](https://zato.io/docs/dev/request-response/) - What a service receives, what it returns and how to shape both
- [Integration examples](https://zato.io/docs/dev/examples/) - Ready-made code for the systems you are likely to connect to
- [IDE and debugging](https://zato.io/docs/dev/ide/) - Write services in the Dashboard or in your own editor
- [Data models](https://zato.io/docs/dev/model/) - Declare inputs and outputs and have them validated for you
- [In-depth API tutorial](https://zato.io/tutorials/main/01.html) - The full platform tutorial, from installation to production patterns