<p dir="auto">Currently, let's assume I write a query like the following</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="q = session.query(Customer).join(Sales, Customer.id==Sales.customer_id).filter(Customer.id==1).all()"><pre class="notranslate"><span class="pl-s1">q</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Customer</span>).<span class="pl-en">join</span>(<span class="pl-v">Sales</span>, <span class="pl-v">Customer</span>.<span class="pl-s1">id</span><span class="pl-c1">==</span><span class="pl-v">Sales</span>.<span class="pl-s1">customer_id</span>).<span class="pl-en">filter</span>(<span class="pl-v">Customer</span>.<span class="pl-s1">id</span><span class="pl-c1">==</span><span class="pl-c1">1</span>).<span class="pl-en">all</span>()</pre></div>
<p dir="auto">under the assumption, e.g., that Customer 1 has 5 sales.<br>
Now, <code class="notranslate">q.count()</code> would return 5, as also SQL would do if I run the corresponding SQL query.</p>
<p dir="auto">However, <code class="notranslate">q.all()</code> will only return a list of 1 Customer, as it deduplicates results - this is done here:<br>
</p><div class="Box Box--condensed my-2">
  <div class="Box-header f6">
    <p class="mb-0 text-bold">
      <a href="https://github.com/sqlalchemy/sqlalchemy/blob/a0d1030096af8f6cdd2e2fb7668224ade2d87c8f/lib/sqlalchemy/orm/loading.py#L88-L89">sqlalchemy/lib/sqlalchemy/orm/loading.py</a>
    </p>
    <p class="mb-0 color-fg-muted">
        Lines 88 to 89
      in
      <a data-pjax="true" class="commit-tease-sha" href="/sqlalchemy/sqlalchemy/commit/a0d1030096af8f6cdd2e2fb7668224ade2d87c8f">a0d1030</a>
    </p>
  </div>
  <div itemprop="text" class="Box-body p-0 blob-wrapper blob-wrapper-embedded data">
    <table class="highlight tab-size mb-0 js-file-line-container" data-tab-size="8" data-paste-markdown-skip="">

        <tbody><tr class="border-0">
          <td id="L88" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="88"></td>
          <td id="LC88" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line"> <span class="pl-k">if</span> <span class="pl-s1">filtered</span>: </td>
        </tr>

        <tr class="border-0">
          <td id="L89" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="89"></td>
          <td id="LC89" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line">     <span class="pl-s1">rows</span> <span class="pl-c1">=</span> <span class="pl-s1">util</span>.<span class="pl-en">unique_list</span>(<span class="pl-s1">rows</span>, <span class="pl-s1">filter_fn</span>) </td>
        </tr>
    </tbody></table>
  </div>
