mirror of
https://github.com/kennethreitz/responder.git
synced 2026-10-24 05:59:12 +00:00
426 lines
39 KiB
HTML
426 lines
39 KiB
HTML
<!DOCTYPE html>
|
||
|
||
<html lang="en" data-content_root="./">
|
||
<head>
|
||
<meta charset="utf-8" />
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="viewport" content="width=device-width, initial-scale=1" />
|
||
|
||
<title>Using SQLAlchemy — responder 9.2.0 documentation</title>
|
||
<link rel="stylesheet" type="text/css" href="_static/pygments.css?v=5ecbeea2" />
|
||
<link rel="stylesheet" type="text/css" href="_static/basic.css?v=b08954a9" />
|
||
<link rel="stylesheet" type="text/css" href="_static/alabaster.css?v=eb4102c5" />
|
||
<link rel="stylesheet" type="text/css" href="_static/copybutton.css?v=76b2166b" />
|
||
<link rel="stylesheet" type="text/css" href="_static/sphinx-design.min.css?v=95c83b7e" />
|
||
<link rel="stylesheet" type="text/css" href="_static/design-elements.e5416f61bae5d36adc6d722a2b6f8cff.css?v=452a8e97" />
|
||
<script src="_static/documentation_options.js?v=e8b940be"></script>
|
||
<script src="_static/doctools.js?v=fd6eb6e6"></script>
|
||
<script src="_static/sphinx_highlight.js?v=6ffebe34"></script>
|
||
<script src="_static/clipboard.min.js?v=a7894cd8"></script>
|
||
<script src="_static/copybutton.js?v=fd10adb8"></script>
|
||
<script>
|
||
</script>
|
||
<script src="_static/design-tabs.js?v=f930bc37"></script>
|
||
<script src="_static/design-elements.bbdccc18c4abea9397628f9fea3d48c2.js?v=03c7770e"></script>
|
||
<link rel="index" title="Index" href="genindex.html" />
|
||
<link rel="search" title="Search" href="search.html" />
|
||
<link rel="next" title="Authentication" href="tutorial-auth.html" />
|
||
<link rel="prev" title="Building a REST API" href="tutorial-rest.html" />
|
||
|
||
<link rel="stylesheet" href="_static/custom.css" type="text/css" />
|
||
|
||
|
||
|
||
|
||
|
||
</head><body>
|
||
|
||
|
||
<div class="document">
|
||
<div class="documentwrapper">
|
||
<div class="bodywrapper">
|
||
|
||
|
||
<div class="body" role="main">
|
||
|
||
<section id="using-sqlalchemy">
|
||
<h1>Using SQLAlchemy<a class="headerlink" href="#using-sqlalchemy" title="Link to this heading">¶</a></h1>
|
||
<p>Most real web applications need a database. This guide shows how to
|
||
integrate <a class="reference external" href="https://www.sqlalchemy.org/">SQLAlchemy</a> with Responder,
|
||
using async support and the lifespan pattern for connection management.</p>
|
||
<p>SQLAlchemy is the most popular Python database toolkit. It gives you an
|
||
ORM (Object-Relational Mapper) for working with databases using Python
|
||
classes instead of raw SQL, plus a powerful query builder for when you
|
||
need fine-grained control.</p>
|
||
<section id="installation">
|
||
<h2>Installation<a class="headerlink" href="#installation" title="Link to this heading">¶</a></h2>
|
||
<p>Install SQLAlchemy with async support and an async database driver.
|
||
We’ll use SQLite for simplicity, but the pattern works with PostgreSQL,
|
||
MySQL, and any other database SQLAlchemy supports:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ uv pip install 'sqlalchemy[asyncio]' aiosqlite
|
||
</pre></div>
|
||
</div>
|
||
</section>
|
||
<section id="define-your-models">
|
||
<h2>Define Your Models<a class="headerlink" href="#define-your-models" title="Link to this heading">¶</a></h2>
|
||
<p>SQLAlchemy models map Python classes to database tables. Each attribute
|
||
becomes a column:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># models.py</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">String</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.orm</span><span class="w"> </span><span class="kn">import</span> <span class="n">DeclarativeBase</span><span class="p">,</span> <span class="n">Mapped</span><span class="p">,</span> <span class="n">mapped_column</span>
|
||
|
||
<span class="k">class</span><span class="w"> </span><span class="nc">Base</span><span class="p">(</span><span class="n">DeclarativeBase</span><span class="p">):</span>
|
||
<span class="k">pass</span>
|
||
|
||
<span class="k">class</span><span class="w"> </span><span class="nc">Book</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
|
||
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s2">"books"</span>
|
||
|
||
<span class="nb">id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">autoincrement</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
|
||
<span class="n">title</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">str</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
|
||
<span class="n">author</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">str</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
|
||
<span class="n">year</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
|
||
<span class="n">isbn</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>This uses SQLAlchemy 2.0’s <code class="docutils literal notranslate"><span class="pre">Mapped</span></code> type annotations and
|
||
<code class="docutils literal notranslate"><span class="pre">mapped_column()</span></code>, which give you type checker support and cleaner
|
||
syntax than the older <code class="docutils literal notranslate"><span class="pre">Column()</span></code> style. Each model class corresponds
|
||
to a table, and each <code class="docutils literal notranslate"><span class="pre">mapped_column()</span></code> corresponds to a column.</p>
|
||
</section>
|
||
<section id="database-setup">
|
||
<h2>Database Setup<a class="headerlink" href="#database-setup" title="Link to this heading">¶</a></h2>
|
||
<p>Create the async engine and session factory. The <em>engine</em> manages
|
||
the connection pool. The <em>session</em> is your unit of work — you use it to
|
||
query and modify data within a transaction:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># database.py</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.ext.asyncio</span><span class="w"> </span><span class="kn">import</span> <span class="n">create_async_engine</span><span class="p">,</span> <span class="n">async_sessionmaker</span>
|
||
|
||
<span class="n">DATABASE_URL</span> <span class="o">=</span> <span class="s2">"sqlite+aiosqlite:///./books.db"</span>
|
||
|
||
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_async_engine</span><span class="p">(</span><span class="n">DATABASE_URL</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
|
||
<span class="n">async_session</span> <span class="o">=</span> <span class="n">async_sessionmaker</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="n">expire_on_commit</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>The <code class="docutils literal notranslate"><span class="pre">echo=True</span></code> flag prints all SQL queries to the console — very
|
||
helpful during development, but you’ll want to disable it in production.</p>
|
||
<p>The <code class="docutils literal notranslate"><span class="pre">expire_on_commit=False</span></code> flag keeps model attributes accessible
|
||
after a commit, which is convenient for returning created objects in
|
||
API responses.</p>
|
||
</section>
|
||
<section id="lifespan-for-startup-and-shutdown">
|
||
<h2>Lifespan for Startup and Shutdown<a class="headerlink" href="#lifespan-for-startup-and-shutdown" title="Link to this heading">¶</a></h2>
|
||
<p>Use Responder’s lifespan context manager to create the database tables
|
||
on startup and dispose of connections on shutdown:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># app.py</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">contextlib</span><span class="w"> </span><span class="kn">import</span> <span class="n">asynccontextmanager</span>
|
||
<span class="kn">import</span><span class="w"> </span><span class="nn">responder</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">database</span><span class="w"> </span><span class="kn">import</span> <span class="n">engine</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">models</span><span class="w"> </span><span class="kn">import</span> <span class="n">Base</span>
|
||
|
||
<span class="nd">@asynccontextmanager</span>
|
||
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">lifespan</span><span class="p">(</span><span class="n">app</span><span class="p">):</span>
|
||
<span class="c1"># Startup: create tables</span>
|
||
<span class="k">async</span> <span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
|
||
<span class="k">await</span> <span class="n">conn</span><span class="o">.</span><span class="n">run_sync</span><span class="p">(</span><span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">)</span>
|
||
<span class="k">yield</span>
|
||
<span class="c1"># Shutdown: close all connections</span>
|
||
<span class="k">await</span> <span class="n">engine</span><span class="o">.</span><span class="n">dispose</span><span class="p">()</span>
|
||
|
||
<span class="n">api</span> <span class="o">=</span> <span class="n">responder</span><span class="o">.</span><span class="n">API</span><span class="p">(</span><span class="n">lifespan</span><span class="o">=</span><span class="n">lifespan</span><span class="p">,</span> <span class="n">sessions</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>This is the proper way to manage database connections in an async
|
||
application. The lifespan context manager ensures that:</p>
|
||
<ol class="arabic simple">
|
||
<li><p>Tables are created before the first request</p></li>
|
||
<li><p>The connection pool is properly closed when the server shuts down</p></li>
|
||
<li><p>If table creation fails, the server won’t start</p></li>
|
||
</ol>
|
||
<div class="admonition note">
|
||
<p class="admonition-title">Note</p>
|
||
<p>We pass <code class="docutils literal notranslate"><span class="pre">sessions=False</span></code> because this is a stateless REST API with
|
||
no cookie sessions. Without it, Responder mints a random per-process
|
||
signing key and logs a startup warning. If your app <em>does</em> use
|
||
<code class="docutils literal notranslate"><span class="pre">req.session</span></code>, set a stable <code class="docutils literal notranslate"><span class="pre">secret_key=</span></code> (or the
|
||
<code class="docutils literal notranslate"><span class="pre">RESPONDER_SECRET_KEY</span></code> environment variable) instead — see the
|
||
<a class="reference internal" href="tour.html"><span class="doc">Feature Tour</span></a> for the full story.</p>
|
||
</div>
|
||
</section>
|
||
<section id="crud-endpoints">
|
||
<h2>CRUD Endpoints<a class="headerlink" href="#crud-endpoints" title="Link to this heading">¶</a></h2>
|
||
<p>First, define the Pydantic schemas Responder uses to validate the request
|
||
body and shape the response:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span><span class="w"> </span><span class="nn">pydantic</span><span class="w"> </span><span class="kn">import</span> <span class="n">BaseModel</span><span class="p">,</span> <span class="n">ConfigDict</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">select</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">responder</span><span class="w"> </span><span class="kn">import</span> <span class="n">abort</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">database</span><span class="w"> </span><span class="kn">import</span> <span class="n">async_session</span>
|
||
<span class="kn">from</span><span class="w"> </span><span class="nn">models</span><span class="w"> </span><span class="kn">import</span> <span class="n">Book</span>
|
||
|
||
<span class="k">class</span><span class="w"> </span><span class="nc">BookIn</span><span class="p">(</span><span class="n">BaseModel</span><span class="p">):</span>
|
||
<span class="n">title</span><span class="p">:</span> <span class="nb">str</span>
|
||
<span class="n">author</span><span class="p">:</span> <span class="nb">str</span>
|
||
<span class="n">year</span><span class="p">:</span> <span class="nb">int</span>
|
||
<span class="n">isbn</span><span class="p">:</span> <span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span> <span class="o">=</span> <span class="kc">None</span>
|
||
|
||
<span class="k">class</span><span class="w"> </span><span class="nc">BookOut</span><span class="p">(</span><span class="n">BaseModel</span><span class="p">):</span>
|
||
<span class="n">model_config</span> <span class="o">=</span> <span class="n">ConfigDict</span><span class="p">(</span><span class="n">from_attributes</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
|
||
|
||
<span class="nb">id</span><span class="p">:</span> <span class="nb">int</span>
|
||
<span class="n">title</span><span class="p">:</span> <span class="nb">str</span>
|
||
<span class="n">author</span><span class="p">:</span> <span class="nb">str</span>
|
||
<span class="n">year</span><span class="p">:</span> <span class="nb">int</span>
|
||
<span class="n">isbn</span><span class="p">:</span> <span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span> <span class="o">=</span> <span class="kc">None</span>
|
||
</pre></div>
|
||
</div>
|
||
<p><code class="docutils literal notranslate"><span class="pre">from_attributes=True</span></code> tells Pydantic to read values off SQLAlchemy
|
||
model attributes (not just dicts), so <code class="docutils literal notranslate"><span class="pre">BookOut.model_validate(book)</span></code>
|
||
turns a <code class="docutils literal notranslate"><span class="pre">Book</span></code> ORM object straight into a response model.</p>
|
||
<section id="a-session-per-request">
|
||
<h3>A Session Per Request<a class="headerlink" href="#a-session-per-request" title="Link to this heading">¶</a></h3>
|
||
<p>Rather than open <code class="docutils literal notranslate"><span class="pre">async</span> <span class="pre">with</span> <span class="pre">async_session()</span></code> by hand in every handler,
|
||
register the session as a <strong>dependency</strong>. Each request gets its own,
|
||
cleaned up automatically:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">dependency</span><span class="p">()</span>
|
||
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">session</span><span class="p">():</span>
|
||
<span class="k">async</span> <span class="k">with</span> <span class="n">async_session</span><span class="p">()</span> <span class="k">as</span> <span class="n">session</span><span class="p">:</span>
|
||
<span class="k">yield</span> <span class="n">session</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>Responder injects this into any handler that declares a <code class="docutils literal notranslate"><span class="pre">session</span></code>
|
||
parameter. The code after <code class="docutils literal notranslate"><span class="pre">yield</span></code> — here, the <code class="docutils literal notranslate"><span class="pre">async</span> <span class="pre">with</span></code> block
|
||
closing the session — runs as teardown once the response has been sent,
|
||
even if the handler raised. That’s one session per request, with
|
||
guaranteed cleanup and no boilerplate in the views. A request-scoped
|
||
session layered on the module-level engine is the canonical dependency;
|
||
see the <a class="reference internal" href="tour.html"><span class="doc">Feature Tour</span></a> for the full dependency-injection guide.</p>
|
||
</section>
|
||
<section id="the-handlers">
|
||
<h3>The Handlers<a class="headerlink" href="#the-handlers" title="Link to this heading">¶</a></h3>
|
||
<p>Each handler declares <code class="docutils literal notranslate"><span class="pre">session</span></code> to receive the injected session. On
|
||
write methods, a Pydantic-typed parameter (<code class="docutils literal notranslate"><span class="pre">book:</span> <span class="pre">BookIn</span></code>) is
|
||
auto-filled with the validated request body — an invalid body returns
|
||
<code class="docutils literal notranslate"><span class="pre">422</span></code> before the handler runs, so there’s no manual <code class="docutils literal notranslate"><span class="pre">await</span> <span class="pre">req.media()</span></code>.
|
||
A <code class="docutils literal notranslate"><span class="pre">-></span> <span class="pre">BookOut</span></code> or <code class="docutils literal notranslate"><span class="pre">-></span> <span class="pre">list[BookOut]</span></code> return annotation makes Responder
|
||
validate and serialize the returned value against that contract, and both
|
||
models flow into the generated <a class="reference internal" href="tour.html"><span class="doc">OpenAPI schema</span></a> automatically.</p>
|
||
<p><strong>List all books</strong>:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">"/books"</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">"GET"</span><span class="p">])</span>
|
||
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">list_books</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-></span> <span class="nb">list</span><span class="p">[</span><span class="n">BookOut</span><span class="p">]:</span>
|
||
<span class="n">result</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">(</span><span class="n">Book</span><span class="p">))</span>
|
||
<span class="n">books</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">scalars</span><span class="p">()</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
|
||
<span class="k">return</span> <span class="p">[</span><span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">for</span> <span class="n">b</span> <span class="ow">in</span> <span class="n">books</span><span class="p">]</span>
|
||
</pre></div>
|
||
</div>
|
||
<p><strong>Create a book</strong>:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span>
|
||
<span class="s2">"/books"</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">"POST"</span><span class="p">],</span> <span class="n">check_existing</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="n">status_code</span><span class="o">=</span><span class="mi">201</span>
|
||
<span class="p">)</span>
|
||
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">create_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book</span><span class="p">:</span> <span class="n">BookIn</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-></span> <span class="n">BookOut</span><span class="p">:</span>
|
||
<span class="n">new</span> <span class="o">=</span> <span class="n">Book</span><span class="p">(</span><span class="o">**</span><span class="n">book</span><span class="o">.</span><span class="n">model_dump</span><span class="p">())</span>
|
||
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">new</span><span class="p">)</span>
|
||
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
|
||
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">new</span><span class="p">)</span>
|
||
<span class="k">return</span> <span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">new</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p><strong>Get a single book</strong>:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">"/books/{book_id:int}"</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">"GET"</span><span class="p">])</span>
|
||
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">get_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book_id</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-></span> <span class="n">BookOut</span><span class="p">:</span>
|
||
<span class="n">book</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_id</span><span class="p">)</span>
|
||
<span class="k">if</span> <span class="n">book</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
|
||
<span class="n">abort</span><span class="p">(</span><span class="mi">404</span><span class="p">,</span> <span class="n">detail</span><span class="o">=</span><span class="s2">"Book not found"</span><span class="p">)</span>
|
||
<span class="k">return</span> <span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">book</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p><strong>Update a book</strong>:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">"/books/{book_id:int}"</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">"PUT"</span><span class="p">],</span> <span class="n">check_existing</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
|
||
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">update_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book_id</span><span class="p">,</span> <span class="n">book</span><span class="p">:</span> <span class="n">BookIn</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-></span> <span class="n">BookOut</span><span class="p">:</span>
|
||
<span class="n">existing</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_id</span><span class="p">)</span>
|
||
<span class="k">if</span> <span class="n">existing</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
|
||
<span class="n">abort</span><span class="p">(</span><span class="mi">404</span><span class="p">,</span> <span class="n">detail</span><span class="o">=</span><span class="s2">"Book not found"</span><span class="p">)</span>
|
||
|
||
<span class="k">for</span> <span class="n">key</span><span class="p">,</span> <span class="n">value</span> <span class="ow">in</span> <span class="n">book</span><span class="o">.</span><span class="n">model_dump</span><span class="p">()</span><span class="o">.</span><span class="n">items</span><span class="p">():</span>
|
||
<span class="nb">setattr</span><span class="p">(</span><span class="n">existing</span><span class="p">,</span> <span class="n">key</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span>
|
||
|
||
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
|
||
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">existing</span><span class="p">)</span>
|
||
<span class="k">return</span> <span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">existing</span><span class="p">)</span>
|
||
</pre></div>
|
||
</div>
|
||
<p><strong>Delete a book</strong>:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">"/books/{book_id:int}"</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">"DELETE"</span><span class="p">],</span> <span class="n">check_existing</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
|
||
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">delete_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book_id</span><span class="p">,</span> <span class="n">session</span><span class="p">):</span>
|
||
<span class="n">book</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_id</span><span class="p">)</span>
|
||
<span class="k">if</span> <span class="n">book</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
|
||
<span class="n">abort</span><span class="p">(</span><span class="mi">404</span><span class="p">,</span> <span class="n">detail</span><span class="o">=</span><span class="s2">"Book not found"</span><span class="p">)</span>
|
||
|
||
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">book</span><span class="p">)</span>
|
||
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
|
||
<span class="n">resp</span><span class="o">.</span><span class="n">no_content</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>A couple of things worth noting. <code class="docutils literal notranslate"><span class="pre">abort(404,</span> <span class="pre">detail="Book</span> <span class="pre">not</span> <span class="pre">found")</span></code>
|
||
raises a rendered, content-negotiated HTTP error and halts the handler
|
||
immediately — no need to set <code class="docutils literal notranslate"><span class="pre">resp.status_code</span></code> and <code class="docutils literal notranslate"><span class="pre">return</span></code> by hand.
|
||
And assigning a model (or a list of them) to <code class="docutils literal notranslate"><span class="pre">resp.media</span></code> just works:
|
||
Responder serializes Pydantic models natively, so the trailing
|
||
<code class="docutils literal notranslate"><span class="pre">.model_dump()</span></code> is no longer needed.</p>
|
||
<div class="admonition note">
|
||
<p class="admonition-title">Note</p>
|
||
<p>The <code class="docutils literal notranslate"><span class="pre">-></span> <span class="pre">BookOut</span></code> return validation only fires when <code class="docutils literal notranslate"><span class="pre">resp.media</span></code> is
|
||
a dict or a Pydantic model. A raw SQLAlchemy ORM object assigned to
|
||
<code class="docutils literal notranslate"><span class="pre">resp.media</span></code> is <strong>not</strong> auto-validated — that’s why every handler
|
||
wraps the result with <code class="docutils literal notranslate"><span class="pre">BookOut.model_validate(book)</span></code> before
|
||
assigning it to <code class="docutils literal notranslate"><span class="pre">resp.media</span></code>.</p>
|
||
</div>
|
||
</section>
|
||
</section>
|
||
<section id="run-it">
|
||
<h2>Run It<a class="headerlink" href="#run-it" title="Link to this heading">¶</a></h2>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">if</span> <span class="vm">__name__</span> <span class="o">==</span> <span class="s2">"__main__"</span><span class="p">:</span>
|
||
<span class="n">api</span><span class="o">.</span><span class="n">run</span><span class="p">()</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>Start the server and you’ll see SQLAlchemy’s SQL echo in the console.
|
||
The SQLite database file <code class="docutils literal notranslate"><span class="pre">books.db</span></code> is created automatically on first
|
||
startup.</p>
|
||
</section>
|
||
<section id="using-postgresql">
|
||
<h2>Using PostgreSQL<a class="headerlink" href="#using-postgresql" title="Link to this heading">¶</a></h2>
|
||
<p>To switch to PostgreSQL, just change the connection URL and driver:</p>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ uv pip install asyncpg
|
||
</pre></div>
|
||
</div>
|
||
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DATABASE_URL</span> <span class="o">=</span> <span class="s2">"postgresql+asyncpg://user:pass@localhost/mydb"</span>
|
||
</pre></div>
|
||
</div>
|
||
<p>Everything else stays the same. SQLAlchemy abstracts the database
|
||
differences so your application code doesn’t need to change.</p>
|
||
</section>
|
||
<section id="tips">
|
||
<h2>Tips<a class="headerlink" href="#tips" title="Link to this heading">¶</a></h2>
|
||
<ul class="simple">
|
||
<li><p>Give each request its own session and transaction — never share one
|
||
across requests. The <code class="docutils literal notranslate"><span class="pre">session</span></code> dependency above does this for you;
|
||
declaring <code class="docutils literal notranslate"><span class="pre">session</span></code> in a handler is all it takes.</p></li>
|
||
<li><p>For complex queries, use SQLAlchemy’s <code class="docutils literal notranslate"><span class="pre">select()</span></code> with <code class="docutils literal notranslate"><span class="pre">.where()</span></code>,
|
||
<code class="docutils literal notranslate"><span class="pre">.order_by()</span></code>, <code class="docutils literal notranslate"><span class="pre">.limit()</span></code>, and <code class="docutils literal notranslate"><span class="pre">.offset()</span></code> — it composes
|
||
naturally.</p></li>
|
||
<li><p>In production, use connection pooling (SQLAlchemy does this by
|
||
default) and set pool size limits appropriate for your database.</p></li>
|
||
<li><p>Consider <a class="reference external" href="https://alembic.sqlalchemy.org/">Alembic</a> for database
|
||
migrations — it tracks schema changes over time so you can evolve
|
||
your database without losing data.</p></li>
|
||
</ul>
|
||
</section>
|
||
</section>
|
||
|
||
|
||
</div>
|
||
|
||
</div>
|
||
</div>
|
||
<div class="sphinxsidebar" role="navigation" aria-label="Main">
|
||
<div class="sphinxsidebarwrapper"><p class="logo">
|
||
<a href="index.html">
|
||
<img class="logo" src="_static/responder.png" />
|
||
</a>
|
||
</p>
|
||
<p>
|
||
<strong>Responder</strong> — a familiar HTTP service framework for Python.
|
||
<br />
|
||
<small>v9.2.0</small>
|
||
</p>
|
||
<h3>Useful Links</h3>
|
||
<ul>
|
||
<li><a href="https://github.com/kennethreitz/responder">Responder @ GitHub</a></li>
|
||
<li><a href="https://pypi.org/project/responder/">Responder @ PyPI</a></li>
|
||
<li><a href="https://github.com/kennethreitz/responder/issues">Issue Tracker</a></li>
|
||
</ul><h3>Navigation</h3>
|
||
<p class="caption" role="heading"><span class="caption-text">User Guide</span></p>
|
||
<ul>
|
||
<li class="toctree-l1"><a class="reference internal" href="quickstart.html">Quick Start</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="tour.html">Feature Tour</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="examples.html">Examples</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="routers.html">Composing Apps with Routers</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="guide-config.html">Configuration</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="clientgen.html">Generated Clients</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="deployment.html">Deployment</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="testing.html">Testing</a></li>
|
||
</ul>
|
||
<p class="caption" role="heading"><span class="caption-text">Tutorials</span></p>
|
||
<ul class="current">
|
||
<li class="toctree-l1"><a class="reference internal" href="tutorial-rest.html">Building a REST API</a></li>
|
||
<li class="toctree-l1 current"><a class="current reference internal" href="#">Using SQLAlchemy</a><ul>
|
||
<li class="toctree-l2"><a class="reference internal" href="#installation">Installation</a></li>
|
||
<li class="toctree-l2"><a class="reference internal" href="#define-your-models">Define Your Models</a></li>
|
||
<li class="toctree-l2"><a class="reference internal" href="#database-setup">Database Setup</a></li>
|
||
<li class="toctree-l2"><a class="reference internal" href="#lifespan-for-startup-and-shutdown">Lifespan for Startup and Shutdown</a></li>
|
||
<li class="toctree-l2"><a class="reference internal" href="#crud-endpoints">CRUD Endpoints</a></li>
|
||
<li class="toctree-l2"><a class="reference internal" href="#run-it">Run It</a></li>
|
||
<li class="toctree-l2"><a class="reference internal" href="#using-postgresql">Using PostgreSQL</a></li>
|
||
<li class="toctree-l2"><a class="reference internal" href="#tips">Tips</a></li>
|
||
</ul>
|
||
</li>
|
||
<li class="toctree-l1"><a class="reference internal" href="tutorial-auth.html">Authentication</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="tutorial-websockets.html">WebSocket Tutorial</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="tutorial-middleware.html">Writing Middleware</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="tutorial-flask.html">Migrating from Flask</a></li>
|
||
</ul>
|
||
<p class="caption" role="heading"><span class="caption-text">Reference</span></p>
|
||
<ul>
|
||
<li class="toctree-l1"><a class="reference internal" href="api.html">API Reference</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="cli.html">Command Line Interface</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="runtime-contracts.html">Runtime Contracts</a></li>
|
||
</ul>
|
||
<p class="caption" role="heading"><span class="caption-text">Project</span></p>
|
||
<ul>
|
||
<li class="toctree-l1"><a class="reference internal" href="changes.html">Changelog</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="migration-v9.html">Migrating to v9</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="migration-v8.html">Migrating to v8</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="migration-v7.html">Migrating to v7</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="migration-v6.html">Migrating to v6</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="migration-v5.html">Migrating to v5</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="sandbox.html">Sandbox</a></li>
|
||
<li class="toctree-l1"><a class="reference internal" href="backlog.html">Backlog</a></li>
|
||
</ul>
|
||
|
||
<div class="relations">
|
||
<h3>Related Topics</h3>
|
||
<ul>
|
||
<li><a href="index.html">Documentation overview</a><ul>
|
||
<li>Previous: <a href="tutorial-rest.html" title="previous chapter">Building a REST API</a></li>
|
||
<li>Next: <a href="tutorial-auth.html" title="next chapter">Authentication</a></li>
|
||
</ul></li>
|
||
</ul>
|
||
</div>
|
||
<search id="searchbox" style="display: none" role="search">
|
||
<h3 id="searchlabel">Quick search</h3>
|
||
<div class="searchformwrapper">
|
||
<form class="search" action="search.html" method="get">
|
||
<input type="text" name="q" aria-labelledby="searchlabel" autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false"/>
|
||
<input type="submit" value="Go" />
|
||
</form>
|
||
</div>
|
||
</search>
|
||
<script>document.getElementById('searchbox').style.display = "block"</script>
|
||
</div>
|
||
</div>
|
||
<div class="clearer"></div>
|
||
</div>
|
||
<div class="footer">
|
||
©2018-2026, Kenneth Reitz.
|
||
|
||
|
|
||
<a href="_sources/tutorial-sqlalchemy.rst.txt"
|
||
rel="nofollow">Page source</a>
|
||
</div>
|
||
|
||
|
||
<a href="https://github.com/kennethreitz/responder" class="github">
|
||
<img src="_static/forkme_right_darkblue.png" alt="Fork me on GitHub" class="github"/>
|
||
</a>
|
||
|
||
|
||
|
||
</body>
|
||
</html> |