CODEWITHSUNDEEP
pythonmysqlsqlalchemy
ig-melnyk
ig-melnyk

Reputation: 2879

Effective batch "update-or-insert" in SqlAlchemy

There exists a table Users and in my code I have a big list of User objects. To insert them I can use : 

session.add_all(user_list)
session.commit()

The problem is that there can be several duplicates which I want to update but the database wont allow to insert duplicate entries. For sure, I can iterate over user_list and try to insert user in the database and if it fails - update it : 

for u in users:
  q = session.query(T).filter(T.fullname==u.fullname).first()
  if q:
    session.query(T).filter_by(index=q.index).update({column: getattr(u,column) for column in Users.__table__.columns.keys() if column!='id'})
    session.commit()
  else:
    session.add(u)
    session.commit()

but I find this solution quiet ineffective : first, I am making several requests to retrieve object q, and instead of batch inserting of new items I insert them one per one. I wonder if there exists a better solution for this task.

UPD better version:

 for u in users:
    q = session.query(T).filter(Users.fullname==u.fullname).first()
    if q:
        for column in Users.__table__.columns.keys():
            if not column=='index':
                 setattr(q,column,getattr(u,column))
                 session.add(q)
    else:
        session.add(u)
session.commit()

Upvotes: 1

Views: 1601

Answers (1)

MOCKBA
MOCKBA

Reputation: 1790

a better solution would be to use

  INSERT ... ON DUPLICATE KEY UPDATE ...

bulk MySQL construct (I assume you're using MySQL because your post is tagged with 'mysql'). This way you're both inserting new entries and updating existing ones in one statement / transaction, see http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

It's not ideal if you have multiple unique indexes and, depending on your schema, you'll have to fill in all NOT NULL values (hence issuing one bulk SELECT before calling it), but it's definitely the most efficient option and we use it a lot. The bulk version will look something like (let's assume name is a unique key):

  INSERT INTO User (name, phone, ...) VALUES
    ('ksmith', '111-11-11', ...),
    ('jford', '222-22,22', ...),
    ...,
  ON DUPLICATE KEY UPDATE
    phone = VALUES(phone),
    ... ;

Unfortunately, INSERT ... ON DUPLICATE KEY UPDATE ... is not supported natively by SQLa so you'll have to implement a little helper function which will build the query for you.

Upvotes: 1

Related Questions