In Odoo, you usually interact with the database using the ORM
(Object-Relational Mapping). However, there are cases where the ORM
isn't efficient or flexible enough—like when you're working with
complex joins, bulk updates, or performance-sensitive operations.
That's where direct SQL execution comes in.
Odoo 18, like previous versions, allows you to run SQL commands
directly through the database cursor (cr) available on the
environment.
self.env.cr.execute("YOUR SQL QUERY HERE", params)
When you use CREATE, UPDATE, or DELETE with SQL, you're changing the
database outside the ORM’s knowledge. This can lead to
inconsistencies between what the ORM "thinks" and what's actually in
the database.
self.env[<model_name>].invalidate_cache(fnames=None, ids=None)
Where:
- fnames: list of field names that changed (or None for all
fields).
- ids: list of record IDs that changed (or None for all
records).
Example: You just updated a record in SQL—call
invalidate_cache() so that when you access that record
again via ORM, it fetches the updated values.
No Cache Clearing for SELECT Queries
If you're only reading data (using SELECT), you don’t need to
invalidate the cache, because no data is being changed.
# Complex SELECT query (no cache clearing needed)
self.env.cr.execute("SELECT id, name FROM res_partner WHERE active=%s", (True,))
partners = self.env.cr.fetchall()
# Raw UPDATE query (cache must be cleared!)
self.env.cr.execute("UPDATE res_partner SET active=%s WHERE id=%s", (False, 42))
self.env['res.partner'].invalidate_cache(ids=[42])