Debian Codegraph @

docs/examples/contrib/sqlalchemy/sqlalchemy_repository_bulk_operations.py

raw
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()