Metadata-Version: 2.4
Name: yjdata
Version: 0.2.3
Summary: yjdata SDK for Python
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: httpx>=0.25
Requires-Dist: polars>=1.30
Requires-Dist: pyarrow>=18
Requires-Dist: pydantic>=2.11

# yjdata

Python SDK for yjdata, including MySQL and PostgreSQL database adapters.

## Select the last row of each group

`MysqlAdapter` (MySQL 8.0+) and `PostgresAdapter` provide:

- `select_last_of_group(...) -> list[dict[str, Any]]`
- `select_last_of_group_polars(...) -> polars.DataFrame`

Both methods execute grouping, row selection, filtering, and projection in SQL
using `ROW_NUMBER()`. They accept the table name followed by keyword arguments:

| Parameter | Meaning |
| --- | --- |
| `table` | Table name, optionally qualified by schema or database. |
| `group_by: Sequence[str]` | Required nonempty sequence of grouping columns. |
| `sort_by: Sequence[str]` | Required nonempty sequence of sort columns, in priority order. |
| `ascending: bool \| Sequence[bool] = True` | One direction for every sort column, or one boolean per column. |
| `columns: Sequence[str] \| None = None` | Nonempty sequence of output columns; `None` returns all original columns. |
| `filter: Expression \| None = None` | Filter source rows before choosing a row per group. |
| `post_filter: Expression \| None = None` | Filter the chosen rows, before projecting output columns. |

The operation first applies `filter`, orders each group, selects its last complete
source row, applies `post_filter`, and finally projects `columns`. Values come
from the same row, including NULL values; this is not a separate last-non-NULL
aggregation for each column. A post-filter rejection removes the group entirely,
without falling back to an older row.

NULLs sort below all non-NULL values on both backends: first for ascending order
and last for descending order. Consequently, ascending selection prefers the
largest non-NULL value, while descending selection prefers NULL when present.
This rule applies to each sort column. NULL grouping keys group together.
For deterministic selection among ties, include a unique column in `sort_by`,
such as `sort_by=["change_time", "record_id"]`. Output order across groups is
unspecified.

Group, sort, and filter columns need not appear in `columns`. Internal ranking
columns are excluded by the SQL projection, even if the table has similarly
named columns. With `columns=None`, an additional zero-row query reads column
metadata so the adapter can explicitly project all original columns.
An empty result is `[]` for dictionaries or a DataFrame with no columns for
Polars, matching the existing selection methods. Polars infers types from all
returned rows.

### THS concept snapshots

For an existing adapter `client` and a cutoff value `cutoff`:

```python
import yjdata.db.expression as ye

data = client.select_last_of_group_polars(
    table="thsetl.concept_change_record",
    group_by=["concept_id"],
    sort_by=["change_time"],
    ascending=True,
    columns=["concept_id", "concept_name"],
    filter=(
        (ye.col("change_time") <= cutoff)
        & (ye.col("isvalid") == "1")
    ),
    post_filter=ye.col("change_type") != "conceptNegation",
)
```

This selects the latest valid change at or before the cutoff, then excludes
concepts whose selected change is a negation. The MySQL query has this form:

```sql
SELECT `concept_id`, `concept_name`
FROM (
    SELECT `concept_id`, `concept_name`, `change_type`,
           ROW_NUMBER() OVER (
               PARTITION BY `concept_id`
               ORDER BY (`change_time` IS NULL) ASC, `change_time` DESC
           ) AS `_yjdata_row_number`
    FROM `thsetl`.`concept_change_record`
    WHERE (`change_time` <= %s) AND (`isvalid` = %s)
) AS `_yjdata_latest`
WHERE `_yjdata_row_number` = 1 AND (`change_type` <> %s)
```

Values are bound in order: `[cutoff, "1", "conceptNegation"]`. PostgreSQL uses
the same query structure with double-quoted identifiers. Expressions retain
normal SQL NULL semantics: for example, `change_type <> 'conceptNegation'`
also excludes NULL `change_type` values.

## Batch writes

PostgreSQL inserts, upserts and updates use `psycopg2.extras.execute_batch`.
MySQL inserts and upserts use PyMySQL's optimized `executemany`. MySQL updates
combine driver-escaped UPDATE statements into one request, preserving input
order, repeated keys and ordinary `=` matching (NULL keys do not match).
MySQL connections enable `CLIENT.MULTI_STATEMENTS` for these update batches.

`batch_size` limits rows per batch. MySQL update batches also respect the
cursor's `max_stmt_length` in encoded bytes (normally about 1 MB); an individual
larger statement is sent alone and remains subject to the server's packet limit.
Every result is consumed so errors in later statements propagate before commit.
With the default transactional connection settings and transactional tables,
all batches commit together or roll back on failure. This reduces network
round trips; the server still executes one UPDATE per input row.

## Tests

```sh
python -m unittest discover -s tests -v
```

The grouped-selection tests exercise both adapters' validation, quoting,
parameter ordering, and result conversion. Generated queries execute against
SQLite's window engine with DB-API placeholders translated, so tests require
neither optional database drivers nor running servers. This does not replace
integration testing against MySQL and PostgreSQL.

With `pymysql` installed, the MySQL batch-update tests also exercise the real
driver's escaping, encoded request sizes and result handling without a server.
