<p dir="auto">Thanks for the really fast reply. Interestingly this is a difference between SQLite and MySQL dialects. Bulk upsert in MySQL (see discussion <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="4870885" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/discussions/9328" data-hovercard-type="discussion" data-hovercard-url="/sqlalchemy/sqlalchemy/discussions/9328/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/discussions/9328">#9328</a>) works fine.  I worked up a SSCCE for this also:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import sqlalchemy as db
import sqlalchemy.dialects.sqlite as sqlite
from sqlalchemy import delete, select, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = 'user'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30), nullable=False)
    count: Mapped[int] = mapped_column(nullable=True)


engine = db.create_engine('sqlite:///:memory:')
conn = engine.connect()

# setup step 0 - ensure the table exists
Base().metadata.create_all(bind=engine)

# setup step 1 - clean out rows with id 1..5
del_stmt = delete(User).where(User.id.in_([1, 2, 3, 4, 5]))
conn.execute(del_stmt)
conn.commit()
sel_stmt = select(User)
users = list(conn.execute(sel_stmt))
print(f'Table size after cleanout: {len(users)}')

# setup step 2 - insert 4 rows
ins_stmt = sqlite.insert(User).values(
    [
        {'id': 1, 'name': 'x', 'count': 10},
        {'id': 2, 'name': 'y', 'count': 20},
        {'id': 3, 'name': 'w'}, # no count
        {'id': 4, 'name': 'z', 'count': 40},
    ]
)
# FAILS HERE IF COUNT IS MISSING
conn.execute(ins_stmt)
conn.commit()
users = list(conn.execute(sel_stmt))
print(f'Table size after insert: {len(users)}')

# demonstrate upsert
ups_stmt = sqlite.insert(User).values(
    [
        {'id': 1, 'name': 'xx', 'count': 10},
        {'id': 2, 'name': 'yy', 'count': 20},
        {'id': 3, 'name': 'ww'}, # no count
        {'id': 5, 'name': 'new', 'count': 50},
    ]
)
ups_stmt = ups_stmt.on_conflict_do_update(
    index_elements=['id'],
    set_=dict(
        name=ups_stmt.excluded.name,
        count=ups_stmt.excluded.count))
# if you want to see the compiled result
x = ups_stmt.compile(dialect=sqlite.dialect())
print(x.string, x.construct_params())
conn.execute(ups_stmt)
conn.commit()

users = list(conn.execute(sel_stmt))
print(f'Table size after upsert: {len(users)}')"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">as</span> <span class="pl-s1">db</span>
<span class="pl-k">import</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">dialects</span>.<span class="pl-s1">sqlite</span> <span class="pl-k">as</span> <span class="pl-s1">sqlite</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">delete</span>, <span class="pl-s1">select</span>, <span class="pl-v">String</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-v">DeclarativeBase</span>, <span class="pl-v">Mapped</span>, <span class="pl-s1">mapped_column</span>


<span class="pl-k">class</span> <span class="pl-v">Base</span>(<span class="pl-v">DeclarativeBase</span>):
    <span class="pl-k">pass</span>


<span class="pl-k">class</span> <span class="pl-v">User</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'user'</span>
    <span class="pl-s1">id</span>: <span class="pl-v">Mapped</span>[<span class="pl-s1">int</span>] <span class="pl-c1">=</span> <span class="pl-en">mapped_column</span>(<span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">name</span>: <span class="pl-v">Mapped</span>[<span class="pl-s1">str</span>] <span class="pl-c1">=</span> <span class="pl-en">mapped_column</span>(<span class="pl-v">String</span>(<span class="pl-c1">30</span>), <span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">False</span>)
    <span class="pl-s1">count</span>: <span class="pl-v">Mapped</span>[<span class="pl-s1">int</span>] <span class="pl-c1">=</span> <span class="pl-en">mapped_column</span>(<span class="pl-s1">nullable</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s1">db</span>.<span class="pl-en">create_engine</span>(<span class="pl-s">'sqlite:///:memory:'</span>)
<span class="pl-s1">conn</span> <span class="pl-c1">=</span> <span class="pl-s1">engine</span>.<span class="pl-en">connect</span>()

<span class="pl-c"># setup step 0 - ensure the table exists</span>
<span class="pl-v">Base</span>().<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)

