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

Question

I am trying to extract data from a .mdb database and get it in the columns of the Odoo 8 class.

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 repetitive work, when I click the "Submit" button, the data is displayed, as in the following images

Can anyone make a valuable contribution to this? for example, how to get these values ​​in the columns of the Odoo class (I had in mind the assignment to the fields of the class), as well as how to get the columns from two tables.

Answer 1

You need to understand the sample 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 this

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

Regardless of the values ​​you want to set, all of them must be returned by the query.

However, this is an example that you may need to update to suit your situation.

Answer 2

Try installing / updating the pyodbc version .. contact this link