update multiple tables from one csv and get row id after update_or_insert

I'm trying to build a .csv data upload facility which will update two tables in sequence. So, i have my 'person' table which has a one to many relationship with the 'thing' table i.e. one person can own many different things.

What i need to do is get the primary key from a 'person' row after an 'update_or_insert ', this ID will then go into the 'owner_id' for the correseponding row in the 'thing' table.

Any ideas as to how i can do this? Excuse my programming i'm a beginner!

model

    db.define_table('person',
                        Field('name'),
                        Field('age'),
                        Field('country'),
                        format='%(name)s')

    db.define_table('thing',
                        Field('thing_name'),
                        Field('value'),
                        Field('location'),
                        Field('owner_id'))

controller

import csv

def importer():
    form = FORM(INPUT(_type='submit',_class="btn btn-primary"))
    if form.process().accepted:
        with open('C:/Users/matt/PycharmProjects/all_projects/web2py/applications/uploadTest/private/test.csv') as csvfile:
            readCSV = csv.reader(csvfile, delimiter=',')
            next(readCSV, None)
            for row in readCSV:
                name = row[0]
                age = row[1]
                country = row[2]
                thing_name = row[3]
                value = row[4]
                location = row[5]
                db.person.update_or_insert(name=name,age=age, country=country)
                db.thing.insert(thing_name=thing_name,value=value,location=location)
    return dict(form=form)

and the following data

person.name,person.age,person.country,thing.name,thing.value,thing.location
matt,38,uk,teddy,12,house
matt,38,uk,kite,13,outside
matt,38,uk,screen,14,shed
matt,38,uk,teddy,15,garden
will,24,us,table,16,house
will,24,us,teddy,17,outside
will,24,us,kite,18,shed
will,24,us,screen,19,garden
pete,56,bel,teddy,20,house
pete,56,bel,table,21,outside
pete,56,bel,teddy,22,shed
pete,56,bel,kite,23,garden
pete,56,bel,screen,24,house
pete,56,bel,teddy,25,outside
matt,38,aus,teddy,26,shed
matt,38,can,kite,27,garden
matt,38,fr,screen,23,shed
pete,25,bel,teddy,22,shed
pete,25,bel,screen,74,shed

ps - i'm getting the file locally as i havent worked out the .retrieve: method yet

Solution

.update_or_insert will not return the record ID in case of update, so you cannot use that method. Instead, simply check for and fetch the record to get its ID. For example:

        for row in readCSV:
            person = dict(zip(('name', 'age', 'country'), row[:3]))
            record = db.person(**person) # Check for a match.
            person_id = record.id if record else db.person.insert(**person)
            thing = dict(zip('thing_name', 'value', 'location'), row[3:6])
            db.thing.insert(owner_id=person_id, **thing)

Note, the db.person(**person) syntax returns the first record that matches all the values in the person dictionary (presumably you are expecting at most one match given your data) or None if there is no match (see the documentation for details).

Also, note that in case there is a matching db.person record, there is no need to update it, as all the fields you have available in the CSV file are already being used to find the match (i.e., there are no additional fields whose values need to be updated).