TypeError: not enough arguments for format string

[Sean Mars]

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

[Mike Bayer]

hello -

this is not enough detail. Please provide a complete mcve illustrating necessary details including what this table looks like, what "conn" is, stack trace, anything. thanks.

[Sean Mars]

Hi,

I forgot completed the code. Updated.

Thanks.

[Mike Bayer]

Why do you need to execute a compiled object directly?

[Mike Bayer]

Recent versions of pymysql report the paramstyle as "pyformat", whereas the default for a plain vanilla mysql dialect is "format". the "pyformat" doesn't come in until the module is imported which is not happening because you are splitting out between two different dialects, the one in your engine, and the one in your compilation, and the system looks at the dialect paramstyle and not that of the "compiled" object when organizing the bound parameters. it only "works" because pymysql actually accepts multiple paramstyles. I guess we can change the executor to check compiled.positional in this one case. But this is an odd use case and you probably don't need to be executing compiled objects directly (alas it's part of the public API).

[Mike Bayer]

diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index b8c2d28..3968663 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -627,7 +627,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
         # into a dict or list to be sent to the DBAPI's
         # execute() or executemany() method.
         parameters = []
-        if dialect.positional:
+        if compiled.positional:
             for compiled_params in self.compiled_parameters:
                 param = []
                 for key in self.compiled.positiontup:

[Mike Bayer]

so I don't know what your goal is, but for now, you need to use the right paramstyle, so if you must use different dialects you need to pass that through:

id = 1
value = 100
ins = table.insert().values(id=id, value=value).compile(
    bind=conn, dialect=mysql.dialect(paramstyle=engine.dialect.paramstyle))
conn.execute(ins)

passing the engine/connection to compile() is all that's needed however to get the correct dialect. there's no need to pass dialect at all if the connection is present.

[Mike Bayer]

Consult compiled paramstyle on execute_compiled

Fixed bug where in the unusual case of passing a :class:.Compiled object directly to :meth:.Connection.execute, the dialect with which the :class:.Compiled object were generated was not consulted for the paramstyle of the string statement, instead assuming it would match the dialect-level paramstyle, causing mismatches to occur.

Change-Id: I114e4db2183fbb75bb7c0b0641f5a161855696ee Fixes: #3938

→ <<cset 7b056709c0f8>>

[Sean Mars]

Hi, thanks your help.

Because i want to use the INSERT... ON DUPLICATE KEY UPDATE which schema is only for MySQL. And I found some script in older issue issue-3133 warning-for-custom-insert-mysql. And if I do not use the .Compiled to tell what dialect i use the @compiles(Insert, 'mysql') will not work, so I had to use it.