批量保存复杂对象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单独添加到会话中，还有其他选择吗？大容量插入会更好，但我不明白如果这样做，为什么数据不能持久化

Answer 1

^{}对于您的用例来说是太低级别的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.bulk_save_objects(showtime_lists)
session.commit()

做

session.add_all(showtime_lists)
session.commit()

Answer 2

您可以手动分配ID：

1. 在表上获取一个write lock

2. 查找现有的最高id

3. 手动生成不断增加的ID序列

您可以将数据库中的id序列增加到"reserve" a block of ids，而不是锁定表

您必须以正确的顺序插入，以避免外键冲突（如果引擎允许，则延迟约束）