Python Microsoft Fabric API - Queries

A connection has three methods for running SQL and Spark code against a lakehouse, and two for the sessions they run on. All of them run on a Spark session, and the section at the end explains how sessions are opened, kept and closed.

query

conn.query(workspace_id, lakehouse_id, sql)

Runs an SQL statement against a lakehouse and returns the rows as a list of dicts keyed by column name.

Returns a list of dicts, one per row. A statement that fails raises an exception with Spark's error message.

# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class Query(Service):

    def handle(self):

        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

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

        rows = conn.query(
            workspace_id,
            lakehouse_id,
            "select location, occupied_beds from occupancy where as_of = '2026-03-11T10:00:00Z'",
        )

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

{
  "rows": [
    {"location": "Riverside", "occupied_beds": 97},
    {"location": "Oak Hill", "occupied_beds": 61},
    {"location": "Maple Grove", "occupied_beds": 44}
  ]
}

run_spark

conn.run_spark(workspace_id, lakehouse_id, session_id, code, kind='pyspark')

Runs code on a Spark session and returns the statement's output once it completes. This is what query uses, with kind='sql', and it is the method for PySpark code that does more than one statement can.

Returns the statement's output as a dict with status, execution_count and data, where data holds the result under a key named after its format, e.g. text/plain for what the code printed. Code that raises an error raises an exception with the error's message.

# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class RunSpark(Service):

    def handle(self):

        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

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

        code = """
frame = spark.sql('select location, count(*) as admissions from admissions group by location')
frame.write.mode('overwrite').saveAsTable('admissions_by_location')
print(frame.count())
"""

        session_id = conn.open_spark_session(workspace_id, lakehouse_id)
        output = conn.run_spark(workspace_id, lakehouse_id, session_id, code)

        data = output['data']
        printed = data['text/plain']

        self.response.payload = {'status': output['status'], 'printed': printed}

open_spark_session

conn.open_spark_session(workspace_id, lakehouse_id)

Opens a new Spark session on a lakehouse and waits until it is ready to run statements, which takes from a few seconds to a couple of minutes depending on the capacity.

Returns the session ID as a string. A session that does not become ready within ten minutes raises an exception.

query does not need this method - it opens and keeps a session of its own, as described below. Call it when running run_spark directly.

close_spark_session

conn.close_spark_session(workspace_id, lakehouse_id)

Closes the session query keeps for a lakehouse, if there is one. Does nothing when there is none.

How sessions are reused

The first time query runs against a lakehouse, the connection opens a Spark session for it and keeps the session's ID. Every later query against the same lakehouse runs on that session, so only the first call starts a session. Before each query the connection checks that the session is still alive, and when Fabric has closed it - sessions that sit idle are closed by Fabric after a while - a new one is opened in its place.

close_spark_session is for the case when a service will not query a lakehouse again for a long time and the capacity should be freed now. When the connection itself is deleted in the Dashboard, every session it kept is closed.

Sessions run through Fabric's Livy endpoint, which a tenant admin has to enable under Tenant settings → Developer settings in the Fabric admin portal, as in the screenshot at the top of this page. Without it, query fails with an authorization error on the first call.

See also

Learn more