</div>
<br>
where <code class="notranslate">filtered is defined a few lines above from </code>query._has_mapper_entities`<br>
<div class="Box Box--condensed my-2">
  <div class="Box-header f6">
    <p class="mb-0 text-bold">
      <a href="https://github.com/sqlalchemy/sqlalchemy/blob/a0d1030096af8f6cdd2e2fb7668224ade2d87c8f/lib/sqlalchemy/orm/loading.py#L38">sqlalchemy/lib/sqlalchemy/orm/loading.py</a>
    </p>
    <p class="mb-0 color-fg-muted">
         Line 38
      in
      <a data-pjax="true" class="commit-tease-sha" href="/sqlalchemy/sqlalchemy/commit/a0d1030096af8f6cdd2e2fb7668224ade2d87c8f">a0d1030</a>
    </p>
  </div>
  <div itemprop="text" class="Box-body p-0 blob-wrapper blob-wrapper-embedded data">
    <table class="highlight tab-size mb-0 js-file-line-container" data-tab-size="8" data-paste-markdown-skip="">

        <tbody><tr class="border-0">
          <td id="L38" class="blob-num border-0 px-3 py-0 color-bg-default" data-line-number="38"></td>
          <td id="LC38" class="blob-code border-0 px-3 py-0 color-bg-default blob-code-inner js-file-line"> <span class="pl-s1">filtered</span> <span class="pl-c1">=</span> <span class="pl-s1">query</span>.<span class="pl-s1">_has_mapper_entities</span> </td>
        </tr>
    </tbody></table>
  </div>
</div>
<p></p>
<p dir="auto">The suggested solution, see e.g.<br>
<a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384640716" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/4143" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/4143/hovercard?comment_id=441939735&amp;comment_type=issue_comment" href="https://github.com/sqlalchemy/sqlalchemy/issues/4143#issuecomment-441939735">#4143 (comment)</a>, is apparently to run instead</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="q = session.query(Customer.id).join(Sales, Customer.id==Sales.customer_id).filter(Customer.id==1).all()"><pre class="notranslate"><span class="pl-s1">q</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Customer</span>.<span class="pl-s1">id</span>).<span class="pl-en">join</span>(<span class="pl-v">Sales</span>, <span class="pl-v">Customer</span>.<span class="pl-s1">id</span><span class="pl-c1">==</span><span class="pl-v">Sales</span>.<span class="pl-s1">customer_id</span>).<span class="pl-en">filter</span>(<span class="pl-v">Customer</span>.<span class="pl-s1">id</span><span class="pl-c1">==</span><span class="pl-c1">1</span>).<span class="pl-en">all</span>()</pre></div>
<p dir="auto">that indeed would return <code class="notranslate">[1,1,1,1,1]</code> (as I would expect from a SQL point of view).</p>
<p dir="auto">I imagine this is to make life easier for some users, but actually we need (<a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="326843203" data-permission-text="Title is private" data-url="https://github.com/aiidateam/aiida-core/issues/1600" data-hovercard-type="issue" data-hovercard-url="/aiidateam/aiida-core/issues/1600/hovercard" href="https://github.com/aiidateam/aiida-core/issues/1600">aiidateam/aiida-core#1600</a>) a way to get the SQL behaviour without deduplication, i.e. a list of 5 Customer entities (we could hack the query and do <code class="notranslate">q._has_mapper_entities = False</code>, but this clearly looks like the wrong thing to do)</p>
<p dir="auto">Is there a proper way to get the 5 results (i.e. not do a 'distinct') already?<br>
Or would it be possible to add an official, documented method on the query that we can call to internally set <code class="notranslate">q._has_mapper_entities</code> to false? So we can rely on it and not on internal functionality of sqlalchemy that might change in the future.</p>
<p dir="auto"><strong>Describe your question</strong><br>
I'm having table A with foreign key of table B. Now When I'm adding a New object to table A with following code.<br>
it gives me the following error describe below.</p>
<p dir="auto"><strong>Example</strong><br>
<a href="https://stackoverflow.com/questions/33083772/sqlalchemy-attributeerror-str-object-has-no-attribute-sa-instance-state" rel="nofollow">Proof 1 see 2nd answer it works fine there</a></p>
<p dir="auto"><strong>Complete stack trace</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="problem occured on line session.commit()
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint &quot;ratings_rating_key&quot;
DETAIL:  Key (rating)=(8.8) already exists.

[SQL: INSERT INTO ratings (rating) VALUES (%(rating)s) RETURNING ratings.id]
[parameters: {'rating': 8.8}]"><pre class="notranslate"><code class="notranslate">problem occured on line session.commit()
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ratings_rating_key"
DETAIL:  Key (rating)=(8.8) already exists.

[SQL: INSERT INTO ratings (rating) VALUES (%(rating)s) RETURNING ratings.id]
[parameters: {'rating': 8.8}]
</code></pre></div>
<p dir="auto"><strong>Versions</strong></p>
<ul dir="auto">
<li>OS: Windows10</li>
<li>Python: 3.8.3</li>
<li>SQLAlchemy: 1.3.22</li>
<li>Database: postgres</li>
<li>DBAPI:</li>
</ul>
<p dir="auto"><strong>Additional context</strong><br>
Code</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="session = self.Session()
        performer = Performer(
                    name = item['name'],
                    aliases = item['aliases'],
                    gender = item['gender'],
                    description = item['description'],
                    profile_pic = item['profile_pic'],
                    date_of_birth = item['date_of_birth'], # get age from here
                    ethnicity = item['ethnicity'],
                    birth_place = item['birth_place'],
                    height = item['height'],
                    
        )

        rating = session.query(Rating).filter_by(rating=item['rating']).first()
        rating_exists = rating is not None

        if rating_exists:
            performer.rating = rating
        else:
            performer.rating = Rating(rating=item['rating'])

        performer_exists = session.query(Performer).filter_by(name=item['name']).first() is not None

        if performer_exists:
            logging.info(f'Item {performer} is in db')
            return item
        else:
            try:
                session.add(performer)
                session.commit()
                logging.info(f'Item {performer} stored in db')
            except:
                logging.info(f'Failed to add {performer} to db')
                session.rollback()
                raise
            finally:
                session.close()"><pre class="notranslate"><span class="pl-s1">session</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-v">Session</span>()
        <span class="pl-s1">performer</span> <span class="pl-c1">=</span> <span class="pl-v">Performer</span>(
                    <span class="pl-s1">name</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'name'</span>],
                    <span class="pl-s1">aliases</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'aliases'</span>],
                    <span class="pl-s1">gender</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'gender'</span>],
                    <span class="pl-s1">description</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'description'</span>],
                    <span class="pl-s1">profile_pic</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'profile_pic'</span>],
                    <span class="pl-s1">date_of_birth</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'date_of_birth'</span>], <span class="pl-c"># get age from here</span>
                    <span class="pl-s1">ethnicity</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'ethnicity'</span>],
                    <span class="pl-s1">birth_place</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'birth_place'</span>],
                    <span class="pl-s1">height</span> <span class="pl-c1">=</span> <span class="pl-s1">item</span>[<span class="pl-s">'height'</span>],
                    
        )

        <span class="pl-s1">rating</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Rating</span>).<span class="pl-en">filter_by</span>(<span class="pl-s1">rating</span><span class="pl-c1">=</span><span class="pl-s1">item</span>[<span class="pl-s">'rating'</span>]).<span class="pl-en">first</span>()
        <span class="pl-s1">rating_exists</span> <span class="pl-c1">=</span> <span class="pl-s1">rating</span> <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-c1">None</span>

        <span class="pl-k">if</span> <span class="pl-s1">rating_exists</span>:
            <span class="pl-s1">performer</span>.<span class="pl-s1">rating</span> <span class="pl-c1">=</span> <span class="pl-s1">rating</span>
        <span class="pl-k">else</span>:
            <span class="pl-s1">performer</span>.<span class="pl-s1">rating</span> <span class="pl-c1">=</span> <span class="pl-v">Rating</span>(<span class="pl-s1">rating</span><span class="pl-c1">=</span><span class="pl-s1">item</span>[<span class="pl-s">'rating'</span>])

        <span class="pl-s1">performer_exists</span> <span class="pl-c1">=</span> <span class="pl-s1">session</span>.<span class="pl-en">query</span>(<span class="pl-v">Performer</span>).<span class="pl-en">filter_by</span>(<span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s1">item</span>[<span class="pl-s">'name'</span>]).<span class="pl-en">first</span>() <span class="pl-c1">is</span> <span class="pl-c1">not</span> <span class="pl-c1">None</span>

        <span class="pl-k">if</span> <span class="pl-s1">performer_exists</span>:
            <span class="pl-s1">logging</span>.<span class="pl-en">info</span>(<span class="pl-s">f'Item <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">performer</span><span class="pl-kos">}</span></span> is in db'</span>)
            <span class="pl-k">return</span> <span class="pl-s1">item</span>
        <span class="pl-k">else</span>:
            <span class="pl-k">try</span>:
                <span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">performer</span>)
                <span class="pl-s1">session</span>.<span class="pl-en">commit</span>()
                <span class="pl-s1">logging</span>.<span class="pl-en">info</span>(<span class="pl-s">f'Item <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">performer</span><span class="pl-kos">}</span></span> stored in db'</span>)
            <span class="pl-k">except</span>:
                <span class="pl-s1">logging</span>.<span class="pl-en">info</span>(<span class="pl-s">f'Failed to add <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">performer</span><span class="pl-kos">}</span></span> to db'</span>)
                <span class="pl-s1">session</span>.<span class="pl-en">rollback</span>()
                <span class="pl-k">raise</span>
            <span class="pl-k">finally</span>:
                <span class="pl-s1">session</span>.<span class="pl-en">close</span>()</pre></div>
<p dir="auto"><strong>Have a nice day!</strong><br>
The really thing is I don't know how to add performer and add it but when when rating exist it should use that or other wise insert so basically how to add some fields of model and update some specific ones.</p>