Query data

This article describes methods for querying Dataverse data by using the SDK for Python. You can query data by using Structured Query Language (SQL) and OData-based APIs.

Python developers should first learn about the SDK for Python by reading Getting started before continuing with this article.

QueryBuilder

QueryBuilder is the recommended way to query records. It provides a fluent, type-safe interface that generates correct OData queries automatically. You don't need to remember OData filter syntax.

# Fluent query builder (recommended)
from PowerPlatform.Dataverse.models.filters import col

for record in (client.query.builder("account")
               .select("name", "revenue")
               .where(col("statecode") == 0)
               .where(col("revenue") > 1000000)
               .order_by("revenue", descending=True)
               .top(100)
               .page_size(50)
               .execute()):
    print(f"{record['name']}: {record['revenue']}")

QueryBuilder handles value formatting, column name casing, and OData syntax automatically. Build filter expressions with col() and standard Python operators.

# Get results as a pandas DataFrame (consolidates all pages)
df = (client.query.builder("account")
      .select("name", "telephone1")
      .where(col("statecode") == 0)
      .top(100)
      .execute()
      .to_dataframe())
print(f"Got {len(df)} accounts")

# Comparison filters using col() expressions
query = (client.query.builder("contact")
         .where(col("statecode") == 0)                        # statecode eq 0
         .where(col("revenue") > 1000000)                     # revenue gt 1000000
         .where(col("name").contains("Corp"))                 # contains(name, 'Corp')
         .where(col("statecode").in_([0, 1]))                 # Microsoft.Dynamics.CRM.In(...)
         .where(col("revenue").between(100000, 500000))       # revenue ge 100000 and revenue le 500000
         .where(col("telephone1").is_null())                  # telephone1 eq null
         )

String filters

Use .contains(), .startswith(), and .endswith() for substring matching. Or use .like() with a % wildcard pattern. The SDK compiles that pattern to the closest equivalent filter.

query = (client.query.builder("contact")
         .where(col("email").contains("outlook.com"))    # contains(email, 'outlook.com')
         .where(col("lastname").startswith("Smith"))      # startswith(lastname, 'Smith')
         .where(col("firstname").endswith("son")))        # endswith(firstname, 'son')

query = client.query.builder("account").where(col("name").like("Contoso%"))

Negation and set membership

Each positive operator has a negated counterpart: .is_not_null(), .not_in(), and .not_between().

query = (client.query.builder("contact")
         .where(col("telephone1").is_not_null())          # telephone1 ne null
         .where(col("statecode").not_in([2, 3]))          # not in set
         .where(col("creditlimit").not_between(0, 100)))  # outside range

Raw OData escape hatch

When you need an OData function or syntax that col() doesn't cover, use raw(). It's the intended fallback for OData expressions that have no typed equivalent.

from PowerPlatform.Dataverse.models.filters import col, raw

query = (client.query.builder("account")
         .where(col("statecode") == 0)
         .where(raw("Microsoft.Dynamics.CRM.Today(PropertyName='createdon')")))

For complex logic (OR, NOT, grouping), compose expressions with &, |, ~:

from PowerPlatform.Dataverse.models.filters import col

# OR conditions: (statecode = 0 OR statecode = 1) AND revenue > 100k
for record in (client.query.builder("account")
               .select("name", "revenue")
               .where(((col("statecode") == 0) | (col("statecode") == 1))
                      & (col("revenue") > 100000))
               .execute()):
    print(record["name"])

# NOT, between, and in operators
for record in (client.query.builder("account")
               .where(col("statecode") != 2)                       # NOT inactive
               .where(col("revenue").between(100000, 500000))      # revenue in range
               .execute()):
    print(record["name"])

Formatted values and annotations

This example shows how to request localized labels, currency symbols, and display names.

# Get formatted values (choice labels, currency, lookup names) — via query builder
for record in (client.query.builder("account")
               .select("name", "statecode", "revenue")
               .include_formatted_values()
               .execute()):
    status = record["statecode@OData.Community.Display.V1.FormattedValue"]
    print(f"{record['name']}: {status}")

# Get formatted values — via records.list() / records.retrieve() include_annotations param
result = client.records.list(
    "account",
    select=["name", "statecode"],
    include_annotations="OData.Community.Display.V1.FormattedValue",
)
for record in result:
    label = record.get("statecode@OData.Community.Display.V1.FormattedValue")
    print(f"{record['name']}: {label}")

record = client.records.retrieve(
    "account", account_id,
    select=["name", "statuscode"],
    include_annotations="OData.Community.Display.V1.FormattedValue",
)
if record:
    print(record.get("statuscode@OData.Community.Display.V1.FormattedValue"))

To request all available annotations, call .include_annotations() on the builder (it defaults to "*"), or pass include_annotations="*" to records.list() and records.retrieve().

builder = (client.query.builder("account")
           .select("name", "_ownerid_value")
           .include_annotations())   # defaults to "*"

Expand navigation properties

Use nested expand with options to expand navigation properties by using $select, $filter, $orderby, and $top.

from PowerPlatform.Dataverse.models.query_builder import ExpandOption

# Expand related tasks with filtering and sorting
for record in (client.query.builder("account")
               .select("name")
               .expand(ExpandOption("Account_Tasks")
                       .select("subject", "createdon")
                       .filter("contains(subject,'Task')")
                       .order_by("createdon", descending=True)
                       .top(5))
               .execute()):
    print(record["name"], record.get("Account_Tasks"))

Paging

Use execute_pages() to stream large result sets with full builder options, such as filtering, sorting, and formatted values. For simpler string-based OData filter queries, use records.list() and records.list_pages() as shortcuts.

