On Nov 1, 2011, at 11:25 AM, Paul wrote:
> Michael Bayer <mike_mp <at> zzzcomputing.com> writes:
>
>>
>>
>> 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.
>>
>
> 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?
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
