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.
