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

Question

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

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.

Answer 1

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.

Answer 2

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