How To Execute SQL Queries in Odoo

Abid Patel
01-Nov-2024 Updated : 01-Nov-2024

Learn to execute SQL queries in Odoo for custom operations, batch updates, and optimized data handling using self.env.cr. Tips, examples, and best practices.

How To Execute SQL Queries in Odoo

To execute direct SQL queries in Odoo is a robust method. It is an effective way to interact with database. This is most apparent with complex operations. Also with optimizations beyond standard ORM capabilities. However it is crucial to grasp how and when to employ SQL in Odoo. The goal is to guarantee system integrity and performance.

Why Use SQL Queries in Odoo?

Odoo's ORM is proficient at handling database interactions. Even then there are scenarios where SQL queries offer more flexibility. More efficiency. More control. Instances of these include:

  • ▹ You need to perform batch updates. These updates span large datasets.
  • ▹ Access to complex joins is needed. Complex joins not feasible with ORM
  • ▹ Optimization of read or write operations is required. Operations that need fine-grained SQL control.

Basic Usage: Executing SQL in Odoo

Odoo gives permission for the execution of SQL. It occurs with the self.env.cr cursor object. Cursor is integral to self.env context. This the PostgreSQL database has direct interaction.

Example Code: Executing an SQL Query in Odoo

This code example shows how to execute an SQL query in Odoo. This query we can use to retrieve. Update. Delete data.

python

from odoo import models, api

class CustomSQLExample(models.Model):
    _name = 'custom.sql.example'
    _description = 'Custom SQL Example Model'

    @api.model
    def execute_custom_query(self):
        # Initialize the cursor
        cr = self.env.cr

        # Example: Select query
        cr.execute("SELECT id, name FROM res_partner WHERE customer_rank > %s", (0,))
        customers = cr.fetchall()  # Fetch all results
        for customer in customers:
            _logger.info("Customer ID: %s, Name: %s", customer[0], customer[1])

        # Example: Update query
        cr.execute("UPDATE res_partner SET active = FALSE WHERE id = %s", (customer[0],))
        self.env.cr.commit()  # Commit to save changes

        # Example: Delete query
        cr.execute("DELETE FROM res_partner WHERE active = FALSE AND customer_rank = 0")
        self.env.cr.commit()  # Commit to save changes

        return True

Explanation of the Code

  • ▹ Cursor Initialization: cr = self.env.cr initializes cursor object. The gateway to run SQL commands.
  • ▹ Select Query: Use cr.execute() to run SELECT query. Then use cr.fetchall() to retrieve results. Here we’re fetching all customers with customer_rank greater than 0.
  • ▹ Update Query: SQL is used to deactivate customers based on specific conditions. After running update, use self.env.cr.commit(). Apply changes to database.
  • ▹ Delete Query: This deletes specific records based on conditions. Keep in mind to commit changes again to ensure deletion is applied.

Points to Keep in Mind

  • ▹ Data Integrity: Always cautious with SQL operations. Prevent data loss or integrity problems. Commit Changes: Use self.env.cr.commit() after write, update, delete queries. Ensure changes are saved.
  • ▹ Testing: Be cautious with SQL queries. Test them in a development environment. Only after apply in production. It is not advisable directly use SQL in Odoo. Odoo’s ORM is a secure and optimized way to interact with database.

Consider SQL as extra tool. Don't see it primary solution. Avoid use of direct SQL for operations as ORM can efficiently handle them. It offers more maintainable, upgrade-friendly code.

Conclusion

Operating SQL queries in Odoo is a practical aptitude. It is for developers who want to orchestrate data effectively. With careful utilization of SQL for particular responsibilities and an understanding of when it's compulsory one can enhance the elasticity of Odoo. One can also promote top-notch performance and robustness. All without compromising any of these.

Make a Comment

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