CODEWITHSUNDEEP
pythoneclipseodoo
Arsalan Sherwani
Arsalan Sherwani

Reputation: 560

What does cr.execute mean in OpenERP development?

I have been trying to learn Open ERP module development through Python . I came up wuth a source code that I dont think I am understanding. I have tried few links on the internet while browsing but cant meet a useful one . I need to understand this badly.

cr.execute("""
            SELECT a.id as id, COALESCE(MAX(l.date),a.purchase_date) AS date
            FROM account_asset_asset a
            LEFT JOIN account_move_line l ON (l.asset_id = a.id)
            WHERE a.id IN %s
            GROUP BY a.id, a.purchase_date """, (tuple(ids),))
        return dict(cr.fetchall())

Please reply, Hopes for suggestion, Best wishes, Thanks.

Upvotes: 2

Views: 6421

Answers (2)

Andrei Boyanov
Andrei Boyanov

Reputation: 2393

As you understood cr is a database cursor. It allows you to execute SQL queries directly on the database and fetch the result.

The way it works is nothing special:

Execute the SQL code:

cr.execute("some sql code")

Retreive the results:

cr.fetchone()
cr.fetchall()
etc...

You can also iterate over the cursor:

for rec in cr:
    print(rec)

For more information about the cursor object you may want to take a look at the Psycopg documentation

In OpenERP the cr object is initialized somewhere in osv/orm code and is available in model you create as child class of osv.osv.

Now that I have sad that I have to notice that normally you don't want to use direct SQL queries in OpenERP. OpenERP offers you entire Object Relational Model (orm) as abstraction of the database layer. For example if you want to access the account_asset_asset model somewhere in your code you should prefer the 'native' for OpenERP method using the orm:

asset_obj = self.pool.get('account.asset.asset')
asset_ids = asset_obj.search(cr, uid, [('date', '>', start_date), ('date', '<', end_date)])
assets = asset_obj.browse(cr, uid, asset_ids, context=your_context)
for asset in assets:
    print asset

Keep the cr functionality for some specific cases where the 'native' method would be to havy.

Upvotes: 3

Adrian Merrall
Adrian Merrall

Reputation: 2499

Just wanted to add some points here so I think it deserves a separate answer:

  • The cr (cursor) is not the psycopg2 cursor, it is an OpenERP class that contains a psycopg2 cursor. It generally passes most method calls through to the encapsulated psyco cursor but it does implement a few of it's own such as close, execute etc. In OpenERP 7 look in sql_db.py

  • To correct Andrei's example, the OpenERP cursor is not iterable, if you try to iterate over it you will see:

    for row in cr:
TypeError: 'Cursor' object is not iterable

    One day I will get around to raising a bug on this.

  • fetchone and fetchall methods are passed through to the underlying pscyo cursor so read the standard docs on those ones.

  • For general use, if you do a cr.execute, the result set is only there until you do something else like an ORM write etc.

  • cr.execute is typically only used for special cases, or running complex SQL joins etc. It should only be a resort after you are sure the ORM won't do what you want in a timely manner.

  • Don't ever try to manage transactions on the supplied cr object yourself. If you need to do this to prevent long running transactions for something like an overnight calculation update, create and manage your own cursor. Have a look at the certified procurement module for an example.

  • Make sure you understand what SQL injection is and the correct way to use query parameters (check the docs for the cursor class in psycopg2) before you start assembling queries.

Upvotes: 3

Related Questions