The object in self.env.cr is a thin wrapper around a psycopg2 cursor. The following methods are the ones that you will want to use most of the time:
- execute(query, params): This executes the SQL query with the parameters marked as %s in the query substituted with the values in params, which is a tuple.
Warning: Never do the substitution yourself, as this can make the code vulnerable to SQL injections.
- fetchone(): This returns one row from the database, wrapped in a tuple (even if there is only one column selected by the query)
- fetchall(): This returns all the rows from the database as a list of tuples
- fetchalldict(): This returns all the rows from the database as a list of dictionaries mapping column names to values
Be very careful when dealing with raw SQL queries:
- You are bypassing all the security of the application. Ensure that you call search([('id', 'in', tuple(ids)]) with any list of ids you are retrieving to filter out records to which the user has no access.
- Any modifications you are making are bypassing the constraints set by the add-on modules, except the NOT NULL, UNIQUE, and FOREIGN KEY constraints, which are enforced at the database level. This is also the case for any computed field recomputation triggers, so you may end up corrupting the database.