A more concise way to update or insert?

[David S]

I've got some csv files which need to be imported to a table. However the import_from_csv_file function doesn't seem to work because the files can (and almost always do) contain errors and to the best of my knowledge that function cannot fix errors on the fly. Anyway, I decided to write my own csv reading function which puts each line into a list and then uses update_or_insert to get each line into the db.

The problem I'm having is there are two csv files for each entry in the table (think product_inspection_1.csv and product_inspection_2.csv, matched by serial #), and when using update_or_insert the inspection 2 results can overwrite the inspection 1 results and vice versa.

Is there a way using update_or_insert to conditionally update a column in a row? Something like:

if col_1:

only update col_2

if col_2:

only update col_1

I've been able to get something working by using an insert, catching an exception when the serial #s conflict, and then selectively updating the columns which need updating. But it's kind of like a moped: it gets the job done but you wouldn't your friends to see you using it.

Does update_or_insert have this capability? Is there another function which might do this in a more concise manner?

David

[Niphlod]

given that you wrote your own parser, why are you having problems with updates ?
just pass only the columns you need to update to the update_or_insert statement....

[David S]

The issue with doing it that way is the files are not separated (in the same folder) and there's not really a good way to tell them apart as the file names are just numbers. I've also no control over where the files are stored, or the folder structure. I suppose I could do something that checks for which columns are empty and determines what to update from that... Regardless there still would have to be some sort of check before updating/inserting.

[Derek]

Right, well, it sounds like you know what you want then. Some kind of update mechanism that doesn't take null fields.

Nothing like that exists in Web2py, so that answers your question.

However, the way I see it, you can write one fairly easily.

Get your 'update' row, make it a dict (dict a). If there is a field with a 'null' in the value, delete that item from the dict.

Next, load your existing object into a different dict (dict b). Now you write your merge/update...

 dicta.get('fieldname',b[fieldname])

What that does is get the item from A if it exists (ie if it's not null and you've not deleted it) and if it doesn't exist, get the item from B.

Does that help you out?