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 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.