Enable Dark Mode!
how-to-execute-sql-queries-odoo-14.jpg
By: Midilaj VK

How to Execute SQL Queries in Odoo 14

This blog explains how we can execute SQL Queries directly in Odoo 14. Generally we use Odoo ORM Techniques to perform SQL operations. In such cases you don't have to write queries explicitly. But in some situations, where their performance is a key consideration we have to execute SQL queries directly. This is because we have to write very complex queries that are difficult to express with Odoo ORM.
For example, we need to generate a report from a huge number of records. Their performance  is a key consideration. If we are using ORM techniq to retrieve data then it might be a time consuming process because ORM is slower than Row SQL Queries. Hence, we need to execute queries directly to speed up the process.
The environment(env) contains an attribute ‘cr’, it’s a cursor for the current database and allows you to execute SQL directly.
If you print self.env.cr.dbname it will return the current database name. By using the ‘cr’ attribute we can perform the database operations directly.
self.env.cr.execute("some_sql", param1, param2, param3)
This is the syntax for executing SQL queries explicitly. You can enter a query directly  inside the brackets, or assign a query to a variable and pass that variable here and also can optionally pass parameters.

You can either use self.env.cr or self._cr, both have the same effect
Example:
     self.env.cr.execute(“””SELECT * FROM res_partner”””)
     self.env.cr.fetchall()
     Or
     query = “””SELECT * FROM res_partner”””
   self.env.cr.execute(query)
   self.env.cr.fetchall()
This query will return all records in res.partner model.
Fetchall will give you matching records in the form of a list of tuples.

Different fetch methods are:
    1. cr.fetchall(): It will return matching record in the form of a list of tuples
    2. cr.fetchone(): same as cr.fetchall() except it return only single record
    3. cr.dictfetchall():  It will return a list of dictionary of matching records with key value pair
    4. cr.dictfetchone(): It will return only a single record in the form of dictionary

Let’s see CRUD operations
1. Create a record
self.env.cr.execute(“INSERT INTO res_partner(name) VALUES(‘ABC’)”)
It will create a new record in res.partner model with the name ‘ABC’

2. Modify an existing record
self.env.cr.execute("""UPDATE res_partner SET mobile='123' WHERE id=5""")
It will update the mobile number of the record to ‘123’ in res.partner model whose id=5

3. Delete an existing record
self.env.cr.execute("""DELETE FROM res_partner WHERE id=5""")
It will delete an existing record in res.partner model with id=5

4. Read
          self.env.cr.execute(“””SELECT * FROM res_partner”””)
         self.env.cr.fetchall()
It will fetch all the records in res.partner model

These are the basic SQL queries. Similarly we can perform any SQL operations directly without using ORM techniques. 
So there is a question that arises, if SQL queries are faster than ORM technique then why are we preferring ORM over row SQL?
If we execute SQL directly instead of default ORM methods, it will bypass the ORM and by consequence it will bypass all the rules and regulations and odoo security checkpoints. If we create a record using Row SQL, then the created user and created date will not be set on that record. If we have added access rights on a model, then it won’t be considered while executing queries directly.
It’s always better to prefer ORM utilities if you don't really need to use SQL queries. So avoid the usage of SQL queries unnecessarily.
In situations like, you need to deal with a large number of database rows and want to optimize your reports, then you can use SQL queries directly because ORM will not be that effective in this situation.


If you need any assistance in odoo, we are online, please chat with us.



0
Comments



Leave a comment

 
whatsapp
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

London

Cybrosys Limited
Alpha House,
100 Borough High Street, London,
SE1 1LB, United Kingdom

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message