Issue 1:
Migrated issue, originally created by Sean Mars (@seanmars)

I get the "TypeError: not enough arguments for format string" when i execute by compiled object with mysql.dialect().

But when i execute by complied object without mysql.dialect(), it is fine.

Env:

MySQL 5.6

Python 3.5

PyMySQL==0.7.9

SQLAlchemy==1.1.6

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)

Error log:

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

Issue 2:
Migrated issue, originally created by Michael Bayer (@zzzeek)

we detect the delete() of one object with the same PK as another which was just add()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.

The expected behavior of add() 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.

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.