How to use cursors in Odoo?

Question

I don't understand using cursor in version 9! Do you need cursor?

In below example after call function in console line get:

<openerp.sql_db.Cursor object at 0x7f94f4c0b0d0>

@api.multi   
    def my_func(self):
        cursor = self.env.cr
        print(cursor)   

Any simple example when and why use cursor?

Answer 1

Basically the cursor object is the interface Odoo uses to communicate with the postgresql database.

In Odoo you usually dont have to interact with it directly however sometimes you will encounter circumstances where the Odoo ORM just isnt giving you the desired results. This is when you need to use the database cursor. For sql queries which cant be achieved using the ORM.

You may also use the database cursor to overcome security restrictions although some would say this is not advisable. I however wont judge you.

The cursor in Odoo is a database cursor. It is basically a psycopg2 database cursor. I believe it has been modified by Odoo however cant give you a specific example of what has been modified.

Here are some simple examples.

@api.multi
def sql_example(self):
    sql = "SELECT * FROM table_name WHERE col = 'some_value'"

ABOVE QUERY CAN BE WHATEVER YOU WANT

    self.env.cr.execute(sql)
    for record in self.env.cr.fetchall():
        # YOUR CODE HERE

IF YOU WANT TO EXECUTE UPDATE / DELETE / CREATE functions you must commit your changes.

    sql = "UPDATE table_name SET col = 'some_value' WHERE col = 'some_value'"
    self.env.cr.execute(sql)
    self.env.cr.commit()

On occasion you may find cached data is being returned in your queries. You may need to flush this.

    self.env.invalidate_all()

So if you want a better understanding of what the cursor object is you can review the documentation

Tutorials Point,Psycopg2 Docs,Postgresql Docs

Bottom line, the cursor is how you query the database. You do not have to use it however it is available to you should you find yourself needing to get data that is not available using the ORM.

Answer 2

You can use cr to execute query in your function.

@api.multi   
def my_func(self):
   cursor = self.env.cr
   cursor.execute("select * from res_partner")
   for partner in cursor.fetchall():
      ......
      ......