<p dir="auto">Hi,</p>
<p dir="auto">I have come across some unexpected behaviour when using dataclasses mapped to SQLAlchemy using classical mapping.</p>
<p dir="auto">Here's a snippet with the essence of the issue:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from __future__ import annotations

from dataclasses import dataclass, field

from sqlalchemy import Column, Table, String, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import mapper, sessionmaker

Base = declarative_base()
metadata = Base.metadata

table = Table(
            &quot;table&quot;,
            metadata,
            #
            Column(&quot;id&quot;, String, primary_key=True),
            Column(&quot;active&quot;, Boolean),
            )


@dataclass
class C:
    id: str
    active: bool = True


@dataclass
class C1:
    id: str
    active: bool


@dataclass
class C2:
    id: str
    active: bool = field(default_factory=lambda: True)


mapper(C, table)
mapper(C1, table)
mapper(C2, table)

Session = sessionmaker()
session = Session()

query = session.query(C)
print(query.filter(C.active == True))
print()

# Result (WRONG)
# SELECT &quot;table&quot;.id AS table_id
# FROM &quot;table&quot;
# WHERE true

query = session.query(C1)
print(query.filter(C1.active == True))
print()

# Result: OK
# SELECT &quot;table&quot;.id AS table_id, &quot;table&quot;.active AS table_active
# FROM &quot;table&quot;
# WHERE &quot;table&quot;.active = true

query = session.query(C2)
print(query.filter(C2.active == True))
print()

# Result: OK
# SELECT &quot;table&quot;.id AS table_id, &quot;table&quot;.active AS table_active
# FROM &quot;table&quot;
# WHERE &quot;table&quot;.active = true"><pre class="notranslate"><span class="pl-k">from</span> __future__ <span class="pl-k">import</span> <span class="pl-s1">annotations</span>

<span class="pl-k">from</span> <span class="pl-s1">dataclasses</span> <span class="pl-k">import</span> <span class="pl-s1">dataclass</span>, <span class="pl-s1">field</span>

<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">Table</span>, <span class="pl-v">String</span>, <span class="pl-v">Boolean</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">mapper</span>, <span class="pl-s1">sessionmaker</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()
<span class="pl-s1">metadata</span> <span class="pl-c1">=</span> <span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>

<span class="pl-s1">table</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(
            <span class="pl-s">"table"</span>,
            <span class="pl-s1">metadata</span>,
            <span class="pl-c">#</span>
            <span class="pl-v">Column</span>(<span class="pl-s">"id"</span>, <span class="pl-v">String</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>),
            <span class="pl-v">Column</span>(<span class="pl-s">"active"</span>, <span class="pl-v">Boolean</span>),
            )


<span class="pl-en">@<span class="pl-s1">dataclass</span></span>
<span class="pl-k">class</span> <span class="pl-v">C</span>:
    <span class="pl-s1">id</span>: <span class="pl-s1">str</span>
    <span class="pl-s1">active</span>: <span class="pl-s1">bool</span> <span class="pl-c1">=</span> <span class="pl-c1">True</span>


<span class="pl-en">@<span class="pl-s1">dataclass</span></span>
<span class="pl-k">class</span> <span class="pl-v">C1</span>:
    <span class="pl-s1">id</span>: <span class="pl-s1">str</span>
    <span class="pl-s1">active</span>: <span class="pl-s1">bool</span>


<span class="pl-en">@<span class="pl-s1">dataclass</span></span>
<span class="pl-k">class</span> <span class="pl-v">C2</span>:
    <span class="pl-s1">id</span>: <span class="pl-s1">str</span>
    <span class="pl-s1">active</span>: <span class="pl-s1">bool</span> <span class="pl-c1">=</span> <span class="pl-en">field</span>(<span class="pl-s1">default_factory</span><span class="pl-c1">=</span><span class="pl-k">lambda</span>: <span class="pl-c1">True</span>)


<span class="pl-en">mapper</span>(<span class="pl-v">C</span>, <span class="pl-s1">table</span>)
<span class="pl-en">mapper</span>(<span class="pl-v">C1</span>, <span class="pl-s1">table</span>)
<span class="pl-en">mapper</span>(<span class="pl-v">C2</span>, <span class="pl-s1">table</span>)

<span class="pl-v">Session</span> <span class="pl-c1">=</span> <span class="pl-en">sessionmaker</span>()
<span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>()

<span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">C</span>)
<span class="pl-en">print</span>(<span class="pl-s1">query</span>.<span class="pl-en">filter</span>(<span class="pl-v">C</span>.<span class="pl-s1">active</span> <span class="pl-c1">==</span> <span class="pl-c1">True</span>))
<span class="pl-en">print</span>()

<span class="pl-c"># Result (WRONG)</span>
<span class="pl-c"># SELECT "table".id AS table_id</span>
<span class="pl-c"># FROM "table"</span>
<span class="pl-c"># WHERE true</span>

<span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">C1</span>)
<span class="pl-en">print</span>(<span class="pl-s1">query</span>.<span class="pl-en">filter</span>(<span class="pl-v">C1</span>.<span class="pl-s1">active</span> <span class="pl-c1">==</span> <span class="pl-c1">True</span>))
<span class="pl-en">print</span>()

<span class="pl-c"># Result: OK</span>
<span class="pl-c"># SELECT "table".id AS table_id, "table".active AS table_active</span>
<span class="pl-c"># FROM "table"</span>
<span class="pl-c"># WHERE "table".active = true</span>

<span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">C2</span>)
<span class="pl-en">print</span>(<span class="pl-s1">query</span>.<span class="pl-en">filter</span>(<span class="pl-v">C2</span>.<span class="pl-s1">active</span> <span class="pl-c1">==</span> <span class="pl-c1">True</span>))
<span class="pl-en">print</span>()

<span class="pl-c"># Result: OK</span>
<span class="pl-c"># SELECT "table".id AS table_id, "table".active AS table_active</span>
<span class="pl-c"># FROM "table"</span>
<span class="pl-c"># WHERE "table".active = true</span></pre></div>
<p dir="auto">So, yes, I have found a workaround (using <code class="notranslate">= field(default_factory=lambda: something)</code> as an initializer instead of just <code class="notranslate">= something</code>), but it doesn't look as good.</p>
<p dir="auto">Is there some general advice regarding the interaction of dataclasses and SQLAlchemy ORM? (I have also tried with attrs, and got the same issue).</p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">Something I'd love to see is having a one-to-many relation() (non-lazy at least) is to have a dictionary returned instead of a list or scalar, the key being a specified column from the joined table, breaking up the results on lists on the given keys.</p>