multiple insert or update
Paul
already exists an update operation is performed or if not an insert is performed.
I have seen merge being recommended for this insert or update, but I wondered if
this was available as a multiple call like add_all which I could provide a list?
Is there a way to do this or should I just put the merge into a loop?
Cheers!
Paul
Michael Bayer
On Nov 1, 2011, at 7:23 AM, Paul wrote:
> What's the cleanest way to perform a multiple insert or update? ie, if the record
> already exists an update operation is performed or if not an insert is performed.
that's a funny usage of terminology - a "multiple" insert would normally be construed as meaning an INSERT that has many rows at once, which is a different issue.
As I read this I wasn't clear what you were asking for, the "MERGE" SQL construct, or just asking about ORM merge. After I wrote a whole reply here based on the SQL construct I realized you were talking about the ORM.
If you have a bunch of objects, all of which you can populate with their primary key, and you'd like some to result in INSERTS and others in UPDATE, then yes you can use Session.merge() in a loop. I would try to pre-load all the existing rows, if possible, into a temporary collection - ideally limited to those rows you know you're operating upon. That would make the operation of Session.merge() much faster as it can locate each object in the local identity map rather than emitting a SELECT for each one.
Paul
Ah thanks, I'm not entirely sure how I would pre-load the rows. Would I just use
a query to select the rows in the same session somewhere before? Do I need to
store these returned instances somewhere?
Michael Bayer
yes and yes, like:
my_instances = Session.query(SomeClass).filter(...).all()
for obj in my_list_of_new_objects:
obj = Session.merge(obj)
Session.commit()
pre-loading isn't strictly needed, it's just an option in the case you'd like to cut down on the number of SELECT statements being emitted. Watch the echoed SQL with echo=True to tailor this as needed.