Odoo already has an ORM (object relational mapping) which is quite easy to use. By using an ORM we can manipulate data in the database easily. Unfortunately ORM in all frameworks, including odoo, has a weakness, which is slow.

Using ORM to manipulate large data in one process, for example when creating a report, sometimes leads us to a dilemma. If we use ORM, the work can be done quickly from the programmer’s point of view, but there is a possibility that on the user side the process may take a relatively longer time. Of course this will affect the user experience.

So there is no harm if we try to use Raw Query on a process that will be slow if we use ORM, like when we create a report.

Raw Query which I mean here are SQL commands, such as the insert, update, delete and select commands which are commonly used for database manipulation. For your information, Raw Query ignore odoo permissions. For example, in an insert process, if we use ORM odoo will check whether the user has create permissions or not. If user don’t have the access rights, a warning or error message will appear, whereas if you use Raw Query, these permissions will be ignored, so use them wisely. This is a tutorial about how to execute raw queries on odoo.

To execute raw queries, we just need to call the execute method in the Cursor class. The Cursor class or object itself can be accessed from all odoo models with code like below.

self.env.cr

Or if you want to access it from the controller, you can use the code like below.

from odoo.http import request

request.env.cr

This is the declaration of the execute method, which is written on line 228 at odoo source code .

@check
def execute(self, query, params=None, log_exceptions=None):

You can see that the execute method has 3 parameters. The first parameter is the SQL Query that we want to run, this parameter is mandatory.

The second parameter is optional. This parameter must be a tuple, list or dictionary. This parameter is used to insert dynamic data in the first parameter to prevent SQL injection.

The third parameter is also optional. Used as a sign that we want to log an error message or not if our query causes an error.

This is an example of how to insert data into the res.partner model using a raw query. Remember the period (.) in the model name in the python file will be translated into an underscore (_) in the database table.

query = """
    INSERT INTO res_partner (name, email)
    VALUES ('Ngasturi', 'ngasturi@gmail.com')
"""

self.env.cr.execute(query)

When using dynamic data, it is not recommended to combine query and value using the plus (+) operator like below.

def insert_new_partner(self, name, email):
    query = """
        INSERT INTO res_partner (name, email)
        VALUES ('""" + name + """', '""" + email + """')"""

    self.env.cr.execute(query)

Or use a string formatting like below

def insert_new_partner(self, name, email):
    query = """
        INSERT INTO res_partner (name, email)
        VALUES ('%s', '%s')
    """ % (name, email)

    self.env.cr.execute(query)

The two codes above are still work, but they are not recommended because they are prone to sql injection. The recommended way to use dynamic data is to insert that data as the second parameter in the execute method. But it must be wrapped as a tuple, list or dictionary, like in the code below.

def insert_new_partner(self, name, email):
    query = """
        INSERT INTO res_partner (name, email)
        VALUES (%s, %s)
    """
    params = (name, email)

    self.env.cr.execute(query, params)

When we use the second parameter to insert dynamic value or data, we don’t need to use single quote or double quote to wrap the text in first parameter, so it’s easier. In the code above we use the tuple as the second parameter, there is no significant difference when we use the tuple or list as the second parameter, here is an example if we use a list.

def insert_new_partner(self, name, email):
    query = """
        INSERT INTO res_partner (name, email)
        VALUES (%s, %s)
    """
    # the only difference is here
    params = [name, email]

    self.env.cr.execute(query, params)

The significant difference is when we use the dictionary.

def insert_new_partner(self, data):
    """
        let's just say the value of the data parameter is like this

        data = {'name': 'Ngasturi', 'email': 'ngasturi@gmail.com'}
    """

    query = """
        INSERT INTO res_partner (name, email)
        VALUES (%(name)s, %(email)s)
    """

    self.env.cr.execute(query, data)

Next, How if we want to retrieve data from the database using the select command ?

The good news is to run all raw queries like insert, delete, update and select commands, we only need one method, namely the execute method above. If the raw query that we run returns a value, then we need another method from the Cursor class to capture that value. This is an example of how to run the insert command that returns an inserted id and how to capture that id’s value.

def insert_new_partner(self, data):
    """
        let's just say the value of the data parameter is like this

        data = {'name': 'Ngasturi', 'email': 'ngasturi@gmail.com'}
    """

    query = """
        INSERT INTO res_partner (name, email)
        VALUES (%(name)s, %(email)s) returning id
    """

    self.env.cr.execute(query, data)
    result = self.env.cr.dictfetchall()
    print(result)

The result will look like this.

[{'id': 51}]

There are 3 methods of the Cursor class that we can use to capture the value from the raw query. The first method is dictfetchone. This is an example how to use it.

query = """
    SELECT 
        partner_id, 
        sum(so_count) AS so_count, 
        sum(po_count) AS po_count
    FROM (
        SELECT 
            partner_id, 
            count(id) AS so_count,
            0 AS po_count
        FROM sale_order
        GROUP BY partner_id
        UNION ALL
        SELECT 
            partner_id, 
            0 AS so_count,
            count(id) AS po_count
        FROM purchase_order
        GROUP BY partner_id
    ) AS sub_query
    GROUP BY partner_id
"""

self.env.cr.execute(query)
result = self.env.cr.dictfetchone()
print(result)

No matter how much of the results from the raw query, the dictfetchone will display one row of data only. The result will be like this.

{'partner_id': 11, 'so_count': 13.0, 'po_count': 1.0}

The next method is dictfetchmany, with this method we can set how many rows of data will be displayed, for example 3 lines, like in the code below.

query = """
    SELECT 
        partner_id, 
        sum(so_count) AS so_count, 
        sum(po_count) AS po_count
    FROM (
        SELECT 
            partner_id, 
            count(id) AS so_count,
            0 AS po_count
        FROM sale_order
        GROUP BY partner_id
        UNION ALL
        SELECT 
            partner_id, 
            0 AS so_count,
            count(id) AS po_count
        FROM purchase_order
        GROUP BY partner_id
    ) AS sub_query
    GROUP BY partner_id
"""

self.env.cr.execute(query)
result = self.env.cr.dictfetchmany(3)
print(result)

What make dictfetchmany different from dictfetchone is that the results will be wrapped in a list, like this.

[
	{'partner_id': 11, 'so_count': 13.0, 'po_count': 1.0}, 
	{'partner_id': 9, 'so_count': 0.0, 'po_count': 2.0}, 
	{'partner_id': 15, 'so_count': 1.0, 'po_count': 0.0}
]

The last method is the dictfetchall. Like his name, dictfetchall will display all query results.

query = """
    SELECT 
        partner_id, 
        sum(so_count) AS so_count, 
        sum(po_count) AS po_count
    FROM (
        SELECT 
            partner_id, 
            count(id) AS so_count,
            0 AS po_count
        FROM sale_order
        GROUP BY partner_id
        UNION ALL
        SELECT 
            partner_id, 
            0 AS so_count,
            count(id) AS po_count
        FROM purchase_order
        GROUP BY partner_id
    ) AS sub_query
    GROUP BY partner_id
"""

self.env.cr.execute(query)
result = self.env.cr.dictfetchall()
print(result)