SQLAlchemy. Creating tables that share enum

Question

Models FacebookPost and TwitterPost share an enum called types. This enum is correctly created when creating facebook_posts table, but when trying to create twitter_posts table, there is an attempt to recreate this type which results in an error.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "types" already exists
 [SQL: "CREATE TYPE types AS ENUM ('Video', 'GIF', 'Scratch Reel', 'Card', 'Video Card', 'Text', 'Photo', 'Shared Article', 'Reply', 'Canvas', 'Carousel', 'Video Carousel', 'Link', 'Status')"]

This is the way I'm creating the database. I can't use Base.metadata.create_all, because I need to be explicit in terms of what tables are created

Engine = create_engine(db_url, echo=False)
Campaign.__table__.create(Engine)
SubCampaign.__table__.create(Engine)
Creative.__table__.create(Engine)
Hashtag.__table__.create(Engine)
FacebookPost.__table__.create(Engine)
TwitterPost.__table__.create(Engine)

I'm creating the enums this way:

from sqlalchemy import Enum
types = ('Video', 'GIF', 'Scratch Reel', 'Card', 'Video Card',
         'Text', 'Photo', 'Shared Article', 'Reply', 'Canvas',
         'Carousel', 'Video Carousel', 'Link', 'Status')
goals = ('CTR', 'ER', 'Awareness', 'CPGA')
sources = ('Facebook', 'Twitter', 'Instagram', 'Tumblr')

vars_ = locals().copy()
for k, v in vars_.items():
    if isinstance(v, tuple):
        locals()[k] = Enum(*v, name=k)

Answer 1

For anyone using Alembic and having this issue.

There's also a create_type kwarg for postgresql.ENUM. which is used to set the schema for an enum type column in the alembic migration script.

Here's what my column definition looks like. (Which uses existing an existing enum)

sa.Column('ActionType', postgresql.ENUM('Primary', 'Secondary', name='actiontype', create_type=False), nullable=True),

This will now use the existing enum for the new column without creating a new one.

I'm using SQLAlchemy==1.1.1 and alembic==0.8.8 in my requirements file.

I've given a similar answer here.

Answer 2

The generic Enum class does not offer any control over emitting the CREATE TYPE statement. But a PostgreSQL-specific alternative ENUM has a parameter create_type which can be used to disable it:

from sqlalchemy.dialects.postgresql import ENUM


class TwitterPost(Base):
    ...
    type = Column("type", ENUM(*types, name="post_type", create_type=False))
    ...

Answer 3

I faced a similar problem in Alembic and used a workaround.

The first example doesn't work. SQLAlchemy creates the enum when create is called on it, but tries to create it again when it creates the tables, causing an error.

NEW_ENUM = sa.Enum(
    "A",
    "B",
    "C",
    name="my_enum",
    schema="my_schema"
)

NEW_ENUM.create(op.get_bind())

op.create_table(
    "table1",
    sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("column1", sa.String),
    sa.Column("column2", NEW_ENUM),
    schema="my_schema",
)

op.create_table(
    "table2",
    sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("column1", sa.Integer),
    sa.Column("column2", NEW_ENUM),
    schema="my_schema",
)

However, creating the tables without the enum columns and adding them afterwards works. The enum is created once on the database (Postgres in my case) and used for the two tables in the added columns:

NEW_ENUM = sa.Enum(
    "A",
    "B",
    "C",
    name="my_enum",
    schema="my_schema"
)

NEW_ENUM.create(op.get_bind())

op.create_table(
    "table1",
    sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("column1", sa.String),
    schema="my_schema",
)

op.add_column("table1", sa.Column("column2", NEW_ENUM), schema="my_schema")

op.create_table(
    "table2",
    sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("column1", sa.Integer),
    schema="my_schema",
)

op.add_column("table2", sa.Column("column2", NEW_ENUM), schema="my_schema")