python - 批量保存复杂对象 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())

假设我们有电影对象:

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")

和一个剧院对象:

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

我们如何将其批量保存到 show_times 模型中？

我试过这样做:

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()

欢呼上面的作品。但是当我像这样批量进行时:

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()

它不会失败，但数据也不会持久保存到数据库中。

我的意思是，是否可以将每个 show_time 单独添加到 session 中？批量插入会更好，但我不明白为什么如果这样做的话数据不会持久化。

Answer 1

Session.bulk_save_objects()对于您的用例而言，API 级别太低，它会保留多个模型对象及其关系。文档对此很清楚:

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.

你应该使用 Session.add_all()将实例集合添加到 session 中。它将一次处理一个实例，但这是您为关系处理等高级功能必须付出的代价。

所以，而不是

session.bulk_save_objects(showtime_lists)
session.commit()

做

session.add_all(showtime_lists)
session.commit()