<p dir="auto"><strong>Migrated issue, originally created by Sean Mars (<a href="https://github.com/seanmars">@seanmars</a>)</strong></p>
<p dir="auto">I get the "TypeError: not enough arguments for format string" when i execute by compiled object with mysql.dialect().</p>
<p dir="auto">But when i execute by complied object without mysql.dialect(), it is fine.</p>
<p dir="auto">Env:</p>
<ul dir="auto">
<li>MySQL 5.6</li>
<li>Python 3.5
<ul dir="auto">
<li>PyMySQL==0.7.9</li>
<li>SQLAlchemy==1.1.6</li>
</ul>
</li>
</ul>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import create_engine
from sqlalchemy import Table, MetaData, text
from sqlalchemy.dialects import mysql

# The score table just two filed id(vchar(20)), value(int)
engine = create_engine('mysql+pymysql://root:root@127.0.0.1/score?charset=utf8mb4')
conn = engine.connect()
meta = MetaData()
table = Table('score', meta, autoload=True, autoload_with=conn)
id = 1
value = 100
table.insert().values(id=id, value=value).compile(bind=conn, dialect=mysql.dialect())
conn.execute(ins)"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Table</span>, <span class="pl-v">MetaData</span>, <span class="pl-s1">text</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">dialects</span> <span class="pl-k">import</span> <span class="pl-s1">mysql</span>

<span class="pl-c"># The score table just two filed id(vchar(20)), value(int)</span>
<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'mysql+pymysql://root:root@127.0.0.1/score?charset=utf8mb4'</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-s1">meta</span> <span class="pl-c1">=</span> <span class="pl-v">MetaData</span>()
<span class="pl-s1">table</span> <span class="pl-c1">=</span> <span class="pl-v">Table</span>(<span class="pl-s">'score'</span>, <span class="pl-s1">meta</span>, <span class="pl-s1">autoload</span><span class="pl-c1">=</span><span class="pl-c1">True</span>, <span class="pl-s1">autoload_with</span><span class="pl-c1">=</span><span class="pl-s1">conn</span>)
<span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-c1">1</span>
<span class="pl-s1">value</span> <span class="pl-c1">=</span> <span class="pl-c1">100</span>
<span class="pl-s1">table</span>.<span class="pl-en">insert</span>().<span class="pl-en">values</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-s1">id</span>, <span class="pl-s1">value</span><span class="pl-c1">=</span><span class="pl-s1">value</span>).<span class="pl-en">compile</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">conn</span>, <span class="pl-s1">dialect</span><span class="pl-c1">=</span><span class="pl-s1">mysql</span>.<span class="pl-en">dialect</span>())
<span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">ins</span>)</pre></div>
<p dir="auto">Error log:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;/test/db/score.py&quot;, line 33, in insert
    conn.execute(ins)
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py&quot;, line 945, in execute
    return meth(self, multiparams, params)
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py&quot;, line 227, in _execute_on_connection
    return connection._execute_compiled(self, multiparams, params)
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py&quot;, line 1075, in _execute_compiled
    compiled, parameters
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py&quot;, line 1189, in _execute_context
    context)
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py&quot;, line 1396, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py&quot;, line 187, in reraise
    raise value
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py&quot;, line 1182, in _execute_context
    context)
  File &quot;/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py&quot;, line 470, in do_execute
    cursor.execute(statement, parameters)
  File &quot;/test/venv/lib/python3.5/site-packages/pymysql/cursors.py&quot;, line 164, in execute
    query = self.mogrify(query, args)
  File &quot;/test/venv/lib/python3.5/site-packages/pymysql/cursors.py&quot;, line 143, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not enough arguments for format string"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/test/db/score.py", line 33, in insert
    conn.execute(ins)
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 227, in _execute_on_connection
    return connection._execute_compiled(self, multiparams, params)
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1075, in _execute_compiled
    compiled, parameters
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1396, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/test/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/test/venv/lib/python3.5/site-packages/pymysql/cursors.py", line 164, in execute
    query = self.mogrify(query, args)
  File "/test/venv/lib/python3.5/site-packages/pymysql/cursors.py", line 143, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not enough arguments for format string
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">we detect the <code class="notranslate">delete()</code> of one object with the same PK as another which was just <code class="notranslate">add()</code>ed, and convert what would be a DELETE and INSERT into an UPDATE.  The main reason for this was that we issue INSERT statements first, then DELETEs, and there was no obvious way to get the unit of work to issue the DELETE first for this one particular record.</p>
<p dir="auto">The expected behavior of <code class="notranslate">add()</code> is INSERT.  The table may have triggers and other defaults that only trigger on INSERT and not UPDATE.  Fields which are undefined on the instance when it is added are expected to be their default values in the DB, which is usually NULL.  The current logic takes none of this into account.   I'm not sure what the current behavior is for related objects/foreign keys.</p>
<p dir="auto">The change here must be that the whole "convert INSERT/DELETE to an UPDATE" must be removed.  The unitofwork must issue the DELETE for any row switch object before it issues the INSERTs for that mapper.  The most obvious way to do this would be to flip the order of DELETEs and INSERTs wholesale for the UOW.   Some experimentation is suggesting that this is much harder than it sounds.</p>