Odoo Help


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.


How can i execute a SQL statement on module update and installation?

on 3/14/13, 10:25 AM 17,390 views

How can i execute a SQL statement after module installation and a different sql statement after every modul update?


Daniel Reis

--Daniel Reis--
| 6 7 9
Lisbon, Portugal
--Daniel Reis--

Author of the "Odoo Development Essentials" book.

Applications Manager at Securitas Portugal

Github: https://github.com/dreispt

Twitter; @reis_pt

Daniel Reis
On 3/15/13, 6:25 AM

You can execute Model's method upon module's install or upgrade. Here's an example:

First, add the method that will do the initialization actions. For example:

class crm_routing_users(osv.osv):
"""For each Department and Role, which user is the responsible"""
_name = "crm.routing.users"

def _migrate_from_project_issue_profiling(self, cr, uid, ids=None, context=None):
"""Migrate from project.issue.profiling. since this module can completely replace it."""
if ids is not None:
raise NotImplementedError("Ids is just there by convention! Please don't use it.")
cr.execute("select exists(select * from information_schema.tables " "where table_name=project_issue_profiling)")
if cr.fetchone()[0]:
cr.execute(""" INSERT INTO crm_routing_users ( create_uid, create_date, write_date, write_uid, notes, user_id, section_id, department_id) SELECT create_uid, create_date, write_date, write_uid, notes, user_id, section_id, department_id FROM project_issue_profiling""")
_logger.info("Successful data copy from project.issue.profiling to crm.routing.users")
return True

Next, declare the call in an XML data file:

<!-- Migration for "project_service_profiling". Can be skipped if that module is not in use. -->
<function model="crm.routing.users" name="_migrate_from_project_issue_profiling"/>


Finally, add the XML data file to tour _openerp_.py manifest, in the data (v7) section. If you want it to run only on first install, add noupdate="1" to the data tag.

Are you sure there could be an init section in module manifest? Please check my comment on GEM's answer below. I think there is not init section and there is no difference between xml declaration using init_xml and update_xml attributes. At least in v7.0. Using noupdate="1" on data tag will run function tags only at module install.

Mohammad Alhashash
on 3/17/13, 1:20 AM

Cyril Gaspard (GEM)

--Cyril Gaspard (GEM)--
| 5 6 8
Paris, France
--Cyril Gaspard (GEM)--

Forum contributor since 2008

Cyril Gaspard (GEM)
On 3/14/13, 5:27 PM


for the answer just create a file.sql with your requests inside, exemple :

DELETE FROM ir_ui_view_sc WHERE user_id = 1 and name = 'Clients';

add in your module_name/__openerp__.py :

in section

'update_xml': ["file.sql",],

for v7 :

'data': ["file.sql",],

use insert , update ... Bye

Regarding .xml and .sql files, there is no difference between module attributes init_xml, update_xml and data. The file will always be updated/executed on any module installation or update. Check the load_module_graph() function in openerp/modules/loading.py

Mohammad Alhashash
on 3/17/13, 1:01 AM

Mohammad Alhashash

--Mohammad Alhashash--
| 5 5 8
Giza, Egypt
--Mohammad Alhashash--


Mohammad Alhashash
On 3/17/13, 2:49 AM

You can create a method init(self, cr) in your model and it will be called upon module installation or upgrade.

You can check for update or install mode by checking the the existence of data or structures that should have been created in initialization or you can just use common sql or replace mechanism to use the same sql for both states.

Most reports use this method to create required views. Check this example from addons/account/report/account_report:

class report_account_receivable(osv.osv):
    _name = "report.account.receivable"
    _description = "Receivable accounts"
    _auto = False
    _columns = {
        'name': fields.char('Week of Year', size=7, readonly=True),
        'type': fields.selection(_code_get, 'Account Type', required=True),
        'balance':fields.float('Balance', readonly=True),
        'debit':fields.float('Debit', readonly=True),
        'credit':fields.float('Credit', readonly=True),
    _order = 'name desc'

    def init(self, cr):
        tools.drop_view_if_exists(cr, 'report_account_receivable')
            create or replace view report_account_receivable as (
                    min(l.id) as id,
                    to_char(date,'YYYY:IW') as name,
                    sum(l.debit-l.credit) as balance,
                    sum(l.debit) as debit,
                    sum(l.credit) as credit,
                    account_move_line l
                left join
                    account_account a on (l.account_id=a.id)
                    l.state <> 'draft'
                group by
                    to_char(date,'YYYY:IW'), a.type

I have just to create report by using sql query (the same with above). But I have the problem: If I execute the query by postgreSQL maestro, there are 600 records. By postgreSQL Maestro, I open the view there are also 600 records. But the tree view in Openerp V7 there are only 19 records. Anyone help me?

on 8/29/13, 5:59 AM

As long as we write less SQL sentences, the ORM can make more optimizations for us in the future and the maintenance will be easier.

There are cases where you have made strong changes to your models already used in production and the ORM can't make the automatic inference of the sql statements needed to give the database a logical state corresponding to your new code (ex: fix your table structure, move data between columns, calculate quickly default values for new not null columns using sql expressions, etc..).If your case is one of those data maintenance problems, you can use the migration scripts as specified in the MigrationManager descriptions: http://bit.ly/1UqiO8h.This mechanism is used extensively inside openupgrade: http://bit.ly/1QDc40R  You can find a lot of samples there.

In your case, just create a migration folder with a -pre or -post update script:

`-- migrations
|-- 8.0
| |-- pre-migration.py

In the  pre-migration.py script you can check the version and put both queries. The first two digits passed to the script is always the odoo version the others come from your module version ( __openerp__.py of your module)

You can install the module specifying one version 1.1 and then change the module version to 1.2, 1.3, etc... in

def migrate(cr, version):
if version == '':
  cr.execute("""UPDATE ... FROM account_analytic_journal WHERE .... """)

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)


Asked: 3/14/13, 10:25 AM
Seen: 17390 times
Last updated: 8/9/16, 9:50 AM