Python Enum usage patterns and SQLAlchemy integration

<p>I'm using Python Enum classes for status fields and vote types in my application. I need them to work correctly with SQLAlchemy (stored as strings in PostgreSQL, not as database enum types), with Pydantic validation, and with proper JSON serialization.</p>

<p>Use str-based Enums for compatibility with SQLAlchemy and Pydantic:</p> <div class="highlight"><pre><span></span><code><span class="kn">import</span><span class="w"> </span><span class="nn">enum</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">mapped_column</span><span class="p">,</span> <span class="n">Mapped</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="c1"># str enum — value IS the string, no native DB enum type needed</span> <span class="k">class</span><span class="w"> </span><span class="nc">TraceStatus</span><span class="p">(</span><span class="nb">str</span><span class="p">,</span> <span class="n">enum</span><span class="o">.</span><span class="n">Enum</span><span class="p">):</span> <span class="n">pending</span> <span class="o">=</span> <span class="s1">'pending'</span> <span class="n">validated</span> <span class="o">=</span> <span class="s1">'validated'</span> <span class="k">class</span><span class="w"> </span><span class="nc">VoteType</span><span class="p">(</span><span class="nb">str</span><span class="p">,</span> <span class="n">enum</span><span class="o">.</span><span class="n">Enum</span><span class="p">):</span> <span class="n">confirmed</span> <span class="o">=</span> <span class="s1">'confirmed'</span> <span class="n">disputed</span> <span class="o">=</span> <span class="s1">'disputed'</span> <span class="c1"># SQLAlchemy — store as String, compare as string:</span> <span class="k">class</span><span class="w"> </span><span class="nc">Trace</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">status</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="mi">20</span><span class="p">),</span> <span class="n">default</span><span class="o">=</span><span class="n">TraceStatus</span><span class="o">.</span><span class="n">pending</span><span class="p">)</span> <span class="c1"># Works in queries:</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Trace</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">status</span> <span class="o">==</span> <span class="n">TraceStatus</span><span class="o">.</span><span class="n">validated</span><span class="p">)</span> <span class="c1"># Also works:</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span><span class="n">Trace</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">status</span> <span class="o">==</span> <span class="s1">'validated'</span><span class="p">)</span> <span class="c1"># Pydantic — accepts both 'validated' string and TraceStatus.validated:</span> <span class="k">class</span><span class="w"> </span><span class="nc">TraceResponse</span><span class="p">(</span><span class="n">BaseModel</span><span class="p">):</span> <span class="n">status</span><span class="p">:</span> <span class="n">TraceStatus</span> <span class="c1"># Validates and serializes as string</span> <span class="c1"># JSON serialization — str enum serializes as its value:</span> <span class="kn">import</span><span class="w"> </span><span class="nn">json</span> <span class="n">status</span> <span class="o">=</span> <span class="n">TraceStatus</span><span class="o">.</span><span class="n">validated</span> <span class="n">json</span><span class="o">.</span><span class="n">dumps</span><span class="p">({</span><span class="s1">'status'</span><span class="p">:</span> <span class="n">status</span><span class="p">})</span> <span class="c1"># -&gt; '{"status": "validated"}'</span> <span class="c1"># Transition validation:</span> <span class="k">def</span><span class="w"> </span><span class="nf">validate_status_transition</span><span class="p">(</span><span class="n">current</span><span class="p">:</span> <span class="n">TraceStatus</span><span class="p">,</span> <span class="n">new</span><span class="p">:</span> <span class="n">TraceStatus</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">bool</span><span class="p">:</span> <span class="n">allowed</span> <span class="o">=</span> <span class="p">{</span> <span class="n">TraceStatus</span><span class="o">.</span><span class="n">pending</span><span class="p">:</span> <span class="p">{</span><span class="n">TraceStatus</span><span class="o">.</span><span class="n">validated</span><span class="p">},</span> <span class="p">}</span> <span class="k">return</span> <span class="n">new</span> <span class="ow">in</span> <span class="n">allowed</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">current</span><span class="p">,</span> <span class="nb">set</span><span class="p">())</span> </code></pre></div> <p>Key points: - <code>str</code> + <code>enum.Enum</code> means instances ARE strings — no <code>.value</code> access needed - Store as <code>String</code> column, not <code>Enum</code> — avoids PostgreSQL ENUM type migration complexity - Pydantic coerces string input to enum value automatically - <code>json.dumps</code> on <code>str</code> enums works without a custom encoder</p>