Blog

Odoo ORM saves our time and speeds up the development process and doesn't need any SQL knowledge to use an ORM. But it has a tendency to be slow and cannot compete with complex queries. So in this blog, we discuss how to use SQL queries in Odoo. For example, In one case, we have to create a report from a large amount of data. Their performance is a key consideration. If we are using the ORM, using this technique would require a long time to retrieve data because ORM is slower than Row SQL Queries. Because of this, a query needs to be run. To execute SQL queries, we need a cursor ‘cr’ attribute which is used to point the current database in the environment (env). This sends this query to PostgreSQL and executes it.

How to Execute Query: Examples

For example, for query, creating a record in the table res_partner

query = """ INSERT INTO res_partner (name, email) VALUES ('Demo partner', 'demo@gmail.com') """
self.env.cr.execute(query)

Let’s look for another example fetching all the data from a model

query = """Select * from res_users"""zz
self.env.cr.execute(query)

After executing this query, we get all the data from the model res_users

Fetching Methods

Let’s have a look at the different fetching methods:

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

Output of the Query

In your given query, the output would be:

• The end result of cr.dictfetchall () is [['reg_no': 123], ['reg_no': 543]].
• If you run cr.dictfetchone () the result is {'reg_no': 123}.
• When you run cr.fetchall (), the result is ' [ (123), (543)]'.
• CR.fetchone () returns ' (123)'.

CRUD Operations

Let’s have a look at the CRUD (Create operations, Read operations, Update operations, Delete operations)

• Create - self.env.cr.execute(“INSERT INTO res_company (name) VALUES (‘demo company)”)
• Read - self.env.cr.execute(“””SELECT * FROM res_company”””) self.env.cr.fetchall()
• Update - self.env.cr.execute("""UPDATE res_company SET phone=’23’ WHERE id=50""")
• Delete - self.env.cr.execute("""DELETE FROM res_company WHERE id=5""")

Request Your Free Quote