Re: Migration fails when changing to single table inheritance

Wed, 07 Jun 2017 15:15:07 -0700 


On 06/07/2017 04:44 PM, Michael wrote:

Hi all, I have a class called MediaChapter(Base), which I've refactored 
into MediaBase(Base) and MediaChapter(MediaBase) When I run the 
migration, I see:


|

psycopg2.IntegrityError:insert orupdate on table "mediachapter"violates 
foreign key constraint "fk_mediachapter_id_mediabase"

DETAIL:Key(id)=(570)isnotpresent intable "mediabase".



here's the real error with the SQL:


sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) insert or 
update on table "mediachapter" violates foreign key constraint 
"fk_mediachapter_id_mediabase"

DETAIL:  Key (id)=(570) is not present in table "mediabase".

 [SQL: 'ALTER TABLE mediachapter ADD CONSTRAINT 
fk_mediachapter_id_mediabase FOREIGN KEY(id) REFERENCES mediabase (id)']



the error means that your "mediachapter" table contains an id, "570", 
which is not present in the "mediabase" table.



it looks like you are starting with a populated "mediachapter" table 
then adding a new table "mediabase".  Before you create the constraint, 
you need to run an INSERT on "mediabase" that selects from 
"mediachapter", like:



INSERT INTO mediabase (id, col1, col2, ...) SELECT id, col1, col2, .. 
FROM mediachapter






|



I'm not sure exactly what this means because I create a mediabase.id.

I did
|

ws-alembic -c kjvrvg/conf/development.ini -x packages=all revision 
--auto-m "MediaBase-MediaChapter subclassing"

|


-- SUCCESS

then
|
ws-alembic -c kjvrvg/conf/development.ini -x packages=all upgrade head
|


-- FAILED


Here are my models, MediaBase:

|
classMediaBase(Base):
#: The table in the database
     __tablename__ ="mediabase"

#: Database primary key for the row (running counter)
     id =Column(Integer,autoincrement=True,primary_key=True)

# table inheritance
     media_type =Column(String(32),nullable=False)

#: Publicly exposed non-guessable
     uuid =Column(UUID(as_uuid=True),default=uuid4)

     localizedname =Column(Unicode(128),default=None)

#: url
     url =Column(Unicode(384),default=None)

# full iso language-locale identifier i.e. zh-Hans-US
     language_id =Column(String(16),default=None)

# name of the person presenting the material
     presenter_name =Column(Unicode(64),default=None)
     source_material =Column(Unicode(128),default=None)

# table inheritance
     __mapper_args__ ={'polymorphic_on':media_type}

def__repr__(self):
"""Shell and debugger presentation."""

return'{} ({}) {} 
<{}>'.format(self.localizedname,self.language_id,str(self.uuid),self.url)


def__str__(self):
"""Python default and admin UI string presentation."""

return'{} ({}) presenter: {} source: {} 
  <{}>'.format(self.localizedname,self.language_id,self.presenter_name,self.source_material,self.url)



|


and MediaChapter:

|
classMediaChapter(MediaBase):

#: The table in the database
     __tablename__ ="mediachapter"

     __mapper_args__ ={'polymorphic_identity':'chapter'}

     id =Column(Integer,ForeignKey('mediabase.id'),primary_key=True)

#: Which chapter this media is part of
     chapter_id =Column(Integer,ForeignKey('chapter.id'))
     chapter =relationship("Chapter",back_populates="mediachapter")
|





and finally here is the auto-generated migration. I put all files in a 
github Gist.


|
https://gist.github.com/mazz/7d63e521316859f4ae852e5cea5d84eb
|



Any suggestions?
Mazz

|

"""MediaBase-MediaChapter subclassing

Revision ID: a00980918d75
Revises: e74ba4203098
Create Date: 2017-06-07 16:10:29.807437

"""

# revision identifiers, used by Alembic.
revision ='a00980918d75'
down_revision ='e74ba4203098'
branch_labels =None
depends_on =None

importdatetime
importwebsauna.system.model.columns

fromsqlalchemy.types importText# Needed from proper creation of JSON 
fields as Alembic inserts astext_type=Text() row


fromalembic importop
importsqlalchemy assa
fromsqlalchemy.dialects importpostgresql

defupgrade():
# ### commands auto generated by Alembic - please adjust! ###
     op.create_table('mediabase',
     sa.Column('id',sa.Integer(),nullable=False),
     sa.Column('media_type',sa.String(length=32),nullable=False),
     sa.Column('uuid',postgresql.UUID(as_uuid=True),nullable=True),
     sa.Column('localizedname',sa.Unicode(length=128),nullable=True),
     sa.Column('url',sa.Unicode(length=384),nullable=True),
     sa.Column('language_id',sa.String(length=16),nullable=True),
     sa.Column('presenter_name',sa.Unicode(length=64),nullable=True),
     sa.Column('source_material',sa.Unicode(length=128),nullable=True),
     sa.PrimaryKeyConstraint('id',name=op.f('pk_mediabase'))
)
     op.alter_column('group','created_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
     op.alter_column('group','updated_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)

     
op.create_foreign_key(op.f('fk_mediachapter_id_mediabase'),'mediachapter','mediabase',['id'],['id'])

     op.drop_column('mediachapter','source_material')
     op.drop_column('mediachapter','presenter_name')
     op.drop_column('mediachapter','localizedname')
     op.drop_column('mediachapter','url')
     op.drop_column('mediachapter','uuid')
     op.drop_column('mediachapter','language_id')
     op.alter_column('user_activation','created_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
     op.alter_column('user_activation','expires_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=False)
     op.alter_column('user_activation','updated_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
     op.alter_column('users','activated_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
     op.alter_column('users','created_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
     op.alter_column('users','last_auth_sensitive_operation_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
     op.alter_column('users','last_login_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
     op.alter_column('users','updated_at',
                existing_type=postgresql.TIMESTAMP(timezone=True),
                type_=websauna.system.model.columns.UTCDateTime(),
                existing_nullable=True)
# ### end Alembic commands ###


defdowngrade():
# ### commands auto generated by Alembic - please adjust! ###
     op.alter_column('users','updated_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.alter_column('users','last_login_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.alter_column('users','last_auth_sensitive_operation_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.alter_column('users','created_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.alter_column('users','activated_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.alter_column('user_activation','updated_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.alter_column('user_activation','expires_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=False)
     op.alter_column('user_activation','created_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)

     
op.add_column('mediachapter',sa.Column('language_id',sa.VARCHAR(length=16),autoincrement=False,nullable=True))
     
op.add_column('mediachapter',sa.Column('uuid',postgresql.UUID(),autoincrement=False,nullable=True))
     
op.add_column('mediachapter',sa.Column('url',sa.VARCHAR(length=384),autoincrement=False,nullable=True))
     
op.add_column('mediachapter',sa.Column('localizedname',sa.VARCHAR(length=128),autoincrement=False,nullable=True))
     
op.add_column('mediachapter',sa.Column('presenter_name',sa.VARCHAR(length=64),autoincrement=False,nullable=True))
     
op.add_column('mediachapter',sa.Column('source_material',sa.VARCHAR(length=128),autoincrement=False,nullable=True))
     
op.drop_constraint(op.f('fk_mediachapter_id_mediabase'),'mediachapter',type_='foreignkey')

     op.alter_column('group','updated_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.alter_column('group','created_at',
                existing_type=websauna.system.model.columns.UTCDateTime(),
                type_=postgresql.TIMESTAMP(timezone=True),
                existing_nullable=True)
     op.drop_table('mediabase')
# ### end Alembic commands ###
|


--

You received this message because you are subscribed to the Google 
Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to sqlalchemy-alembic+unsubscr...@googlegroups.com 
<mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.

For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.






















					Reply via email to