great, thanks for your help Dave. I'll have a go and if i have any luck will post my code for future reference
On Mon, Oct 23, 2017 at 10:02 PM, Dave S <snidely....@gmail.com> wrote: > > > On Saturday, October 21, 2017 at 2:25:47 PM UTC-7, Matthew J Watts wrote: >> >> Hi all >> >> I'm pretty new to web2py and web development in general. I'm really stuck >> with something and haven't been able to find a tutorial which can help me >> out etc. I was wondering if some one could point me in the right direction. >> >> I have three tables. I want to upload a csv, and update each table in >> sequence, so a newly generated primary key can be passed to each >> successive table. >> >> (1) >> db.define_table('study_info', >> Field('title')) >> One >> to many relationship >> (2) >> db.define_table('', >> >> Field('study_info_id', 'reference study_info'), >> Field('taxon_name', type = 'string', >> comment='Classification of sample collected')) >> >> (3) >> db.define_table('sample_data', >> One to many relationship >> Field('Core_data_id', 'reference core_data'), >> Field('value', type = 'double')) >> >> >> So just to clarify , data gets entered into the first table, the primary >> key from table 1 is then passed to the foreign key of 2, then same again >> for 2 to 3. >> >> I managed to get the function working to update one table from a csv, >> Would i somehow modify this with a for loop to insert one line from the >> csv at a time? >> >> def import_csv(): >> from gluon.sqlhtml import form_factory >> form = SQLFORM.factory(Field('csvfile','upload',uploadfield=False)) >> if form.process().accepted: >> db.study_info.import_from_csv_file(request.vars.csvfile.file) >> redirect(URL('index')) >> return dict(form=form) >> >> >> > I see 3 possible ways to go: > > Probably the cleanest is to do what import_from_csv_file() does: > > import csv # it's in the standard Python library, > csv.reader(mycsvfile, etc) > # <URL:https://docs.python.org/2.7/library/csv.html> > # for each line in the file: > lineno, line in enumerate(reader): > validate line > new_id = update_or_insert() > # take a look at gluon/packages/dal/pydal/objects.py, lines 911-949 > # to see how column headers are handled, etc > handle_next_table_here(..., new_id,...) > > The second way is similar, but puts more burden on you in preparing the > insert > > file = open(mycsvfile) > while not eof: > line = readline(file) > pieces = line.split(",") > pieces = pieces.cleanup() > # cleanup is a routine you supply to validate the format of the fields > # the split and the cleanup may need to handle different delimiters > ("csv" may actually be tab-seperated, or space-seperated) > # and if you have what looks like a delimiter in a quoted field, you > have to deal with that, too. > # but if your csv is controlled enough, you can do this without too > much trouble. > new_id = update_or_insert() > handle_next_table_here(..., new_id,...) > > The third choice is easy [1] but ugly, and may be slow: > > file1 = open(mycsvflle, "rb") > while not eof: > line = files1.readline() > file2 = open(mytmpfile, "wb") > file2.write(line) > file2.close() > import_from_csv_file(file2) > newrow = db(query).select().last() > handle_next_table_here(..., newrow.id,...) > > > > [1] okay, not dramatically easier than the first 2 > > Good luck! > > /dps > > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to a topic in the > Google Groups "web2py-users" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/web2py/JDVUaWb26ns/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > web2py+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.