如果 SQLAlchemy 不存在，则插入对象的最快方法

2021-12-30 00:00:00 python sqlalchemy mysql

所以我对 SQLAlchemy 很陌生.

So I'm quite new to SQLAlchemy.

我有一个模型 Showing，表中有大约 10,000 行.这是课程:

I have a model Showing which has about 10,000 rows in the table. Here is the class: 

class Showing(Base):
    __tablename__   = "showings"

    id              = Column(Integer, primary_key=True)
    time            = Column(DateTime)
    link            = Column(String)
    film_id         = Column(Integer, ForeignKey('films.id'))
    cinema_id       = Column(Integer, ForeignKey('cinemas.id'))

    def __eq__(self, other):
        if self.time == other.time and self.cinema == other.cinema and self.film == other.film:
            return True
        else:
            return False

如果新节目不存在，谁能给我一些关于插入新节目的最快方法的指导.我认为它稍微复杂一些，因为只有在时间、电影和电影在放映中是独一无二的，放映才是独一无二的.

Could anyone give me some guidance on the fastest way to insert a new showing if it doesn't exist already. I think it is slightly more complicated because a showing is only unique if the time, cinmea, and film are unique on a showing.

我目前有这个代码:

def AddShowings(self, showing_times, cinema, film):
    all_showings = self.session.query(Showing).options(joinedload(Showing.cinema), joinedload(Showing.film)).all()
    for showing_time in showing_times:
        tmp_showing = Showing(time=showing_time[0], film=film, cinema=cinema, link=showing_time[1])
        if tmp_showing not in all_showings:
            self.session.add(tmp_showing)
            self.session.commit()
            all_showings.append(tmp_showing)

它有效，但似乎很慢.非常感谢任何帮助.

which works, but seems to be very slow. Any help is much appreciated.

推荐答案

如果任何此类对象基于列的组合是唯一的，则需要将它们标记为复合主键.将 primary_key=True 关键字参数添加到这些列中的每一列，完全删除您的 id 列:

If any such object is unique based on a combination of columns, you need to mark these as a composite primary key. Add the primary_key=True keyword parameter to each of these columns, dropping your id column altogether:

class Showing(Base):
    __tablename__   = "showings"

    time            = Column(DateTime, primary_key=True)
    link            = Column(String)
    film_id         = Column(Integer, ForeignKey('films.id'), primary_key=True)
    cinema_id       = Column(Integer, ForeignKey('cinemas.id'), primary_key=True)

这样您的数据库可以更有效地处理这些行(不需要递增列)，SQLAlchemy 现在会自动知道 Showing 的两个实例是否相同.

That way your database can handle these rows more efficiently (no need for an incrementing column), and SQLAlchemy now automatically knows if two instances of Showing are the same thing.

我相信然后您可以将新的Showing合并回会话:

I believe you can then just merge your new Showing back into the session:

def AddShowings(self, showing_times, cinema, film):
    for showing_time in showing_times:
        self.session.merge(
            Showing(time=showing_time[0], link=showing_time[1],
                    film=film, cinema=cinema)
        )

相关文章