views:

1119

answers:

3

Hi, does anybody know what is the equivalent to SQL "INSERT OR REPLACE" clause in SQLAlchemy and its SQL expression language?

Many thanks -- honzas

+3  A: 
Session.save_or_update(model)
M. Utku ALTINKAYA
This is not my case. I don't use session, I'm using Connection directly, so it must be done using SQL expression language and conn.execute(...)
honzas
Anyway, this is 0.4 SQLA specific, for 0.5 you have to use Session.add(model)
Jiri
+1  A: 

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.

DNS
+1  A: 

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

hadrien