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 the ORM technique 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.
Fetch all 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 a 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 the 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. To 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.