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