<span class="pl-c"># setup step 1 - clean out rows with id 1..5</span>
<span class="pl-s1">del_stmt</span> <span class="pl-c1">=</span> <span class="pl-en">delete</span>(<span class="pl-v">User</span>).<span class="pl-en">where</span>(<span class="pl-v">User</span>.<span class="pl-s1">id</span>.<span class="pl-en">in_</span>([<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-c1">3</span>, <span class="pl-c1">4</span>, <span class="pl-c1">5</span>]))
<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">del_stmt</span>)
<span class="pl-s1">conn</span>.<span class="pl-en">commit</span>()
<span class="pl-s1">sel_stmt</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-v">User</span>)
<span class="pl-s1">users</span> <span class="pl-c1">=</span> <span class="pl-en">list</span>(<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">sel_stmt</span>))
<span class="pl-en">print</span>(<span class="pl-s">f'Table size after cleanout: <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-en">len</span>(<span class="pl-s1">users</span>)<span class="pl-kos">}</span></span>'</span>)

<span class="pl-c"># setup step 2 - insert 4 rows</span>
<span class="pl-s1">ins_stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">sqlite</span>.<span class="pl-en">insert</span>(<span class="pl-v">User</span>).<span class="pl-en">values</span>(
    [
        {<span class="pl-s">'id'</span>: <span class="pl-c1">1</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'x'</span>, <span class="pl-s">'count'</span>: <span class="pl-c1">10</span>},
        {<span class="pl-s">'id'</span>: <span class="pl-c1">2</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'y'</span>, <span class="pl-s">'count'</span>: <span class="pl-c1">20</span>},
        {<span class="pl-s">'id'</span>: <span class="pl-c1">3</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'w'</span>}, <span class="pl-c"># no count</span>
        {<span class="pl-s">'id'</span>: <span class="pl-c1">4</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'z'</span>, <span class="pl-s">'count'</span>: <span class="pl-c1">40</span>},
    ]
)
<span class="pl-c"># FAILS HERE IF COUNT IS MISSING</span>
<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">ins_stmt</span>)
<span class="pl-s1">conn</span>.<span class="pl-en">commit</span>()
<span class="pl-s1">users</span> <span class="pl-c1">=</span> <span class="pl-en">list</span>(<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">sel_stmt</span>))
<span class="pl-en">print</span>(<span class="pl-s">f'Table size after insert: <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-en">len</span>(<span class="pl-s1">users</span>)<span class="pl-kos">}</span></span>'</span>)

<span class="pl-c"># demonstrate upsert</span>
<span class="pl-s1">ups_stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">sqlite</span>.<span class="pl-en">insert</span>(<span class="pl-v">User</span>).<span class="pl-en">values</span>(
    [
        {<span class="pl-s">'id'</span>: <span class="pl-c1">1</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'xx'</span>, <span class="pl-s">'count'</span>: <span class="pl-c1">10</span>},
        {<span class="pl-s">'id'</span>: <span class="pl-c1">2</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'yy'</span>, <span class="pl-s">'count'</span>: <span class="pl-c1">20</span>},
        {<span class="pl-s">'id'</span>: <span class="pl-c1">3</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'ww'</span>}, <span class="pl-c"># no count</span>
        {<span class="pl-s">'id'</span>: <span class="pl-c1">5</span>, <span class="pl-s">'name'</span>: <span class="pl-s">'new'</span>, <span class="pl-s">'count'</span>: <span class="pl-c1">50</span>},
    ]
)
<span class="pl-s1">ups_stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">ups_stmt</span>.<span class="pl-en">on_conflict_do_update</span>(
    <span class="pl-s1">index_elements</span><span class="pl-c1">=</span>[<span class="pl-s">'id'</span>],
    <span class="pl-s1">set_</span><span class="pl-c1">=</span><span class="pl-en">dict</span>(
        <span class="pl-s1">name</span><span class="pl-c1">=</span><span class="pl-s1">ups_stmt</span>.<span class="pl-s1">excluded</span>.<span class="pl-s1">name</span>,
        <span class="pl-s1">count</span><span class="pl-c1">=</span><span class="pl-s1">ups_stmt</span>.<span class="pl-s1">excluded</span>.<span class="pl-s1">count</span>))
<span class="pl-c"># if you want to see the compiled result</span>
<span class="pl-s1">x</span> <span class="pl-c1">=</span> <span class="pl-s1">ups_stmt</span>.<span class="pl-en">compile</span>(<span class="pl-s1">dialect</span><span class="pl-c1">=</span><span class="pl-s1">sqlite</span>.<span class="pl-en">dialect</span>())
<span class="pl-en">print</span>(<span class="pl-s1">x</span>.<span class="pl-s1">string</span>, <span class="pl-s1">x</span>.<span class="pl-en">construct_params</span>())
<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">ups_stmt</span>)
<span class="pl-s1">conn</span>.<span class="pl-en">commit</span>()

<span class="pl-s1">users</span> <span class="pl-c1">=</span> <span class="pl-en">list</span>(<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">sel_stmt</span>))
<span class="pl-en">print</span>(<span class="pl-s">f'Table size after upsert: <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-en">len</span>(<span class="pl-s1">users</span>)<span class="pl-kos">}</span></span>'</span>)</pre></div>
<p dir="auto"><em>Originally posted by <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/chrisinmtown/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/chrisinmtown">@chrisinmtown</a> in <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="5133607" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/discussions/9702" data-hovercard-type="discussion" data-hovercard-url="/sqlalchemy/sqlalchemy/discussions/9702/hovercard?comment_id=5733073" href="https://github.com/sqlalchemy/sqlalchemy/discussions/9702#discussioncomment-5733073">#9702 (reply in thread)</a></em></p>
<p dir="auto">Error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlalchemy.exc.CompileError: INSERT value for column ip_time.time is explicitly rendered as a boundparameter in the VALUES clause;
a Python-side value or SQL expression is required"><pre class="notranslate"><code class="notranslate">sqlalchemy.exc.CompileError: INSERT value for column ip_time.time is explicitly rendered as a boundparameter in the VALUES clause;
a Python-side value or SQL expression is required
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">If we have a self-referential, many-to-many relation (e.g. network of nodes), when we define a validator on one side of the symmetrical relationship (namely children), append works or doesn't work correctly depending on whether the data is commited or not. Works fine if there's no validator or changes are commited at the very end of operation.</p>
<p dir="auto">An obvious workaround is to change the append to append to the other side of the relationship (ie. parent.children.append instead of children.parents.append), so I've set the ticket priority to low. However, something is definitely wrong with the code and this can be a manifestation of some nastier bug, ergo the ticket.</p>
<p dir="auto">Self-contained test case is attached. My setup: SQLAlchemy 0.5.5, Python 2.5 on win32</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1526/validator_bug.py">validator_bug.py</a> | <a href="../wiki/imported_issue_attachments/1526/backref_validator_test.py">backref_validator_test.py</a></p>