Bulk saving complex objects SQLAlchemy

Question

association_table = Table("association_table",
                          Base.metadata,
                          Column("show_id", Integer(), ForeignKey("show_times.id"), primary_key=True),
                          Column("theater_id", Integer(), ForeignKey("theaters.id")))

association_table2 = Table("association_table2",
                           Base.metadata,
                           Column("show_id", Integer(), ForeignKey("show_times.id"), primary_key=True),
                           Column("movie_id", Integer(), ForeignKey("movies.id")))



class Movie(Base):
    __tablename__ = "movies"
    id = Column(Integer, primary_key=True)
    title = Column(String(), unique=True)
    plot = Column(String())
    duration = Column(String())
    rating = Column(String())
    trailer = Column(String())
    imdb = Column(String())
    poster = Column(String())
    summary = Column(String())

class Theater(Base):
    __tablename__ = "theaters"
    id = Column(Integer, primary_key=True)
    zip_code = Column(String())
    city = Column(String())
    state = Column(String())
    address = Column(String())
    phone_number = Column(String())


class Showtime(Base):
    __tablename__ = "show_times"
    id = Column(Integer, primary_key=True)
    date = Column(Date())
    theaterz = relationship("Theater", secondary=association_table)
    moviez = relationship("Movie", secondary=association_table2)
    showtimes = Column(String())

supposing we have movie objects:

movie_1 = Movie(title="Cap Murica",
              plot="Cap punches his way to freedom",
              duration="2 hours")

movie_2 = Movie(title="Cap Murica 22222",
              plot="Cap punches his way to freedom again",
              duration="2 hours")

and a theater object:

theater = Theater(name="Regal Cinemas",
                  zip_code="00000",
                  city="Houston",
                  state="TX")

how do we bulk save this into the show_times Model?

I've tried doing this:

movies = [movie_1, movie_2] # these movie objects are from the code snippet above

show_times = Showtime(date="5/19/2016",
                      theaterz=[theater],
                      moviez=movies)
session.add(show_times)
session.commit()

hurray the above works. but when i do it in bulk like this:

showtime_lists = [show_time1, show_time2, showtime3] # these are basically just the same show time objects as above

session.bulk_save_objects(showtime_lists)
session.commit()

it doesn't fail but the data also doesn't get persisted to the database.

I mean is there an alternative to adding each show_time to the session individually? A bulk insert would be better but I don't get why the data doesn't get persisted if done that way.

Does it fail to persist completely, or do the `ShowTime` instances themselves get persisted, but no related data. Apparently [`bulk_save_objects`](http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.bulk_save_objects) is quite a low level API and ignores a lot of stuff, like relationships. Have you had a look at [`Session.add_all`](http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.add_all)? — Ilja Everilä, May 20 '16 at 06:05
@IljaEverilä I guess I failed to mention it. but yes `Showtime` get persisted but the `relationships` get ignored — halcyonjuly7, May 20 '16 at 06:19

score 44 · Accepted Answer · answered May 20 '16 at 06:37

Session.bulk_save_objects() is too low level API for your use case, which is persisting multiple model objects and their relationships. The documentation is clear on this:

Warning

The bulk save feature allows for a lower-latency INSERT/UPDATE of rows at the expense of most other unit-of-work features. Features such as object management, relationship handling, and SQL clause support are silently omitted in favor of raw INSERT/UPDATES of records.

Please read the list of caveats at Bulk Operations before using this method, and fully test and confirm the functionality of all code developed using these systems.

You should use Session.add_all() to add a collection of instances to the session. It will handle the instances one at a time, but that is the price you have to pay for advanced features such as relationship handling.

So, instead of

session.bulk_save_objects(showtime_lists)
session.commit()

do

session.add_all(showtime_lists)
session.commit()

Well thats what I get for just scanning the docs and not thoroughly reading them. I appreciate you highlighting that part. I was even planning to do this via using the `SQLAlchemy core` but seeing as the relationships are quite complex.. well... `Session.add_all` it is — halcyonjuly7, May 20 '16 at 06:53

score -3 · Answer 2 · edited Jun 20 '20 at 09:12

You can assign ids manually:

Get a write lock on the table
Find the highest existing id
Manually generate an increasing sequence of ids

Instead of locking the table, you might be able to increment the id sequence in the database to "reserve" a block of ids.

You'll have to insert in the proper order to avoid foreign key violations (or defer the constraints if your engine allows this).

Bulk saving complex objects SQLAlchemy

2 Answers2

Linked

Related