CODEWITHSUNDEEP
pythonpostgresqlenumssqlalchemy
FranGoitia
FranGoitia

Reputation: 1993

SQLAlchemy. Creating tables that share enum

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)

Upvotes: 19

Views: 11401

Answers (4)

Nick K9
Nick K9

Reputation: 4663

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.

Upvotes: 1

Mustansir Zia
Mustansir Zia

Reputation: 1004

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.

Upvotes: 8

Tectuktitlay
Tectuktitlay

Reputation: 81

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

Upvotes: 8

Peter Bašista
Peter Bašista

Reputation: 907

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

Upvotes: 30

Related Questions