Odoo : SQL Query Tips

In this article we will show some useful tips that will help you work with SQL Queries in python/Odoo.

How to execute queries in Odoo 10 ?

you should use the self.env.cr to run a sql query

cr = self.env.cr
cr.execute("SELECT a, b, c FROM [your_table] WHERE [condition]")
cr.fetchall()

It’s highly recommended to use the double quotes ” ” instead of single quotes ‘ ‘

What’s the query can returns ?

cr.dictfetchall() : will give you all the matching records in the form of the list of dictionary containing key, value [{'': ''}, {'': ''}, ...].
cr.dictfetchone : works same as cr.dictfetchall() except it returns only a single record {'': ''}.
cr.fetchall() : will give you all the matching records in the form of the list of tuple [(''), (''), ...].
cr.fetchone() : works same way as cr.fetchall() except it returns only single record ('').

SQL Queries with variables

You can execute Queries with variables using %s like:

cr.execute("SELECT a, b, c FROM [your_table] AS a WHERE a.id = %s AND a.field = %s", (first_value, second_value))

Hope it was useful for you
Good luck !!

4 thoughts on “Odoo : SQL Query Tips

  1. asmaa samir says:

    I wrote this code on odoo 11 to retrieve vin_sn and driver_name based on selected model_id
    @api.onchange(“model_id”)
    def onchange_lines(self):
    if self.model_id:
    self.env.cr.execute(“select vin_sn , driver_name from fleet_vehicle AS a where a.model_id=%s”, (self.model_id))
    self.vin_sn= self.env.cr.fetchall()[0]

    but it return this error:

    SQL query parameters should be a tuple, list or dict; got fleet.vehicle.model(15,)
    what is problem

Leave a Reply

Your email address will not be published. Required fields are marked *