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.

Photocell

And more over its a differnent database. (.MDB file) – Photocell

Anyone with any suggestion.!!!? – Photocell

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.

Avictor

Emipro technologies, First of all thank you very much for your explained answer. But now I am getting error something like : AttributeError: 'pyodbc.Cursor' object has no attribute 'dictfetchall' – Photocell

Answer 2

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

Gunrunning

I have already upgraded by refering to that link. It still does not work – Photocell