6# How to Execute SQL Queries in Odoo

Odoo 18 introduces several improvements in database interactions, but sometimes, you may need to execute raw SQL queries to achieve specific requirements efficiently. In this blog, we will explore different ways to execute SQL queries in Odoo 18, covering both best practices and common use cases.

https://www.youtube.com/@smuhabdullah/

Why Use SQL Queries in Odoo?

While Odoo provides a robust ORM (Object-Relational Mapping) for handling database operations, certain scenarios may require raw SQL queries, such as:

• Bulk data updates or inserts
• Complex aggregations or joins
• Performance optimization for large datasets
• Running database maintenance tasks

Methods to Execute SQL Queries in Odoo

1. Using self.env.cr (Cursor)

Odoo provides a cursor object self.env.cr, which allows developers to execute raw SQL queries directly.

Example: Selecting Data

class ResPartnerInherit(models.Model):
    _inherit = 'res.partner'
    
    def get_partner_data(self):
        self.env.cr.execute("SELECT id, name FROM res_partner WHERE customer = TRUE")
        result = self.env.cr.fetchall()
        return result

2. Executing Queries with Parameters

To prevent SQL injection, always use parameterized queries.

self.env.cr.execute("SELECT id, name FROM res_partner WHERE id = %s", (partner_id,))
result = self.env.cr.fetchall()

3. Inserting or Updating Data

For bulk operations, you can use SQL queries to improve performance.

self.env.cr.execute("UPDATE res_partner SET is_company = TRUE WHERE country_id = %s", (country_id,))

4. Using self.env.cr.dictfetchall() for Dictionary Output

By default, fetchall() returns a list of tuples. Use dictfetchall() to retrieve results as dictionaries.

self.env.cr.execute("SELECT id, name FROM res_partner")
result = self.env.cr.dictfetchall()
for row in result:
    print(row['id'], row['name'])

Common Methods of self.env.cr

1. execute(query, params=None)

Executes an SQL query with optional parameters.

Example:

self.env.cr.execute("SELECT id, name FROM res_partner WHERE active=%s", (True,))

2. fetchone()

Retrieves a single record from the last executed query.

Example:

self.env.cr.execute("SELECT id, name FROM res_partner WHERE id=%s", (1,))
record = self.env.cr.fetchone()
print(record)  # Output: (1, 'Customer Name')

3. fetchall()

Retrieves all results from the last executed query.

Example:

self.env.cr.execute("SELECT id, name FROM res_partner WHERE active=%s", (True,))
records = self.env.cr.fetchall()
print(records)  # Output: [(1, 'Customer A'), (2, 'Customer B')]

4. fetchmany(size)

Retrieves a specified number of records.

Example:

self.env.cr.execute("SELECT id, name FROM res_partner WHERE active=%s", (True,))
records = self.env.cr.fetchmany(5)  # Fetch only 5 records

5. commit()

Saves the transaction permanently to the database.

Example:

self.env.cr.execute("UPDATE res_partner SET active=%s WHERE id=%s", (False, 1))
self.env.cr.commit()

Note: Be cautious when using commit(), as it bypasses Odoo’s transaction management.

6. rollback()

Reverts any uncommitted changes in case of an error.

Example:

try:
    self.env.cr.execute("UPDATE res_partner SET active=%s WHERE id=%s", (False, 1))
    raise Exception("Something went wrong")  # Simulating an error
    self.env.cr.commit()
except Exception:
    self.env.cr.rollback()  # Undo changes

7. rowcount

Returns the number of affected rows by the last SQL query.

Example:

self.env.cr.execute("UPDATE res_partner SET active=%s WHERE active=%s", (False, True))
print(self.env.cr.rowcount)  # Output: Number of affected rows

8. description

Returns metadata about the last executed query (column names, types, etc.).

Example:

self.env.cr.execute("SELECT id, name FROM res_partner")
print(self.env.cr.description)

Final Thoughts

Understanding self.env.cr methods can help you unlock more flexibility when working with Odoo databases. While raw SQL queries can be powerful, use them wisely to maintain data consistency and performance.

Do you have any questions or need help optimizing your Odoo queries? Feel free to ask in the comments!

📌 **Follow for more Odoo deve

CRUD Operations with SQL in Odoo

1. Create (Insert Data)

To insert records into a database table, use the following SQL statement:

class ResPartnerInherit(models.Model):
    _inherit = 'res.partner'
    
    def insert_partner(self, name, email):
        self.env.cr.execute("""
            INSERT INTO res_partner (name, email, company_type, create_date)
            VALUES (%s, %s, 'company', NOW())
        """, (name, email))
        self.env.cr.commit()

2. Read (Select Data)

Fetching records from a table:

class ResPartnerInherit(models.Model):
    _inherit = 'res.partner'
    
    def get_partner_data(self):
        self.env.cr.execute("SELECT id, name, email FROM res_partner WHERE customer = TRUE")
        result = self.env.cr.fetchall()
        return result

For dictionary output:

self.env.cr.execute("SELECT id, name FROM res_partner")
result = self.env.cr.dictfetchall()
for row in result:
    print(row['id'], row['name'])

3. Update Data

Updating existing records:

self.env.cr.execute("""
    UPDATE res_partner 
    SET is_company = TRUE 
    WHERE country_id = %s
""", (country_id,))
self.env.cr.commit()

4. Delete Data

Removing records from the database:

self.env.cr.execute("DELETE FROM res_partner WHERE id = %s", (partner_id,))
self.env.cr.commit()

Best Practices When Using SQL Queries in Odoo 18

• Use ORM whenever possible: Odoo’s ORM is safer, more efficient, and maintains data integrity.
• Always use parameterized queries: This prevents SQL injection vulnerabilities.
• Avoid hardcoded table names: Use self.env["model.name"]._table to get the correct table name dynamically.
• Optimize for performance: Use indexes and LIMIT clauses where applicable.
• Commit transactions when needed: If your SQL query modifies data outside ORM, you may need to commit changes explicitly.

self.env.cr.commit()

Conclusion

Executing SQL queries in Odoo can be powerful when used correctly. While the ORM should be your primary tool, direct SQL queries can be beneficial for specific use cases like bulk updates, reporting, and performance tuning. Always follow best practices to ensure data integrity and security.

By mastering these techniques, you can enhance your Odoo development skills and optimize your applications efficiently!

https://www.buymeacoffee.com/smuhabdullah

Click on the link below to discover a wealth of knowledge and explore a variety of engaging topics.

Medium Profile: Muhammad Abdullah Arif — Medium

Stay Up-to-Date with Muhammad Abdullah Arif’s Latest Publications — Subscribe Now! (medium.com)

If you wish to offer your support, kindly click on the link below to treat me to a coffee! ☕️😊

https://www.buymeacoffee.com/smuhabdullah

I wish you all the best in your future endeavours.