Comment attribute causes crash during table creation under MySQL if percent (%) symbol present in comment

Using SQLAlchemy==1.2.0b1, this code:

#! /usr/bin/env/python
# sqlalchemy_comment_mysql_bug.py

"""
-- In MySQL:

CREATE DATABASE dummy;
GRANT ALL PRIVILEGES ON dummy.* TO 'scott'@'localhost' IDENTIFIED BY 'tiger';
"""

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import Boolean, Integer

Base = declarative_base()


class Thing(Base):
    __tablename__ = "thing"
    id = Column(Integer, primary_key=True, autoincrement=True)
    weight_loss = Column(Boolean, comment="Weight loss of 5% or more")


url = "mysql+mysqldb://scott:tiger@127.0.0.1:3306/dummy?charset=utf8"
engine = create_engine(url, echo=True)
Base.metadata.create_all(engine)

produces this output:

2017-08-24 23:03:24,913 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2017-08-24 23:03:24,913 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,915 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-08-24 23:03:24,915 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,915 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-08-24 23:03:24,915 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,916 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-08-24 23:03:24,916 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,917 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-08-24 23:03:24,917 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,917 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2017-08-24 23:03:24,917 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,918 INFO sqlalchemy.engine.base.Engine DESCRIBE `thing`
2017-08-24 23:03:24,918 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,919 INFO sqlalchemy.engine.base.Engine ROLLBACK
2017-08-24 23:03:24,920 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE thing (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    weight_loss BOOL COMMENT 'Weight loss of 5% or more', 
    PRIMARY KEY (id), 
    CHECK (weight_loss IN (0, 1))
)
2017-08-24 23:03:24,920 INFO sqlalchemy.engine.base.Engine ()
2017-08-24 23:03:24,920 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/MySQLdb/cursors.py", line 238, in execute
    query = query % args
TypeError: not enough arguments for format string
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/MySQLdb/cursors.py", line 240, in execute
    self.errorhandler(self, ProgrammingError, str(m))
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/MySQLdb/connections.py", line 52, in defaulterrorhandler
    raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not enough arguments for format string
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "<input>", line 27, in <module>
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 3981, in create_all
    tables=tables)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1929, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1538, in _run_visitor
    **kwargs).traverse_single(element)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 757, in visit_metadata
    _is_metadata_operation=True)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
    compiled
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/MySQLdb/cursors.py", line 240, in execute
    self.errorhandler(self, ProgrammingError, str(m))
  File "/home/rudolf/dev/venvs/camcops/lib/python3.5/site-packages/MySQLdb/connections.py", line 52, in defaulterrorhandler
    raise errorclass(errorvalue)
sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) not enough arguments for format string [SQL: "\nCREATE TABLE thing (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tweight_loss BOOL COMMENT 'Weight loss of 5% or more', \n\tPRIMARY KEY (id), \n\tCHECK (weight_loss IN (0, 1))\n)\n\n"]

It works fine if you remove the % from the comment.

The cause is the lack of escaping for '%' in the comment string, causing it to be treated as a format specifier. It looks like sqlalchemy.dialects.mysql.base.MySQLDDLCompiler.get_column_specification escapes it for SQL, but then presumably it's not being escaped further for the Python database access library.

Comments (5)