| 1 |
import json |
| 2 |
from pathlib import Path |
| 3 |
from typing import Any |
| 4 |
|
| 5 |
from rich import get_console |
| 6 |
from sqlalchemy import create_engine |
| 7 |
from sqlalchemy.orm import Mapped, Session, sessionmaker |
| 8 |
|
| 9 |
from litestar.plugins.sqlalchemy import base, repository |
| 10 |
from litestar.repository.filters import LimitOffset |
| 11 |
|
| 12 |
here = Path(__file__).parent |
| 13 |
console = get_console() |
| 14 |
|
| 15 |
|
| 16 |
class USState(base.UUIDBase): |
| 17 |
# you can optionally override the generated table name by manually setting it. |
| 18 |
__tablename__ = "us_state_lookup" # type: ignore[assignment] |
| 19 |
abbreviation: Mapped[str] |
| 20 |
name: Mapped[str] |
| 21 |
|
| 22 |
|
| 23 |
class USStateRepository(repository.SQLAlchemySyncRepository[USState]): |
| 24 |
"""US State repository.""" |
| 25 |
|
| 26 |
model_type = USState |
| 27 |
|
| 28 |
|
| 29 |
engine = create_engine( |
| 30 |
"duckdb:///:memory:", |
| 31 |
future=True, |
| 32 |
) |
| 33 |
session_factory: sessionmaker[Session] = sessionmaker(engine, expire_on_commit=False) |
| 34 |
|
| 35 |
|
| 36 |
def open_fixture(fixtures_path: Path, fixture_name: str) -> Any: |
| 37 |
"""Loads JSON file with the specified fixture name |
| 38 |
|
| 39 |
Args: |
| 40 |
fixtures_path (Path): The path to look for fixtures |
| 41 |
fixture_name (str): The fixture name to load. |
| 42 |
|
| 43 |
Raises: |
| 44 |
FileNotFoundError: Fixtures not found. |
| 45 |
|
| 46 |
Returns: |
| 47 |
Any: The parsed JSON data |
| 48 |
""" |
| 49 |
fixture = Path(fixtures_path / f"{fixture_name}.json") |
| 50 |
if fixture.exists(): |
| 51 |
with fixture.open(mode="r", encoding="utf-8") as f: |
| 52 |
f_data = f.read() |
| 53 |
return json.loads(f_data) |
| 54 |
raise FileNotFoundError(f"Could not find the {fixture_name} fixture") |
| 55 |
|
| 56 |
|
| 57 |
def run_script() -> None: |
| 58 |
"""Load data from a fixture.""" |
| 59 |
|
| 60 |
# Initializes the database. |
| 61 |
with engine.begin() as conn: |
| 62 |
USState.metadata.create_all(conn) |
| 63 |
|
| 64 |
with session_factory() as db_session: |
| 65 |
# 1) Load the JSON data into the US States table. |
| 66 |
repo = USStateRepository(session=db_session) |
| 67 |
fixture = open_fixture(here, USStateRepository.model_type.__tablename__) # type: ignore |
| 68 |
objs = repo.add_many([USStateRepository.model_type(**raw_obj) for raw_obj in fixture]) |
| 69 |
db_session.commit() |
| 70 |
console.print(f"Created {len(objs)} new objects.") |
| 71 |
|
| 72 |
# 2) Select paginated data and total row count. |
| 73 |
created_objs, total_objs = repo.list_and_count(LimitOffset(limit=10, offset=0)) |
| 74 |
console.print(f"Selected {len(created_objs)} records out of a total of {total_objs}.") |
| 75 |
|
| 76 |
# 3) Let's remove the batch of records selected. |
| 77 |
deleted_objs = repo.delete_many([new_obj.id for new_obj in created_objs]) |
| 78 |
console.print(f"Removed {len(deleted_objs)} records out of a total of {total_objs}.") |
| 79 |
|
| 80 |
# 4) Let's count the remaining rows |
| 81 |
remaining_count = repo.count() |
| 82 |
console.print(f"Found {remaining_count} remaining records after delete.") |
| 83 |
|
| 84 |
|
| 85 |
if __name__ == "__main__": |
| 86 |
run_script() |