Does anybody know what the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language is?
Add a comment
|
4 Answers
What about Session.merge?
Session.merge(instance, load=True, **kw)
Copy the state an instance onto the persistent instance with the same identifier.
If there is no persistent instance currently associated with the session, it will be loaded. Return the persistent instance. If the given instance is unsaved, save a copy of and return it as a newly persistent instance. The given instance does not become associated with the session. This operation cascades to associated instances if the association is mapped with cascade="merge".
from http://www.sqlalchemy.org/docs/reference/orm/sessions.html
1 Comment
bfontaine
I downvoted because this works only if you’re inserting something that already have a primary key; it does nothing for new rows without primary key but with values that already exist in
unique indexes.I don't think (correct me if I'm wrong) INSERT OR REPLACE is in any of the SQL standards; it's an SQLite-specific thing. There is MERGE, but that isn't supported by all dialects either. So it's not available in SQLAlchemy's general dialect.
The cleanest solution is to use Session, as suggested by M. Utku. You could also use SAVEPOINTs to save, try: an insert, except IntegrityError: then rollback and do an update instead. A third solution is to write your INSERT with an OUTER JOIN and a WHERE clause that filters on the rows with nulls.
Comments
Session.save_or_update(model)
You can use OR REPLACE as a so-called prefix in your SQLAlchemy Insert -- the documentation for how to place OR REPLACE between INSERT and INTO in your SQL statement is here
1 Comment
Stephen Emslie
This is similar to this answer.