批量保存复杂对象SQLAlchemy [英] Bulk saving complex objects SQLAlchemy

问题描述

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模型中?

how do we bulk save this into the show_times Model?

我尝试这样做:

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.

我的意思是除了将每个show_time分别添加到会话之外，还有其他选择吗?批量插入会更好，但我不明白为什么这样做不能使数据持久化.

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.

推荐答案

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:

警告

批量保存功能允许以较低的延迟对行进行INSERT/UPDATE，而以大多数其他工作单元功能为代价.对象管理，关系处理和SQL子句支持等功能被静默省略，而支持对记录的原始INSERT/UPDATES.

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() 将实例的集合添加到会话中.它将一次处理一个实例，但这就是您需要为高级功能(如关系处理)支付的价格.

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.

所以，而不是

session.bulk_save_objects(showtime_lists)
session.commit()

做

session.add_all(showtime_lists)
session.commit()

这篇关于批量保存复杂对象SQLAlchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！