How to do an upsert with SqlAlchemy?

by

SQLAlchemy does have a “save-or-update” behavior, which in recent versions has been built into session.add, but previously was the separate session.saveorupdate call. This is not an “upsert” but it may be good enough for your needs.

It is good that you are asking about a class with multiple unique keys; I believe this is precisely the reason there is no single correct way to do this. The primary key is also a unique key. If there were no unique constraints, only the primary key, it would be a simple enough problem: if nothing with the given ID exists, or if ID is None, create a new record; else update all other fields in the existing record with that primary key.

However, when there are additional unique constraints, there are logical issues with that simple approach. If you want to “upsert” an object, and the primary key of your object matches an existing record, but another unique column matches a different record, then what do you do? Similarly, if the primary key matches no existing record, but another unique column does match an existing record, then what? There may be a correct answer for your particular situation, but in general I would argue there is no single correct answer.

That would be the reason there is no built in “upsert” operation. The application must define what this means in each particular case.

More Related Contents:

  1. How to upsert pandas DataFrame to PostgreSQL table?
  2. How to upsert pandas DataFrame to Microsoft SQL Server table?
  3. Writing a connection string when password contains special characters
  4. Does SQLAlchemy have an equivalent of Django’s get_or_create?
  5. How to write DataFrame to postgres table
  6. Speeding up pandas.DataFrame.to_sql with fast_executemany of pyODBC
  7. CS50: LIKE operator, variable substitution with % expansion
  8. SQLAlchemy: What’s the difference between flush() and commit()?
  9. Group by & count function in sqlalchemy
  10. Debugging (displaying) SQL command sent to the db by SQLAlchemy
  11. Flask-SQLalchemy update a row’s information
  12. Efficiently updating database using SQLAlchemy ORM
  13. SQLAlchemy ORDER BY DESCENDING?
  14. SQLAlchemy ON DUPLICATE KEY UPDATE
  15. SQLAlchemy, get object not bound to a Session
  16. stored procedures with sqlAlchemy
  17. SqlAlchemy – Filtering by Relationship Attribute
  18. How to delete a record by id in Flask-SQLAlchemy
  19. SQLAlchemy printing raw SQL from create()
  20. SQLAlchemy: engine, connection and session difference
  21. SQLAlchemy: how to filter date field?
  22. method of iterating over sqlalchemy model’s defined columns?
  23. How to execute “left outer join” in SqlAlchemy
  24. How to close a SQLAlchemy session?
  25. convert selected datetime to date in sqlalchemy
  26. Using SQLAlchemy models in and out of Flask
  27. SQLAlchemy column name with space
  28. Connecting to SQL Server 2012 using sqlalchemy and pyodbc
  29. using sqlalchemy to load csv file into a database
  30. pyodbc.connect() works, but not sqlalchemy.create_engine().connect()

Leave a Comment