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)

Did you read this? Might answer your question. http://stackoverflow.com/questions/28894257/enum-type-in-sqlalchemy-with-postgresql#28894354 — filiprem, Aug 18 '16 at 18:10
precisely THIS answer http://stackoverflow.com/a/33042283/540341 — filiprem, Aug 18 '16 at 18:25
Can you show the whole code including FacebookPost & TwitterPost class definitions? At least the field in question. — filiprem, Aug 18 '16 at 19:54
From my understanding you're essentially wanting to CREATE TYPE if i doesn't exist. There is an IF EXISTS on INSERTS but not CREATE TYPE. Maybe http://stackoverflow.com/a/28274121/1804656 would help? — kyle, Aug 18 '16 at 20:26

score 27 · Answer 1 · answered Apr 03 '19 at 08:38

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))
    ...

score 8 · Answer 2 · answered Dec 02 '20 at 00:43

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")

Mustansir Zia · Answer 3 · 2021-06-24T07:44:31.073

8

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.

edited Jun 24 '21 at 07:44

answered Jun 24 '21 at 07:37

Mustansir Zia

984
10
18

Note that you can import a python `enum.Enum` and then pass it directly as the first argument to the `ENUM` function. – Nick K9 Oct 20 '22 at 15:52

Nick K9 · Answer 4 · 2022-10-22T13:21:42.817

These answers were all helpful, but my situation was slightly different and yet very common, so I wanted to post another example. I was creating a new table AND a new Enum type. So Mustansir Zia's example didn't work as-is for me. Instead, I ran flask db migrate and then I needed to modify the alembic code slightly. The model in my app:

import enum
from sqlalchemy.types import Enum

class NewType(enum.Enum):
    TYPE_1 = "TYPE_1"


class MyClass(Base):
    __tablename__ = "my_class"

    a_type = db.Column(
        Enum(
            NewType,
            values_callable=lambda obj: obj._member_names_,
        ),
        nullable=False,
    )

And then in the migration script:

from sqlalchemy.dialects import postgresql
from myapp.models import NewType

def upgrade():
    new_type = postgresql.ENUM(NewType, name="new_type", create_type=False)
    new_type.create(op.get_bind(), checkfirst=True)

    op.create_table(
        "my_class",
        sa.Column("a_type", new_type, nullable=False),
    )

def downgrade():
    op.drop_table("my_class")

    new_type = postgresql.ENUM(NewType, name="new_type", create_type=False)
    new_type.drop(op.get_bind())

The key thing is that create_type=False argument is being used when just instantiating the type. Then the type is actually created, and then later in the create_table, the original type instance can be used without Postgres trying to create the type again.

SQLAlchemy. Creating tables that share enum

4 Answers4