dv-query

# Skill: Query — Read and Analyze Dataverse Records > **This skill uses Python and the Dataverse CLI.** Do not use Node.js, JavaScript, or any other language for Dataverse scripting. See the overview skill's Hard Rules. ## Reads: prefer a managed surface, choose by shape **Fast path for simple reads:** If `dataverse auth who` shows an active profile, skip workspace setup and query directly with the CLI examples below. No `.env`, `auth.py`, pip install, or PAC needed for data reads. Pick **MCP, the Dataverse CLI, or the SDK by the shape of the read** — all three handle auth and retry (see the routing table below and the overview's **Tool Capabilities** / Hard Rule 2). MCP fits small, interactive reads; the CLI fits headless one-liners (OData, SQL, count); the SDK fits bulk iteration and analytics. For `$apply` aggregation and N:N `$expand`, prefer `client.query.fetchxml()` (aggregates + link-entity) or the managed `dataverse api` escape hatch; reach for hand-rolled `urllib`/`get_token()` **only** to stay in-process inside a tight Python loop (e.g. paging thousands of rows with client-side processing — see web-api-advanced.md). ### Dataverse CLI gotchas (custom tables + Windows) When you drive the `dataverse` CLI directly (headless reads/CRUD; note the CLI needs .NET + a keyring, so it is blocked on ChatGPT web / Codex cloud — use the SDK there), two empirical traps: - **Custom-table SQL pluralization.** `dataverse data query` in SQL mode auto-pluralizes the table name, and irregular plurals resolve wrong: `FROM im_category` looks up entity set `im_categorys` and returns a **404** that reads like "table missing." It is not — switch to OData mode with the explicit entity set: `dataverse data query --table im_categories --select im_name`. Discover the real `EntitySetName` from `EntityDefinitions` when unsure; never conclude the table doesn't exist from this 404. - **Windows shell quoting.** Wrap the whole `--path` value in double quotes so `cmd.exe`/PowerShell don't treat `&` as a command separator. Keep `&` **literal** — it separates OData query options; encoding it to `%26` merges them and breaks the query. Encode only `$`->`%24` (in PowerShell a bare `$select` is read as a variable). If an unquoted `&` splits the command, the wrapper can exit nonzero *even when the API returned valid JSON* — quoting prevents it. (This is why the `dataverse api request` examples in other skills quote the path, use `%24`, and leave `&` literal.) ### Dataverse CLI query examples (copy-paste ready) All `dataverse` commands take `--context` for skill attribution (global flag). ```bash # OData filtered read (--table takes the EntitySet name, e.g. accounts not account) dataverse data query --table accounts --select "name,accountid" --filter "name eq 'john'" --top 10 --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>" # Count records dataverse data count --table accounts --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>" # SQL mode (uses the logical name, e.g. account not accounts) dataverse data query --sql "SELECT name, accountid FROM account WHERE name LIKE '%john%'" --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>" # Get single record by ID dataverse data get --table accounts --id <guid> --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>" # Raw API escape hatch dataverse api request --target dataverse --path "/api/data/v9.2/accounts?%24select=name&%24top=5" --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>" ``` **ERP target is a separate path.** ERP (Finance and Operations), when linked to a Dataverse env, does not go through the Python SDK. See [`references/erp-reads.md`](references/erp-reads.md). ## How to Answer Data Questions When the user asks a question about their data, pick the approach by **what they're asking**, not by which API you know: | User asks... | Approach | Why | |---|---|---| | "show me open tickets" / simple filter | **MCP** `read_query`, **CLI** `dataverse data query --table ... --filter ...`, or `client.records.list(table, filter=...)` | Small result, no aggregation | | "how many X" / simple count | **CLI** `dataverse data count --table ...`, **MCP** `read_query`, or `client.query.sql("SELECT COUNT(*) ...")` | Server-side count (no row download) | | Single-table aggregation (most/sum/avg/top-N) | **`$apply`** (raw) or **`client.query.sql()`** GROUP BY | Both run server-side, return only grouped results | | Cross-table aggregation | **`client.query.sql("...INNER JOIN...GROUP BY...")`** or **`client.query.fetchxml(...)`** (server-side); else builder->DataFrame + `pd.merge()` | `sql()` supports INNER/LEFT JOIN + GROUP BY; pandas merge for shapes SQL can't express | | "show me X with related Y" / resolve lookups | `client.records.list(table, expand=...)` or **QueryBuilder** | Lookup resolution | | "export this data" / bulk extract | **`client.query.builder(t).select(...).execute().to_dataframe()`** | Direct to DataFrame → CSV | | "load into notebook" / interactive analysis | **`client.query.builder(t).select(...).execute().to_dataframe()`** | pandas native | | "find duplicates" / complex filter | `client.records.list(table, filter=...)` or **QueryBuilder** | SDK handles pagination | | Simple filtered read (<5K rows) | **CLI** `dataverse data query --sql "SELECT ..."`, or **`client.query.sql()`** | Lightweight single call | **Key principle:** Let the server do the work. For single-table aggregation, use `$apply` (raw) or `client.query.sql()` GROUP BY — both run server-side and return only grouped results. For cross-table questions, prefer a server-side `sql()` JOIN (INNER/LEFT) or `fetchxml()` link-entity; when SQL can't express it, pull each table via `client.query.builder(t).select(...).execute().to_dataframe()` and `pd.merge()` — the merge is sub-second; the bottleneck is network transfer, which `select` minimizes. **Always query the live Dataverse environment.** Do not query local copies, cached files, or source databases when the user expects results from Dataverse. The data in Dataverse is the source of truth. --- ## SQL Queries — `client.query.sql()` `client.query.sql()` uses the Dataverse Web API `?sql=` parameter — a **T-SQL subset**. It **supports** `SELECT` / `SELECT DISTINCT` / `SELECT TOP N` (0-5000), `INNER JOIN` / `LEFT JOIN`, `WHERE`, `GROUP BY`, `ORDER BY`, `OFFSET`/`FETCH`, and `COUNT/SUM/AVG/MIN/MAX`. It does **NOT** support `SELECT *`, subqueries, CTEs, `HAVING`, `UNION`, `RIGHT`/`FULL`/`CROSS JOIN`, `CASE`, or string/date/math functions. Results are capped at ~5,000 rows. **When to use:** Fast filtered reads on tables with <5K rows. For these, it's significantly faster (~2-6s) than page iteration or DataFrames because it's a single HTTP call. ```python # Fast filtered read on small tables (<5K rows) results = client.query.sql( "SELECT TOP 100 name, estimatedvalue " "FROM opportunity " "WHERE statecode = 0 " "ORDER BY estimatedvalue DESC" ) for r in results: print(f"{r['name']}: ${r.get('estimatedvalue', 0):,.0f}") ``` **Do NOT use for:** Tables >5K rows (results silently truncated), `SELECT *`, subqueries/CTEs, `HAVING`, `UNION`, `RIGHT`/`FULL`/`CROSS JOIN`, or functions. `INNER`/`LEFT JOIN` and `GROUP BY` **are** supported — use them for server-side joins/aggregation on <5K-row results; for larger or unsupported shapes use `fetchxml()` or `$apply`. ## FetchXML — server-side joins and aggregates For SQL-JOIN scenarios or aggregates the OData builder cannot express, use FetchXML. `client.query.fetchxml(xml)` returns an inert query object — no HTTP is made until you call `.execute()` (eager, all pages) or `.execute_pages()` (lazy, one page at a time). Both return `QueryResult` pages with `.to_dataframe()`. ```python query = client.query.fetchxml(""" <fetch top="50"> <entity name="account"> <attribute name="name" /> <link-entity name="contact" from="parentcustomerid" to="accountid" alias="c" link-type="inner"> <attribute name="fullname" /> </link-entity> </entity> </fetch> """) result = query.execute() # collect all pages df = result.to_dataframe() # Or stream one page at a time for large results: for page in query.execute_pages(): print(page.to_dataframe().shape) ``` ## Discover queryable columns — `client.query.sql_columns()` Before writing a SQL or `$select` read, list the columns the SQL endpoint can actually query — virtual and computed lookup-display columns are excluded. Each entry has `name`, `type`, `is_pk`, `is_name`, and `label`. ```python for c in client.query.sql_columns("account"): print(f"{c['name']:30s} {c['type']:20s} PK={c['is_pk']}") ``` For deeper schema inspection — full column metadata and table relationships — use `dv-metadata` (`client.tables.list_columns()`, `client.tables.list_relationships()`, `client.tables.list_table_relationships()`). ## Skill boundaries | Need | Use instead | |---|---| | Create, update, delete records (Dataverse) | **dv-data** | | Query, create, update, delete records (ERP) | See [`references/erp-reads.md`](references/erp-reads.md) and [`erp-writes`](../dv-data/references/erp-writes.md) | | Create tables, columns, relationships | **dv-metadata** | | Export or deploy solutions | **dv-solution** | --- ## Setup ```python import os, sys sys.path.insert(0, os.path.join(os.getcwd(), "scripts")) from auth import get_client # get_client sets a plugin attribution context on the User-Agent header. # Do not modify the context value — it is a closed schema for server-side # telemetry (app/skill/agent). Never include secrets or PII. client = get_client("dv-query") ``` `get_client(skill)` handles auth, environment URL, and plugin attribution (User-Agent tagging). See `scripts/auth.py`. For scripts that run to completion, wrap the returned client in a `with` statement for automatic connection cleanup. For ERP, use ERP MCP or the Dataverse CLI `--target erp` path — see [`references/erp-reads.md`](references/erp-reads.md). --- ## Field Name Casing Rule Getting this wrong causes 400 errors. | Property type | Convention | Example | When used | |---|---|---|---| | **Structural** (columns) | LogicalName — always lowercase | `new_name`, `new_priority` | `$select`, `$filter`, `$orderby` | | **Navigation** (lookups) | Navigation Property Name — case-sensitive, matches `$metadata` | `new_AccountId` | `$expand` | - System table navigation properties (e.g., `parentaccountid`, `ownerid`): lowercase - Custom lookup navigation properties: case-sensitive, match `$metadata` SchemaName (e.g., `new_AccountId`) --- ## Query Records `client.records.list()` is the primary read method on the GA SDK. It collects all pages and returns a flat `QueryResult` you iterate directly (records, not pages). For very large result sets, `client.records.list_pages()` streams one `QueryResult` per HTTP page. **Always use `select=` to limit columns.** ```python # list() -- flat QueryResult, iterate records directly result = client.records.list( "new_ticket", select=["new_name", "new_priority", "new_status"], filter="new_status eq 100000000", orderby=["new_name asc"], top=50, ) for r in result: print(r["new_name"], r["new_priority"]) print(f"{len(result)} tickets") # QueryResult supports len(), indexing, .first(), .to_dataframe() ``` For large tables where you do not want every row in memory at once, stream pages: ```python for page in client.records.list_pages("new_ticket", select=["new_name"], page_size=200): for r in page: # each page is a QueryResult print(r["new_name"]) ``` Each record is a `Record` object that supports dict-like access: `r["column"]`, `r.get("column")`, `r.keys()`. Do not use `r.data.get()` -- use `r.get()` directly.