Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

0

Advanced orm, sql insert through select, how to?

By
Andrea Dalseno
on 5/16/14, 5:14 PM 1,384 views

Hi, in my application that I'm trying to port in Openerp, I use a sql insert on a table like this:
INSERT INTO target_table (list of fields) SELECT field_list and other values FROM source_table WHERE complex_conditions_with_subselect_from_different_table ORDER BY x,y,z

How to obtain the same behavoiur in Openerp? I guess I should try to use the orm, instead of pure SQL.
I have three different models: the one in which I will operate, let's call it shpmnt, the source, let's call it cust, and the target, let's call it shpmnt_log (actually I have one more model for the subquery, but we can ignore it by now).
I guess I should do something like this (without going into details):
"""Define the cursors"""
source_cr = self.pool.get('cust.cust')
target_cr = self.pool.get('shpmnt.log')
"""Find the needed elements"""
src_ids = source_cr.search(cr,user,search_domain)

""" Browse the result, iterate through elements and create the record to save"""

my_objs = source_cr.browse(cr,uid, src_ids)

for cur_obj in my_objs:
"""Create val to insert using filed from cur_obj and other values"""
    my_vals = ...
"""Insert into the target table"""
    target_cr.create(cr,uid,my_vals)

Now my questions are:
1) Am I correct? Is this approach right?
2) Doing this way, since the selection is huge, I will make a lot of writes: is there a more effective method?  Mybe using export_data and then load? Something like:

my_data = source_cr.export_data(cr, uid, src_ids, list_of_fileds) 

target_cr.load(cr, uid, list_of_fields, my_data)

Will it work? Is it more efficent or is it the same?

Since the whole process should be inside a single transaction, how can I do? Is it possible?
Shall I put the code inside one method?
If so, doing it on something like 20.000 rows, are there chances to get out of memory?

Thanks

 

    
    

A lot of views, but no answer yet!?!

Andrea Dalseno
on 5/28/14, 6:21 AM

Thank you René, can you tell me if the process will be inside a single transaction, so that it succeeds or fails as a whole?

Andrea Dalseno
on 5/28/14, 11:30 AM
1

René Schuster

--René Schuster--
1560
| 5 5 8
Weinheim, Germany
--René Schuster--

Challenge Everything!

René Schuster
On 5/28/14, 7:02 AM

1) Your approach will work.

2) Your suggested export_data and load methods are more or less read and create. The ORM might be smart and the performance is quite the same as in a direct SQL insert.

I can not state whether the memory will lack.

Some remark:

The ORM provides a search_read method, that might improve your overall performance.

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

2 follower(s)

Stats

Asked: 5/16/14, 5:14 PM
Seen: 1384 times
Last updated: 3/16/15, 8:10 AM