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. On Tue, Mar 9, 2021, at 9:58 AM, [email protected] wrote: > 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? > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/f7ddef2f-9152-4b5a-a60e-bf51794f531bn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/f7ddef2f-9152-4b5a-a60e-bf51794f531bn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/766fc0ee-2555-47f4-9720-5dbceeee06e4%40www.fastmail.com.
