Tabular export, import and batch operations
rn_forge.fastapi.transfer serves spreadsheet download and upload, an import
template, and batch get, create, update and delete. It needs the transfer
extra (rn-forge-data, which brings tablib and openpyxl, and
python-multipart) and is not imported by rn_forge.fastapi. The wire contract
is §17 of rn-forge-web's API conventions. Persistence is the application's:
each route factory takes a store, a dependency that returns an object with
the methods the factory calls.
| Factory | Route | Store | Store methods |
|---|---|---|---|
import_router |
POST {collection}:import |
ImportStore |
import_rows(rows, *, validate_only) |
import_template_router |
GET {collection}:importTemplate |
TemplateSource |
rows() |
batch_create_router |
POST {collection}:batchCreate |
BatchCreateStore |
create_many(items) |
batch_delete_router |
POST {collection}:batchDelete |
BatchDeleteStore |
find(ids), delete_many(ids) |
batch_get_router |
GET {collection}:batchGet |
BatchGetStore |
get_many(ids) |
batch_update_router |
POST {collection}:batchUpdate |
BatchUpdateStore |
current(ids), update_many(items) |
The stores are structural protocols, so a store never imports them. A factory
validates the request, applies the row cap (max_rows) and calls the store only
with data that passed. The store makes the writes. Because the store is obtained
through Depends, it can take a database session, the principal or filter
parameters the ordinary FastAPI way. Every factory also takes dependencies=
for route-level dependencies such as authorization.
A complete resource
The code below is application code, not part of rn-forge. It joins
rn-forge-fastapi and rn-forge-sqlalchemy, which do not import each other:
the application's store calls upsert and keyset, and rn-forge-fastapi
calls the store. It serves /orders with a paged list, CSV and xlsx export,
import, import template, batch create and batch delete.
The model and the three wire models. OrderImport's aliases are the spreadsheet
headers, and it also serves as the export and template model, so a downloaded
file can be uploaded again.
from collections.abc import Sequence
from fastapi import Depends, FastAPI
from pydantic import ConfigDict, Field
from sqlalchemy import delete, select
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import Mapped, mapped_column
from rn_forge.fastapi import order_by_param, page_params, register_problem_handlers
from rn_forge.fastapi.transfer import (
batch_create_router,
batch_delete_router,
import_router,
import_template_router,
tabular_format,
tabular_export,
)
from rn_forge.sqlalchemy import AuditMixin, Base, keyset, next_page_token, upsert
from rn_forge.web import ItemsDenied, Page, WireModel
from rn_forge.web.transfer import ImportCounts
class Order(AuditMixin, Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
order_no: Mapped[str] = mapped_column(unique=True)
name: Mapped[str]
quantity: Mapped[int]
class OrderImport(WireModel): # columns: the spreadsheet's headers
model_config = ConfigDict(populate_by_name=True)
order_no: str = Field(alias="Order No", serialization_alias="Order No")
name: str = Field(alias="Name", serialization_alias="Name")
quantity: int = Field(alias="Quantity", serialization_alias="Quantity")
class OrderCreate(WireModel): # one item of :batchCreate
order_no: str
name: str
quantity: int = 0
class OrderOut(WireModel): # one created or listed order
order_no: str
name: str
quantity: int
The store. It opens its own session for each operation, so each operation is one transaction that it commits or discards itself.
SORT_ATTRS = {"orderNo": "order_no", "name": "name"}
class OrderStore:
"""Implements ImportStore, TemplateSource, BatchCreateStore and BatchDeleteStore."""
def __init__(self, sessions: async_sessionmaker[AsyncSession], actor: str) -> None:
self.sessions = sessions
self.actor = actor
async def import_rows(
self, rows: Sequence[OrderImport], *, validate_only: bool
) -> ImportCounts:
async with self.sessions() as session:
counts = await upsert(
session,
Order,
[row.model_dump(by_alias=False) for row in rows],
key=["order_no"],
fields=["name", "quantity"],
actor=self.actor,
)
if validate_only:
await session.rollback()
else:
await session.commit()
return ImportCounts(counts.created, counts.updated, counts.skipped)
async def rows(self) -> Sequence[Order]:
async with self.sessions() as session:
return (await session.scalars(select(Order).order_by(Order.id))).all()
async def create_many(self, items: Sequence[OrderCreate]) -> Sequence[Order]:
denied = {
str(i): ["You may not create this order."]
for i, item in enumerate(items)
if item.name.startswith("internal-")
}
if denied:
raise ItemsDenied(denied, root="requests")
created = [Order(**item.model_dump(by_alias=False), created_by=self.actor) for item in items]
async with self.sessions() as session:
session.add_all(created)
await session.commit()
return created
async def find(self, ids: Sequence[str]) -> list[str]:
async with self.sessions() as session:
stmt = select(Order.order_no).where(Order.order_no.in_(ids))
return list(await session.scalars(stmt))
async def delete_many(self, ids: Sequence[str]) -> None:
async with self.sessions() as session:
await session.execute(delete(Order).where(Order.order_no.in_(ids)))
await session.commit()
async def page(self, size, cursor, terms) -> Page[OrderOut]:
stmt = keyset(
select(Order),
columns={"orderNo": Order.order_no, "name": Order.name},
terms=terms,
cursor=cursor,
id_column=Order.id,
)
async with self.sessions() as session:
found = (await session.scalars(stmt.limit(size + 1))).all()
window = found[:size]
token = None
if len(found) > size:
last = window[-1]
values = [getattr(last, SORT_ATTRS[t.field]) for t in terms]
token = next_page_token(values, last.id, terms)
return Page[OrderOut](
items=[OrderOut.model_validate(o, from_attributes=True) for o in window],
next_page_token=token,
)
The wiring.
def build_app() -> FastAPI:
engine = create_async_engine("sqlite+aiosqlite://")
sessions = async_sessionmaker(engine, expire_on_commit=False)
def order_store() -> OrderStore:
return OrderStore(sessions, actor="alice")
app = FastAPI()
register_problem_handlers(app)
app.include_router(import_router("/orders", model=OrderImport, store=order_store, max_rows=5000))
app.include_router(import_template_router("/orders", model=OrderImport, source=order_store, max_rows=5000))
app.include_router(batch_create_router("/orders", model=OrderCreate, response_model=OrderOut, resource_name="orders", store=order_store, max_rows=100))
app.include_router(batch_delete_router("/orders", resource_label="Order", store=order_store, max_rows=100))
@app.get("/orders")
async def list_orders(
fmt=Depends(tabular_format(("csv", "xlsx"))),
params=Depends(page_params(cap=100, default=50)),
terms=Depends(order_by_param(allowed=["orderNo", "name"])),
store: OrderStore = Depends(order_store),
):
if fmt is not None:
return await tabular_export(await store.rows(), OrderImport, fmt, f"orders.{fmt.extension}", max_rows=5000, instance="/orders")
return await store.page(*params, terms)
return app
All or nothing, and validateOnly
Each store method does all its writes in one session and commits once, at the end:
import_rowsrunsupsert, which never commits. A real import then commits.validateOnlyrolls back instead, so the report shows the truecreated,updatedandskippedcounts and nothing is persisted. Any exception leaves theasync withblock before the commit, which discards the session's work.create_manychecks every item for permission before it adds any, and raisesItemsDeniedif one is refused: a 403 whoseerrors[].pointeris/requests/<i>. A database error before the commit discards the batch the same way.delete_manyis oneDELETEand one commit. The factory has already calledfindand answered a 404 for the first id that does not exist.
The factories do not open transactions, since they do not know the persistence
layer. A store that shares a request-scoped session instead must roll that
session back itself when it fails or when validate_only is set.
A row that fails a check only the store can make, such as a foreign-key lookup,
is reported by raising RowsInvalid(errors) (from rn_forge.web, built from
rn_forge.web.transfer.RowError) from import_rows. It renders the same 422 as
a cell that failed validation, pointing at /rows/<row>/<column>.
Batch get and update
batch_get_router reads a repeated ids query parameter. The store's
get_many(ids) returns the rows that exist within the caller's scope, keyed by
id as a string. batch_update_router takes
{"requests": [{"id", "patch", "ifMatch"}]}: the store's current(ids) returns
each existing row as an object with row and version (None when the
resource is unversioned), the factory merges each patch into the row dumped
through model by alias and validates the result as model, and
update_many(items) receives (id, model instance) pairs only after every
check has passed. It writes them in one transaction, bumps each version and
returns the updated rows in the order given.
app.include_router(batch_get_router("/orders", response_model=OrderOut, resource_name="orders", resource_label="Order", store=order_store, max_rows=100))
app.include_router(batch_update_router("/orders", model=OrderDocument, response_model=OrderOut, resource_name="orders", resource_label="Order", store=order_store, max_rows=100, require_if_match=True))
update_many raises ItemsDenied(errors, root="requests") before writing to
refuse an item. codec= changes the validator format of ifMatch, which
defaults to VersionETagCodec.
What the factories answer
| Request | Response |
|---|---|
:import succeeds |
200 {created, updated, skipped, validateOnly} |
:import has no file part |
422, errors[].pointer /file, This field is required. |
:import has an unsupported or unreadable file |
422 at /file with the shared detail |
:import has a failed cell |
422, errors[].pointer /rows/<row>/<column>; the store is not called |
:import or a batch is over max_rows |
422, The import exceeds the limit of N rows. or The batch exceeds the limit of N rows., no errors member |
a batch request has no requests or ids member |
422 at /requests or /ids, This field is required. |
| the member is an empty list or not a list | 422 at the member, A non-empty list is required. |
:batchCreate item fails validation |
422, every failed field at /requests/<i>/<field>; the store is not called |
:batchCreate raises ItemsDenied |
403, errors[].pointer /requests/<i> |
:batchCreate succeeds |
200 {"<resource_name>": [...]} of response_model dumped by alias |
:batchGet has no non-empty ids, or more than max_rows |
400, A non-empty ids parameter is required. or The batch exceeds the limit of N rows. |
:batchGet or :batchUpdate names an unknown id |
404, {resource_label} {id} not found |
:batchGet succeeds |
200 {"<resource_name>": [...]} in request order, duplicates repeated |
:batchUpdate item is malformed or repeats an id |
422, every failing item at /requests/<i>/id, /patch or /ifMatch; the store is not called |
:batchUpdate precondition fails |
428, 400 or 412, errors[].pointer /requests/<i>/ifMatch |
:batchUpdate merged document is invalid |
422, every failed field at /requests/<i>/patch/<field>; the store is not asked to write |
:batchUpdate raises ItemsDenied |
403, errors[].pointer /requests/<i> |
:batchUpdate succeeds |
200 {"<resource_name>": [...]} of response_model dumped by alias, in request order |
:batchDelete names an unknown id |
404, {resource_label} {id} not found |
:batchDelete succeeds |
204 |
These need register_problem_handlers(app), which renders RowsInvalid,
ItemsDenied and the request validation errors as application/problem+json.
Export
tabular_format() is a dependency that negotiates Accept or ?format=. A
None result means the client wants the JSON list. tabular_export answers a
tabular request from a list route: the file, or a 422 when there are more rows
than max_rows.
The export model is a pydantic model. Its serialization_aliases are the column
headers, and a computed_field is a column, which covers foreign-key traversal,
full names and enum display names. Rows are validated with
from_attributes=True, so ORM objects work directly. CSV is streamed in chunks
of 1,000 rows; xlsx is written with rn_forge.data.write_xlsx.
tabular_response is the same without the cap, for a route that applies its
own.
Reading a file yourself
read_rows loads an UploadFile (csv, tsv or xlsx) and validates each row
against a pydantic model whose aliases are the external headers. A bad cell is
never raised: RowsResult.errors holds a RowError for each, valid holds the
rows that passed and total counts the data rows. An unsupported or unreadable
file raises ValueError with the shared detail. The tablib work and per-row
validation run in a worker thread. A route that needs more than import_router
offers can call it and answer with problem_response(row_errors_problem(...)).
Paging a SQLAlchemy list
order_by_param and page_params parse the request, rn-forge-sqlalchemy's
keyset applies them to a Select, and next_page_token writes the next
token. The page method of the store above does this: it fetches size + 1
rows to learn whether another page exists. keyset takes the sort column and
the unique id column, so the order is stable when the sort column repeats.
Conformance
tests/test_conformance.py serves /conformance/orders* and
/conformance/capped* through these factories over one in-memory store, and
passes every transfer.* case in the shared table. The rn-forge-sqlalchemy
test suite serves an application over SQLite and passes the pagination and
import cases.