sqlalchemy postgresql enum does not create type on db migrate
19,848
Solution 1
I decided this problem using that.
I changed code of migration and migration is look like this:
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
def upgrade():
banner_status = postgresql.ENUM('active', 'inactive', 'archive', name='banner_status')
banner_status.create(op.get_bind())
op.add_column('banner', sa.Column('status', sa.Enum('active', 'inactive', 'archive', name='banner_status'), nullable=True))
def downgrade():
op.drop_column('banner', 'status')
banner_status = postgresql.ENUM('active', 'inactive', 'archive', name='banner_status')
banner_status.drop(op.get_bind())
And now python manage.py db upgrade\downgrade
is succesfully executed.
Solution 2
I think this way is more simple:
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
def upgrade():
# others_column = ...
banner_status = postgresql.ENUM('active', 'inactive', 'archive', name='banner_status', create_type=False), nullable=False)
Also added the postgresql.ENUM
to your downgrade()
function if that needed.
Related videos on Youtube
Author by
Alexey Egorov
Updated on August 05, 2020Comments
-
Alexey Egorov over 3 years
I develop a web-app using Flask under Python3. I have a problem with postgresql enum type on db migrate/upgrade.
I added a column "status" to model:
class Banner(db.Model): ... status = db.Column(db.Enum('active', 'inactive', 'archive', name='banner_status')) ...
Generated migration by
python manage.py db migrate
is:from alembic import op import sqlalchemy as sa def upgrade(): op.add_column('banner', sa.Column('status', sa.Enum('active', 'inactive', 'archive', name='banner_status'), nullable=True)) def downgrade(): op.drop_column('banner', 'status')
And when I do
python manage.py db upgrade
I get an error:... sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "banner_status" does not exist LINE 1: ALTER TABLE banner ADD COLUMN status banner_status [SQL: 'ALTER TABLE banner ADD COLUMN status banner_status']
Why migration does not create a type "banner_status"?
What am I doing wrong?
$ pip freeze alembic==0.8.6 Flask==0.10.1 Flask-Fixtures==0.3.3 Flask-Login==0.3.2 Flask-Migrate==1.8.0 Flask-Script==2.0.5 Flask-SQLAlchemy==2.1 itsdangerous==0.24 Jinja2==2.8 Mako==1.0.4 MarkupSafe==0.23 psycopg2==2.6.1 python-editor==1.0 requests==2.10.0 SQLAlchemy==1.0.13 Werkzeug==0.11.9
-
Ilja Everilä almost 8 yearsFrom reading stackoverflow.com/questions/14845203/… and bitbucket.org/zzzeek/alembic/issues/67/… it'd seem you might have to manually create and drop the enums.
-
-
Geekfish over 6 yearsFor the downgrade you can also avoid duplication by executing SQL directly (at least for postgres):
op.execute("DROP TYPE banner_status;")
-
radzak over 4 years@Geekfish Why not move
postgresql.ENUM('active', 'inactive', 'archive', name='banner_status')
outside of the upgrade and downgrade methods? Would there be any downsides? -
Geekfish over 4 years@Jatimir it's been a while since I looked into it but your suggestion it's probably fine!
-
kevlarr over 4 yearsYou can actually pass
banner_status
as the column type directly, rather than redefining it viasa.Enum(...)
-
UselesssCat about 3 yearsyou dont need to declare all enumeration options on downgrade, just
bind = op.get_bind()
thensa.Enum(name='banner_status').drop(bind, checkfirst=False)