156 lines
4.7 KiB
Plaintext
156 lines
4.7 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "markdown",
|
|
"metadata": {},
|
|
"source": [
|
|
"# Minimal Postgres Demo\n",
|
|
"\n",
|
|
"This notebook connects to the dockerized Postgres instance defined in `docker/docker-compose.yml`.\n",
|
|
"\n",
|
|
"Steps before running the cells:\n",
|
|
"\n",
|
|
"1. Start Postgres: `docker compose -f docker/docker-compose.yml up -d`.\n",
|
|
"2. Export environment variables so `sqlmodel_pg_kit` can locate the database (host defaults to `localhost` when run from the host).\n",
|
|
" ```bash\n",
|
|
" export SQL_HOST=localhost\n",
|
|
" export SQL_PORT=5432\n",
|
|
" export SQL_USER=appuser\n",
|
|
" export SQL_PASSWORD=changeme\n",
|
|
" export SQL_DATABASE=appdb\n",
|
|
" export SQL_SSLMODE=disable\n",
|
|
" ```\n",
|
|
"3. `pip install -e .` or `uv pip install .` from the project root.\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 1,
|
|
"id": "44df8c21",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"from typing import Optional, List\n",
|
|
"import os\n",
|
|
"os.environ[\"SQL_HOST\"] = \"127.0.0.1\"\n",
|
|
"os.environ[\"SQL_PORT\"] = \"5432\"\n",
|
|
"os.environ[\"SQL_USER\"] = \"appuser\"\n",
|
|
"os.environ[\"SQL_PASSWORD\"] = \"changeme\"\n",
|
|
"os.environ[\"SQL_DATABASE\"] = \"appdb\"\n",
|
|
"os.environ[\"SQL_SSLMODE\"] = \"disable\"\n",
|
|
"from sqlalchemy import func\n",
|
|
"from sqlmodel import Field, SQLModel, select\n",
|
|
"\n",
|
|
"from sqlmodel_pg_kit import Repository, create_all, get_session\n",
|
|
"\n",
|
|
"\n",
|
|
"class Widget(SQLModel, table=True):\n",
|
|
" id: Optional[int] = Field(default=None, primary_key=True)\n",
|
|
" name: str\n",
|
|
" in_stock: bool = True\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "23860bb5",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Created: in_stock=True id=6 name='rocket'\n",
|
|
"Fetched by primary key: in_stock=True name='rocket' id=6\n",
|
|
"First page of in-stock widgets: [(9, 'probe'), (7, 'satellite')]\n",
|
|
"Search 'ro' -> [(9, 'probe'), (6, 'rocket')]\n",
|
|
"In-stock count: 3\n",
|
|
"Updated: in_stock=False id=6 name='rocket'\n",
|
|
"Deleted: True\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"create_all()\n",
|
|
"repo = Repository(Widget)\n",
|
|
"\n",
|
|
"with get_session() as session:\n",
|
|
" session.execute(Widget.__table__.delete())\n",
|
|
" session.commit()\n",
|
|
"\n",
|
|
" widget = repo.create(session, {\"name\": \"rocket\"})\n",
|
|
" print(\"Created:\", widget)\n",
|
|
"\n",
|
|
" repo.bulk_insert(\n",
|
|
" session,\n",
|
|
" [\n",
|
|
" {\"name\": \"satellite\", \"in_stock\": True},\n",
|
|
" {\"name\": \"capsule\", \"in_stock\": False},\n",
|
|
" {\"name\": \"probe\", \"in_stock\": True},\n",
|
|
" ],\n",
|
|
" )\n",
|
|
"\n",
|
|
" same = repo.get(session, widget.id)\n",
|
|
" print(\"Fetched by primary key:\", same)\n",
|
|
"\n",
|
|
" page = repo.list(\n",
|
|
" session,\n",
|
|
" where=Widget.in_stock.is_(True),\n",
|
|
" order_by=[Widget.id.desc()],\n",
|
|
" page=1,\n",
|
|
" size=2,\n",
|
|
" )\n",
|
|
" print(\"First page of in-stock widgets:\", [(w.id, w.name) for w in page])\n",
|
|
" search_term = \"ro\"\n",
|
|
" search_stmt = (\n",
|
|
" select(Widget)\n",
|
|
" .where(Widget.name.ilike(f\"%{search_term}%\"))\n",
|
|
" .order_by(Widget.name.asc())\n",
|
|
" )\n",
|
|
" matches = session.exec(search_stmt).all()\n",
|
|
" print(f\"Search '{search_term}' ->\", [(w.id, w.name) for w in matches])\n",
|
|
"\n",
|
|
" count_stmt = select(func.count(Widget.id)).where(Widget.in_stock.is_(True))\n",
|
|
" # scalar() keeps compatibility across SQLAlchemy versions\n",
|
|
" inventory_count = session.exec(count_stmt).scalar() or 0\n",
|
|
" print(\"In-stock count:\", inventory_count)\n",
|
|
"\n",
|
|
" updated = repo.update(session, widget.id, in_stock=False)\n",
|
|
" print(\"Updated:\", updated)\n",
|
|
"\n",
|
|
" removed = repo.delete(session, widget.id)\n",
|
|
" print(\"Deleted:\", removed)\n"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "7637a17d",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": []
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "sqlmodel",
|
|
"language": "python",
|
|
"name": "python3"
|
|
},
|
|
"language_info": {
|
|
"codemirror_mode": {
|
|
"name": "ipython",
|
|
"version": 3
|
|
},
|
|
"file_extension": ".py",
|
|
"mimetype": "text/x-python",
|
|
"name": "python",
|
|
"nbconvert_exporter": "python",
|
|
"pygments_lexer": "ipython3",
|
|
"version": "3.12.11"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|