CODEWITHSUNDEEP
pythonms-accessodoo-8openerp-8odoo-9
Shravy
Shravy

Reputation: 666

How to extract data from SQL query and assign it to Odoo class columns?

I have been trying to extract data from a .mdb database and get it into Odoo 8 class columns.

This is my .py file

  class attendance_biometric(osv.Model):
    _name="attendance.biometric"
    _rec_name='name'
    _columns={

        'fdate':fields.datetime('From Date'),
        'tdate':fields.datetime('To Date'),
        'code':fields.integer('Code'),
        'name':fields.many2one('res.users','Employee Name', readonly=True),
        'ref': fields.one2many('bio.data', 'bio_ref', 'Data'),
    }

    _defaults = {
            'name': lambda obj, cr, uid, context: uid,

            }


def confirm_submit(self, cr, uid, ids, context=None):
        result=[]
        DBfile = '/home/administrator/test.mdb'
        conn = pyodbc.connect('DRIVER=MDBtools;DBQ='+DBfile)
        cr = conn.cursor()
        sql = '''
            select InTime, OutTime, OutDeviceId, Duration from 
AttendanceLogs '''
        cr.execute(sql)
        rows = cr.fetchall()
        for row in enumerate(rows):
            result.append(row)
        raise osv.except_osv(_('Info'),_('Data : %s\n' % (result)))

Now after some re-work when I click submit button, the data shows up like in the following images

Results in the logger info

Could someone provide valuable input on this? like how to get those values into Odoo class columns(I meant assigning to the fields that of the class) and also how to get columns from two tables.

Upvotes: 7

Views: 5594

Answers (2)

KbiR
KbiR

Reputation: 4174

Try to install /upgrade pyodbc version .. refer this link

Upvotes: 1

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

You need to understand the fetch types in odoo.

 - cr.dictfetchall()
       It will returns the list of dictionary.
       Example:
           [{'column1':'value_column1',}, {'column2':'value_column2',}] 

 - cr.dictfetchone() 
       It will return dictionary (Single record)
       Example:
           {'column1':'value_column1',}


 - cr.fetchall()
        It will returns the list of tuple.
        Example: 
            [('value_column1'), ('value_column2'), ].



 - cr.fetchone()
        It will returns the list of tuple.
        Example: 
            ('value_column1')

So update your code something like that,

res = cr.dictfetchall()
result['sname'] = res and res[0]['sname']

Whatever the values you want to set, all those must be returned by query.

However this is example you may need to update it according to your situation.

Upvotes: 2

Related Questions