Dv Query

# Jupyter Notebook Setup

> **Auth note:** Notebooks do not have a `scripts/` directory, so `scripts/auth.py` is not available. Use `InteractiveBrowserCredential` directly — this is the intended exception to the `scripts/auth.py` rule. For scripts (`.py` files), always use `scripts/auth.py`.

```python
# Cell 1: Setup
import os
from azure.identity import InteractiveBrowserCredential
from PowerPlatform.Dataverse.client import DataverseClient

credential = InteractiveBrowserCredential()
client = DataverseClient(
    base_url="https://<org>.crm.dynamics.com",  # replace with your org URL
    credential=credential,
)

# Cell 2: Load data into pandas (direct DataFrame, no manual iteration)
df = client.dataframe.get("account",
    select=["name", "industrycode", "revenue", "numberofemployees"],
)
df.head()
```

**Prerequisites:**
```bash
pip install --upgrade PowerPlatform-Dataverse-Client pandas matplotlib seaborn azure-identity
```

`pandas>=2.0.0` is a required dependency of the SDK (since b7) and is installed automatically with `--upgrade`.


# QueryBuilder — Fluent Query API (SDK b8+)

> **Version check:** QueryBuilder requires SDK version b8 or later (`pip show PowerPlatform-Dataverse-Client` → Version ≥ 0.1.0b8). If you're on b7 or earlier, `client.query.builder()` does not exist — use `client.records.get()` instead (documented above). Do NOT introspect the SDK with `dir()` or `inspect` to discover APIs — if a method isn't documented here, it doesn't exist in the installed version.

QueryBuilder offers composable filters, OR/AND logic, and `.to_dataframe()` in one chain. It calls `client.records.get()` internally — it is a convenience layer, not a replacement.

```python
# Basic — flat record iteration
for record in client.query.builder("opportunity") \
        .select("name", "estimatedvalue", "statuscode") \
        .filter_eq("statuscode", 1) \
        .order_by("estimatedvalue", descending=True) \
        .top(100) \
        .execute():
    print(record["name"], record["estimatedvalue"])
```

**Direct DataFrame result** — combines query + pandas handoff in one call:

```python
df = client.query.builder("opportunity") \
    .select("name", "estimatedvalue", "statuscode") \
    .filter_eq("statuscode", 1) \
    .to_dataframe()
```

**Composable filter expressions** — for OR/AND logic:

```python
from PowerPlatform.Dataverse.models.filters import eq, gt

active_or_pending = (eq("statecode", 0) | eq("statecode", 1)) & gt("estimatedvalue", 10000)

df = client.query.builder("opportunity") \
    .select("name", "estimatedvalue") \
    .where(active_or_pending) \
    .to_dataframe()
```

**Paged execution** — when you need per-page control:

```python
for page in client.query.builder("opportunity").select("name").execute(by_page=True):
    for record in page:
        print(record["name"])
```

---

## Pandas DataFrame Handoff

**Prefer `client.dataframe.get()` for any read that involves analysis, verification, comparison, or export.** Use `client.records.get()` with page iteration only when you need per-page processing (e.g., streaming to a file) or when the table is too large to fit in memory.

| Task | Use | Why |
|---|---|---|
| Aggregate, group, pivot | `client.dataframe.get()` | pandas does this natively |
| Compare counts after import | `client.records.get()` with single-column select | Page-count is memory-efficient; no need to load full DataFrame for a count |
| Build a lookup map (small table) | `client.dataframe.get()` | `dict(zip(df["src_id"], df["guid"]))` — 1 line |
| Build a lookup map (100K+ rows) | `client.records.get()` | Page iterator uses less memory |
| Export to CSV/Excel | `client.dataframe.get()` | `df.to_csv("out.csv")` |
| Stream large result to file | `client.records.get()` | Page-at-a-time avoids loading all into memory |
| Cross-table join/aggregation | `client.dataframe.get()` both tables with `$select` + `pd.merge()` | pandas merge is sub-second; use `$select` to minimize network transfer |

**Always