can you insert data for a model with a many to many relationship using alembic?

861 views
Skip to first unread message

maqui...@gmail.com

unread,
Mar 9, 2021, 9:58:36 AM3/9/21
to sqlalchemy
I'm trying to create an alembic bulk insert statement to add a row into a model that has a relationship (many-to-many) with another table but don't know if this is possible or if so what syntax to use.

In my current alembic file I do this in two or more steps:
1.) I add the rows to the table represented by the model
2.) I add the rows to the mixer table for the model and its related model/table

like so:
g.session.bulk_insert_mappings(
    CvConfiguration,
    [
        {
            "controlled_vocabulary_type_id": controlled_vocabulary_type(
                "cv_vessel_type"
            ),
            "default_cv_id": cv_vessel_type("well_plate"),
        },
    ],
    return_defaults=True,
)

g.session.flush()

mix_cv_organization_cv_configuration_stmt = mix_cv_organization_cv_configuration.insert().values(
    [
        {
            "cv_organization_id": cv_organization("biologics_generation_group"),
            "cv_configuration_id": cv_configuration("cv_vessel_type", "well_plate"),
        },
    ],
)
g.session.execute(mix_cv_organization_cv_configuration_stmt)

I'd really like to combine the relationship into the bulk_insert_mapping if possible, so if the relationship on the SqlAlchemy model is called "used_by_cv_organizations" my insert looks something like this, with the foreign key objects in a list or something.

g.session.bulk_insert_mappings(
    CvConfiguration,
    [
        {
            "controlled_vocabulary_type_id": controlled_vocabulary_type(
                "cv_vessel_type"
            ),
            "default_cv_id": cv_vessel_type("well_plate"),
            "used_by_cv_organizations": [
                cv_organization("biologics_generation_group")
            ],
        },
    ],
    return_defaults=True,
)

g.session.flush()

Is this possible? Does anyone know how to do it?


Mike Bayer

unread,
Mar 9, 2021, 11:25:16 AM3/9/21
to noreply-spamdigest via sqlalchemy

bulk_insert_mappings doesn't handle relationships in any case, if you wanted relationships to be persisted without doing the INSERT yourself you would need to use regular ORM Session unit of work patterns.

In Alembic, you would need to use ORM mappings inside of your alembic scripts, which is perfectly acceptable, the only issue with that is that if you import these models from your application, they might not match the state of the database for when the migration is running.    so it might be better to create an ad-hoc mapping in your migration file.   Depending on how you are getting those Table objects, you can assign them to a new declarative model using __table__ (See https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#orm-imperative-table-configuration )   and you can make a new Session for the local transaction using Session(op.get_bind()).

if you wanted bulk_insert_mappings to apply directly to your manytomany table then you can map a class to that table and use the method, but I dont see much advantage to this vs. using plain table.insert(), in both cases it's a command with a list of dictionaries.


if you are looking to use session.flush(), that implies you should just use traditional unit of work patterns and you wouldn't be using bulk_insert_mappings, which is a special case method that IMO is not actually very useful compared to other APIs that already exist.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Mark Aquino

unread,
Mar 10, 2021, 10:23:10 PM3/10/21
to sqlal...@googlegroups.com
Thanks. I ended up using the models in the alembic file instead of bulk insert mappings. Your point is well taken about the caveats of the model being out of date but in this use case (just seeding the database) there’s no risk. 

Mark Aquino
From: sqlal...@googlegroups.com <sqlal...@googlegroups.com> on behalf of Mike Bayer <mik...@zzzcomputing.com>
Sent: Tuesday, March 9, 2021 11:24:48 AM
To: noreply-spamdigest via sqlalchemy <sqlal...@googlegroups.com>
Subject: Re: [sqlalchemy] can you insert data for a model with a many to many relationship using alembic?
 
Reply all
Reply to author
Forward
0 new messages