postgresql (psycopg2) Enum type breaks if enum label contains a single quote (')

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')" {}

Comments (2)