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)
-
repo owner -
repo owner - changed milestone to 1.2
-
repo owner this is addressed by https://gerrit.sqlalchemy.org/489 which should go through soon.
-
reporter Lovely; thank you!
-
repo owner - changed status to resolved
Apply percent sign escaping to literal binds, comments
Fixed bug in new percent-sign support (e.g.
3740
) where a bound parameter rendered with literal_binds would fail to escape percent-signs for relevant dialects. In addition, ensured new table / column comment support feature also fully makes use of literal-rendered parameters so that this percent sign support takes place with table / column comment DDL as well, allowing percent sign support for the mysql / psycopg2 backends that require escaping of percent signs.Change-Id: Ia4136a300933e9bc6a01a7b9afd5c7b9a3fee4e3 Fixes:
#4054Fixes:#4052→ <<cset 2392ae1900f1>>
- Log in to comment
This as well as the documentation issues you've posted can move forward quickly with pull requests