# Preferred: query.builder().execute_pages() — stream one page at a time, memory stays flat
# Supports composable filters, sorting, formatted values, and expand with nested selects
for page_num, page in enumerate(
    client.query.builder("account")
    .select("accountid", "name", "revenue")
    .where(col("statecode") == 0)
    .order_by("name")
    .page_size(500)        # optional: override Dataverse default (~5000/page)
    .execute_pages()
):
    print(f"Page {page_num + 1}: {len(page)} records")
    for record in page:
        print(f"  {record['name']}")

# Simple shortcut: records.list() — automatic paging, all records in memory
# Use for basic filter+select queries; string OData filter only (no composable expressions)
result = client.records.list(
    "account",
    filter="statecode eq 0",
    select=["name", "revenue"],
    orderby=["name asc"],          # optional sort
    top=500,                       # bounds total records returned and number of HTTP round-trips
    page_size=200,                 # optional: hint Dataverse default page size
)
for record in result:
    print(record["name"])

# Simple streaming shortcut: records.list_pages() — same params as records.list(), yields one page at a time
for page_num, page in enumerate(
    client.records.list_pages("account", filter="statecode eq 0", select=["name"], orderby=["name asc"])
):
    print(f"Page {page_num + 1}: {len(page)} records")
    for record in page:
        print(record["name"])

Record count

To get a record count, include $count=true in the request.

# Via query builder
results = (client.query.builder("account")
           .where(col("statecode") == 0)
           .count()
           .execute())

# Via records.list() — count=True adds $count=true to the OData request
results = client.records.list("account", filter="statecode eq 0", count=True)

FetchXML queries

Calling client.query.fetchxml() returns an inert FetchXmlQuery object. The method doesn't make an HTTP request until you call .execute() or .execute_pages().

xml = """
<fetch>
  <entity name="account">
    <attribute name="name"/>
    <attribute name="revenue"/>
    <filter><condition attribute="statecode" operator="eq" value="0"/></filter>
  </entity>
</fetch>
"""

# .execute() — blocking, fetches all pages and returns a single QueryResult
result = client.query.fetchxml(xml).execute()
df = result.to_dataframe()

# .execute_pages() — streaming, yields one QueryResult per HTTP page
# Use count="N" in the FetchXML <fetch> element to set page size
for page_num, page in enumerate(client.query.fetchxml(xml).execute_pages()):
    print(f"Page {page_num + 1}: {len(page)} records")
    for record in page:
        print(record["name"])

Simple list shortcut

The records.list() call accepts a raw OData filter string for basic queries. For anything beyond a simple filter and select, use client.query.builder(), which provides composable filters, formatted values, and nested expand.

# records.list() shortcut — raw OData filter string, all records loaded into memory
# Column names in filter must be lowercase logical names
for record in client.records.list(
    "account",
    select=["name"],
    filter="statecode eq 0",
    top=100,
):
    print(record["name"])

# Discover navigation property names for $expand (metadata-discovery helper, kept at GA)
nav_props = client.query.odata_expands("account")  # → list of navigation property metadata

# Expand navigation properties using the query builder
from PowerPlatform.Dataverse.models.query_builder import ExpandOption
for record in (client.query.builder("contact")
               .select("fullname")
               .expand(ExpandOption("parentcustomerid_account").select("name"))
               .execute()):
    acct = record.get("parentcustomerid_account") or {}
    print(f"{record['fullname']} -> {acct.get('name')}")

# Build @odata.bind for lookup fields (deprecated helper, still functional with DeprecationWarning)
bind = client.query.odata_bind("contact", "account", account_id)
# Returns: {"parentcustomerid_account@odata.bind": "/accounts(guid)"}
client.records.create("contact", {"firstname": "Jane", **bind})

Query data using SQL

Dataverse provides a read-only interface to a limited set of SQL SELECT commands. It supports SQL JOINs, aggregates, GROUP BY, DISTINCT, and OFFSET FETCH pagination.

You can also access the SQL query capability by using the Dataverse Web API's ?sql= parameter. This approach lets code written in languages other than Python access Dataverse data. For more information, see Use SQL to Query Data With the Dataverse Web API.

Supported SQL syntax

The SQL interface supports the following T-SQL subset.

The following statements or features aren't supported: SELECT *, subqueries, CTEs, HAVING, UNION, RIGHT JOIN, FULL JOIN, CROSS JOIN, CASE, COALESCE, window functions, string/date/math functions, and any write statement (INSERT, UPDATE, DELETE). Unsupported or write queries raise ValidationError before the request is sent. Queries the client can't validate, but the server rejects, raise HttpError.

The following example code demonstrates a SQL query in Python.

# Basic query
results = client.query.sql(
    "SELECT TOP 10 accountid, name FROM account WHERE statecode = 0"
)

# JOINs and aggregates work
results = client.query.sql(
    "SELECT a.name, COUNT(c.contactid) as cnt "
    "FROM account a "
    "JOIN contact c ON a.accountid = c.parentcustomerid "
    "GROUP BY a.name"
)

# SQL results directly as a DataFrame
df = client.dataframe.sql(
    "SELECT name, revenue FROM account ORDER BY revenue DESC"
)

# Discover columns from metadata (schema-discovery helper, kept at GA)
cols_meta = client.query.sql_columns("account")
col_names = [c["LogicalName"] for c in cols_meta]

# Build queries using the discovered column names
sql = f"SELECT TOP 10 {', '.join(col_names[:5])} FROM account"
df = client.dataframe.sql(sql)

Related information

• SDK for Python code examples
• SDK for Python README

See also

• Getting started
• Handle errors and enable HTTP diagnostics
• Use the Microsoft Dataverse Web API
• Use SQL to query data