[Sqlalchemy-tickets] Issue #4348: Table name 'function' not quoted in MySQL which is now a syntax e
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-tickets] Issue #4348: Table name 'function' not quoted in MySQL which is now a syntax error in version 8 (zzzeek/sqlalchemy)
|
From: Alex H. <iss...@bi...> - 2018-10-15 20:07:15
|
New issue 4348: Table name 'function' not quoted in MySQL which is now a syntax error in version 8 https://bitbucket.org/zzzeek/sqlalchemy/issues/4348/table-name-function-not-quoted-in-mysql Alex Hall: I have a table named 'function', and when SQLAlchemy creates it, it sends the SQL `CREATE TABLE function (...)`. This was valid in MySQL 5.6, but in the new MySQL 8 it's a syntax error because `function` is a reserved keyword. Quoting the table name with backticks works, so SQLAlchemy should probably do that. While waiting for a fix, how can I force SQLAlchemy to always quote identifiers? I don't want to change the table name. I'm using SQLAlchemy==1.2.12, mysql-connector==2.1.6, and MySQL versions as follows: ``` mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+-----------------------+ | Variable_name | Value | +-------------------------+-----------------------+ | innodb_version | 8.0.12 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | version | 8.0.12 | | version_comment | Homebrew | | version_compile_machine | x86_64 | | version_compile_os | osx10.13 | | version_compile_zlib | 1.2.11 | +-------------------------+-----------------------+ ``` Code to create the table: ``` #!python class Base(object): @declared_attr def __tablename__(cls): return cls.__name__.lower() Base = declarative_base(cls=Base) class Function(Base): ... # column definitions Base.metadata.create_all(engine) ``` Full traceback: ``` #!python Traceback (most recent call last): File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute cursor.execute(statement, parameters) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/cursor.py", line 559, in execute self._handle_result(self._connection.cmd_query(stmt)) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 494, in cmd_query result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 396, in _handle_result raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'function ( id INTEGER NOT NULL AUTO_INCREMENT, file TEXT, name TEXT, html' at line 1 The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/runpy.py", line 170, in _run_module_as_main "__main__", mod_spec) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/runpy.py", line 85, in _run_code exec(code, run_globals) File "/Users/alexhall/Desktop/python/treetrace/birdseye/__main__.py", line 5, in <module> from birdseye.server import main File "/Users/alexhall/Desktop/python/treetrace/birdseye/server.py", line 37, in <module> db = Database() File "/Users/alexhall/Desktop/python/treetrace/birdseye/db.py", line 169, in __init__ Base.metadata.create_all(engine) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 4005, in create_all tables=tables) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1940, in _run_visitor conn._run_visitor(visitorcallable, element, **kwargs) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor **kwargs).traverse_single(element) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single return meth(obj, **kw) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 757, in visit_metadata _is_metadata_operation=True) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single return meth(obj, **kw) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_table include_foreign_key_constraints=include_foreign_key_constraints File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute return meth(self, multiparams, params) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl compiled File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context context) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 248, in reraise raise value.with_traceback(tb) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute cursor.execute(statement, parameters) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/cursor.py", line 559, in execute self._handle_result(self._connection.cmd_query(stmt)) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 494, in cmd_query result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 396, in _handle_result raise errors.get_exception(packet) sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'function ( id INTEGER NOT NULL AUTO_INCREMENT, file TEXT, name TEXT, html' at line 1 [SQL: '\nCREATE TABLE function (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tfile TEXT, \n\tname TEXT, \n\thtml_body LONGTEXT, \n\tlineno INTEGER, \n\tdata LONGTEXT, \n\thash VARCHAR(64), \n\tbody_hash VARCHAR(64), \n\tPRIMARY KEY (id), \n\tCONSTRAINT everything_unique UNIQUE (hash)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405) ``` |
