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"""
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?
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.
The ORM provides a search_read method, that might improve your overall performance.
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
|Asked: 5/16/14, 5:14 PM|
|Seen: 1386 times|
|Last updated: 3/16/15, 8:10 AM|