SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql

by

There is an upsert-esque operation in SQLAlchemy:

db.session.merge()

After I found this command, I was able to perform upserts, but it is worth mentioning that this operation is slow for a bulk “upsert”.

The alternative is to get a list of the primary keys you would like to upsert, and query the database for any matching ids:

# Imagine that post1, post5, and post1000 are posts objects with ids 1, 5 and 1000 respectively
# The goal is to "upsert" these posts.
# we initialize a dict which maps id to the post object

my_new_posts = {1: post1, 5: post5, 1000: post1000} 

for each in posts.query.filter(posts.id.in_(my_new_posts.keys())).all():
    # Only merge those posts which already exist in the database
    db.session.merge(my_new_posts.pop(each.id))

# Only add those posts which did not exist in the database 
db.session.add_all(my_new_posts.values())

# Now we commit our modifications (merges) and inserts (adds) to the database!
db.session.commit()

More Related Contents:

  1. Updates to JSON field don’t persist to DB
  2. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  3. How to execute raw SQL in Flask-SQLAlchemy app
  4. How to write DataFrame to postgres table
  5. jsonify a SQLAlchemy result set in Flask [duplicate]
  6. CS50: LIKE operator, variable substitution with % expansion
  7. SQLAlchemy create_all() does not create tables
  8. SQLAlchemy ORM conversion to pandas DataFrame
  9. How to create an SQL View with SQLAlchemy?
  10. How to update SQLAlchemy row entry?
  11. Flask-SQLalchemy update a row’s information
  12. Generate sql with subquery as a column in select statement using SQLAlchemy
  13. SQLAlchemy ManyToMany secondary table with additional fields
  14. Can SQLAlchemy be used with Google Cloud SQL?
  15. Complex foreign key constraint in SQLAlchemy
  16. Flask sqlalchemy many-to-many insert data
  17. How to delete a record by id in Flask-SQLAlchemy
  18. Connection problems with SQLAlchemy and multiple processes
  19. How to upsert pandas DataFrame to PostgreSQL table?
  20. Get inserted key before commit session
  21. Querying with function on Flask-SQLAlchemy model gives BaseQuery object is not callable error
  22. SQLAlchemy multiple foreign keys in one mapped class to the same primary key
  23. flask-sqlalchemy or sqlalchemy
  24. How to set connection timeout in SQLAlchemy
  25. Flask-SQLAlchemy Constructor
  26. SQLAlchemy: filtering on values stored in nested list of the JSONB field
  27. How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
  28. Retrieve query results as dict in SQLAlchemy
  29. SQLAlchemy ordering by count on a many to many relationship
  30. flask sqlalchemy query with keyword as variable

Leave a Comment