Odoo uses ORM techniques that are easy to use and from the developer's point of view, the ORM method is a quick one to use but from the user side, it may be a slow process. This will affect the user experience. But the SQL queries are faster than the ORM. So it is better to use/execute SQL queries to speed up the process.
In this blog, let’s discuss how to execute SQL queries in Odoo 15.
To execute SQL queries we have an attribute ‘cr’ in the environment(env). ‘cr’ is the cursor for the current database to execute queries directly.
Have a look at the below example of how to insert data into a res.company model using SQL query.
query = """ INSERT INTO res_company (name, email)
VALUES (‘Demo company, ‘demo@gmail.com')"""
self.env.cr.execute(query)
We can use self._cr instead of self.env.cr . Both terms have the same result.
Let’s check another example for selecting all the data from a model.
query = “””SELECT * FROM res_company”””
self.env.cr.execute(query)
By executing this query we get all the data from the res.company model.
Also with the execution of the query, we can use different fetching methods. Mainly there are 4 fetching methods. They are :
1. cr.fetchall()
2. cr.fetchone()
3. cr.dictfetchall()
4. cr.dictfetchone()
Fetchall returns matching records in the form of a list of tuples. Fetchone is also the same as fetchall, but it should return a single matching record.
Dictfetchall is used to return a list of dictionaries of matching records, which have key-value pairs. Dictfetchone is also the same as dictfetchall, but it must return a single dictionary.
Next, we have a look at the operations which are able to do on data. The main operations which can be done are create, read, update and delete.
We can create new data through SQL queries and we can make updates on existing data and we can delete the data which is not necessary and we can simply read data.
Let’s have a look,
self.env.cr.execute(“INSERT INTO res_company(name) VALUES(‘demo company)”)
This is an example of inserting new data into the res.company model. ‘name’ is the corresponding field name where we are going to insert data and ‘demo company’ is the new data that we are inserting to that field.
self.env.cr.execute("""UPDATE res_company SET phone=’23’ WHERE id=50""")
This is an example of modifying existing data. Here updating the contact number of the company whose ID is 50 on the res.company model.
self.env.cr.execute("""DELETE FROM res_company WHERE id=5""")
This is an example of deleting an existing record from res.company model
self.env.cr.execute(“””SELECT * FROM res_company”””)
self.env.cr.fetchall()
This is an example of fetching all the data from the res.company model.