Re: Migration fails when changing to single table inheritance
Yes, I'll try Plan A first(do the INSERT FROM first) But mainly out of academic curiosity, if I were to start with a blank slate as you say, I'd still need to import the existing data that is currently in the non-inheritance table schema? So I guess I would just drop the database, upgrade to head. At this point I have the latest inheritance-based schema but no data. Say I dumped the db to a file before I dropped it, does alembic have facilities to take a dumped db and insert it into a new/slightly different schema? On Wednesday, June 7, 2017 at 7:12:27 PM UTC-4, mike bayer wrote: > > > > On 06/07/2017 07:01 PM, Michael wrote: > > Thanks for the insight Mike. I guess the best way to go about that would > > be to just call the raw insert sql statemen in the migration? like in > > > https://stackoverflow.com/questions/23206562/sqlalchemy-executing-raw-sql-with-parameter-bindings/23206636#23206636 > > > i'd start with that just to make sure it gets it working.From there, > we do support insert().from_select(select()) if you have the inclination > to build a Core SQL statement out of it but if you are only targeting > Postgresql it's not critical. > > > http://docs.sqlalchemy.org/en/latest/core/dml.html?highlight=insert%20from_select#sqlalchemy.sql.expression.Insert.from_select > > > > Since this app is not in production yet, would it be easier to make the > > current schema the initial schema and just insert the data that is in > > the MediaChapter table into the new schema just once? I guess could use > > bulk_insert() to do that? > > if the data is not there yet, sure it's easier to start with a blank > slate if that's what you mean > > > > > On Wednesday, June 7, 2017 at 6:14:39 PM UTC-4, mike bayer wrote: > > > > > > > > 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 > > > > > > > -- 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.
Re: Migration fails when changing to single table inheritance
Thanks for the insight Mike. I guess the best way to go about that would be to just call the raw insert sql statemen in the migration? like in https://stackoverflow.com/questions/23206562/sqlalchemy-executing-raw-sql-with-parameter-bindings/23206636#23206636 Since this app is not in production yet, would it be easier to make the current schema the initial schema and just insert the data that is in the MediaChapter table into the new schema just once? I guess could use bulk_insert() to do that? On Wednesday, June 7, 2017 at 6:14:39 PM UTC-4, mike bayer wrote: > > > > 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 > > > > -- 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.
Re: Migration fails when changing to single table inheritance
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)
Migration fails when changing to single table inheritance
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 or update on table "mediachapter" violates
foreign key constraint "fk_mediachapter_id_mediabase"
DETAIL: Key (id)=(570) is not present in table "mediabase".
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:
class MediaBase(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:
class MediaChapter(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
import datetime
import websauna.system.model.columns
from sqlalchemy.types import Text # Needed from proper creation of JSON
fields as Alembic inserts astext_type=Text() row
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
def upgrade():
# ### 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),
