如果SQLAlchemy不存在该对象的最快方法 [英] Fastest way to insert object if it doesn't exist with SQLAlchemy

问题描述

所以我对SQLAlchemy还是很陌生.

So I'm quite new to SQLAlchemy.

我有一个显示模型，该模型在表中大约有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)
        )

这篇关于如果SQLAlchemy不存在该对象的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！