On 06/07/2017 10:15 PM, Michael wrote:
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?
that kind of task is known in a general sense as extract/transform/load
(ETL), and is a whole class of problem handled in many different ways
(but most normally, by just writing code). This falls under the realm
of "data migration" which is not automated in Alembic, although you can
write code in your migration files that does data migration as well.
If you have a real SQL dumpfile, easiest way is to load that into the
schema it corresponds towards, then write ETL to move that data from old
schema into new. One way ETL might happen is if you generated a JSON
file from your old database that can be consumed into the new one, for
example.
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
<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
<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
<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.
