[Sqlalchemy-tickets] [sqlalchemy] #2878: postgresql (psycopg2) Enum type breaks if enum label contains a single quote (')

[sqlalchemy <mi...@zz...>]

#2878: postgresql (psycopg2) Enum type breaks if enum label contains a single
quote (')
------------------------+-----------------------------------------
 Reporter:  kwohlfahrt  |           Owner:  zzzeek
     Type:  defect      |          Status:  new
 Priority:  medium      |       Milestone:
Component:  postgres    |        Severity:  no triage selected yet
 Keywords:              |  Progress State:  awaiting triage
------------------------+-----------------------------------------
 When creating an Enum column on a postgresql database, the following
 example errors out with a syntax error on the s in "Men's Captain". In
 postgresql single quotes in an enum label must be doubled, like {{{
 "Men''s Captain" }}}.

 {{{
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy import Column, Integer, Enum
 from sqlalchemy import create_engine
 from sqlalchemy.orm import sessionmaker

 Base = declarative_base()

 class Foo(Base):
     __tablename__ = 'foo'
     id = Column(Integer, primary_key=True)
     foo = Column(Enum("President", "Men's Captain",
 name="committee_position"))

 if __name__ == '__main__':
     engine = create_engine('postgresql://test@localhost/test')

     Base.metadata.create_all(engine)
 }}}

 Traceback

 {{{
 Traceback (most recent call last):
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 867, in _execute_context
     context)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/default.py",
 line 326, in do_execute
     cursor.execute(statement, parameters)
 psycopg2.ProgrammingError: syntax error at or near "s"
 LINE 1: ...TYPE committee_position AS ENUM ('President','Men's Captain'...
                                                              ^


 The above exception was the direct cause of the following exception:

 Traceback (most recent call last):
   File "test.py", line 16, in <module>
     Base.metadata.create_all(engine)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/schema.py", line
 2796, in create_all
     tables=tables)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 1479, in _run_visitor
     conn._run_visitor(visitorcallable, element, **kwargs)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 1122, in _run_visitor
     **kwargs).traverse_single(element)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/sql/visitors.py",
 line 109, in traverse_single
     return meth(obj, **kw)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/ddl.py", line
 70, in visit_metadata
     self.traverse_single(table, create_ok=True)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/sql/visitors.py",
 line 109, in traverse_single
     return meth(obj, **kw)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/ddl.py", line
 83, in visit_table
     _ddl_runner=self)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/event.py", line 407,
 in __call__
     fn(*args, **kw)
   File "/usr/lib64/python3.2/site-
 packages/sqlalchemy/util/langhelpers.py", line 407, in __call__
     return getattr(self.target, self.name)(*arg, **kw)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/types.py", line
 1937, in _on_table_create
     t._on_table_create(target, bind, **kw)
   File "/usr/lib64/python3.2/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 870, in
 _on_table_create
     self.create(bind=bind, checkfirst=checkfirst)
   File "/usr/lib64/python3.2/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 821, in create
     bind.execute(CreateEnumType(self))
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 662, in execute
     params)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 720, in _execute_ddl
     compiled
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 874, in _execute_context
     context)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 1024, in _handle_dbapi_exception
     exc_info
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/util/compat.py",
 line 187, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/util/compat.py",
 line 182, in reraise
     raise value.with_traceback(tb)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/base.py",
 line 867, in _execute_context
     context)
   File "/usr/lib64/python3.2/site-packages/sqlalchemy/engine/default.py",
 line 326, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
 near "s"
 LINE 1: ...TYPE committee_position AS ENUM ('President','Men's Captain'...
                                                              ^
  "CREATE TYPE committee_position AS ENUM ('President','Men's Captain')" {}
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2878>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python