Linking webform to specific users in database

[aetag...@gmail.com]

Hello,
I am very new to web2py. I was hoping for some insight as to whether what I am trying to do is possible, and maybe if I can be redirected as to what I should read up on to help me figure this out. Any advice or insight will help.

I have web forms, which are linked to an excel file template. Once a web form is filled out and submitted, it is saved into the same folder that the excel template file is located in, under a new name where the original excel file template is untouched.

I want to make it so that, a user registers an account and when they fill out the webform it saves to that specific user in the database. If they fill out the same webform again, it saves without erasing their previous forms.

I am very certain that this is possible to do within web2py. I think to accomplish this, I need to use SQLForm. Will I need two seperate databases to accomplish this? One database that registers users, and another database that has an upload field defined that links to the other database?

Thank you.

[Richard Vézina]

Sure all this can be done. What have you done so far? Can you show us some piece of code?

Richard

--
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+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[aetag...@gmail.com]

Model:
db = DAL('sqlite://webform.sqlite')
db.define_table('excelform',
    Field('last_name', 'string'),
    Field('first_name', 'string'),
    Field('age', 'string'),
    Field('location', 'string')
)


That is one of my models, I have another that contains:


auth.settings.extra_fields['auth_user']= [
    Field('nickname', 'string'),
    Field('image', 'upload')
]

But I have not done anything with that yet in regards to the web form.

Controller(the sections that apply to my question):

def user():
    return dict(form=auth())


def excelform():
    update = db.excelform(request.args(0))
    form = SQLFORM(db.excelform, update)
    if form.accepts(request,session):
        response.flash = 'Thanks! The form has been submitted.'
        session.vars=form.vars
        redirect(URL('about'))
    elif form.errors:
       response.flash = 'Please correct the error(s).'
    else:
       response.flash = 'Try again - no fields can be empty.'
    return dict(form=form)

def about():
    import sys
    form = SQLFORM(db.excelform)
    from openpyxl import load_workbook
    wb = load_workbook(filename='/home/../Documents/web2py/applications/../static/excel.xlsx')
    sheet_ranges = wb['Sheet1']
    sheet_ranges['C4'] = session.vars.last_name
    sheet_ranges['C6'] = session.vars.first_name
    sheet_ranges['C8'] = session.vars.age
    sheet_ranges['C10'] = session.vars.location
    wb.save(filename='/home/../Documents/web2py/applications/../static/excel2.xlsx')
    message = session.vars.last_name , " ", session.vars.first_name + " "
    return dict()


def aboutlist():
    grid = SQLFORM.grid(db.excelform,user_signature=False)
    return locals()




I'm just trying to have a working model before I add error checking and more options into it.
From the web2py book, there is this section that I believe applies to what I want to do:

"Uploading files in database

By default, all uploaded files handled by SQLFORMs are safely renamed and stored in the filesystem under the "uploads" folder. It is possible to instruct web2py to store uploaded files in the database instead.

Now, consider the following table:

db.define_table('dog',
    Field('name')
    Field('image', 'upload'))

where dog.image is of type upload. To make the uploaded image go in the same record as the name of the dog, you must modify the table definition by adding a blob field and link it to the upload field:

db.define_table('dog',
    Field('name')
    Field('image', 'upload', uploadfield='image_data'),
    Field('image_data', 'blob'))

Here "image_data" is just an arbitrary name for the new blob field.

Line 3 instructs web2py to safely rename uploaded images as usual, store the new name in the image field, and store the data in the uploadfield called "image_data" instead of storing the data on the filesystem. All of this is be done automatically by SQLFORMs and no other code needs to be changed.

With this tweak, the "uploads" folder is no longer needed."

However I am confused by it, maybe because the example being used is an image file. I also am confused because this makes me think I need two separate databases instead of one where I can just link the session to the user that is logged in.. Maybe I am understanding it wrong.

[aetag...@gmail.com]

Also.. To add to my other post below..I was reading on the SQLFORM.factory and it seems like that may be a viable options as well for what I am trying to do. Here is the section I am referring to:

"SQLFORM.factory

There are cases when you want to generate forms as if you had a database table but you do not want the database table. You simply want to take advantage of the SQLFORM capability to generate a nice looking CSS-friendly form and perhaps perform file upload and renaming.

This can be done via a form_factory. Here is an example where you generate the form, perform validation, upload a file and store everything in the session :"

If a user is logged in and it stores in the session, is that the same thing as it saving it to an associated record in the database table?
 

Thank you

On Tuesday, November 24, 2015 at 11:18:51 AM UTC-5, Richard wrote:

[Anthony]

No, nothing will be persisted if you use only the session (the session lasts only during a particular browser session and disappears when the browser session has ended -- such as when the user closes the browser).

[Anthony]

There is no need to store the data in the session and then redirect to another action to create the file. Instead, just do it all at once. Also, if you want to store the file (safely) and make it easily retrievable, then add an upload field to the database table and store it that way. Finally, if you want to associate files with their creators, just add a reference field to the excelform table to reference the auth_user record of the currently logged in user. This can all be simplified as follows:

In a model file:

def create_excel(row):
    from openpyxl import load_workbook
    from openpyxl.writer.excel import save_virtual_workbook
    from cStringIO import StringIO

    wb = load_workbook(filename='/home/../Documents/web2py/applications/../static/excel.xlsx')
    sheet_ranges = wb['Sheet1']

    sheet_ranges['C4'] = row.last_name
    sheet_ranges['C6'] = row.first_name
    sheet_ranges['C8'] = row.age
    sheet_ranges['C10'] = row.location
    excel_file = StringIO()
    excel_file.write(save_virtual_workbook(wb))
    excel_file.seek(0)
    return db.excelform.excel_file.store(excel_file, 'spreadsheet.xlsx')

db.define_table('excelform',
    Field('last_name', 'string', requires=IS_NOT_EMPTY()),
    Field('first_name', 'string', requires=IS_NOT_EMPTY()),
    Field('age', 'string', requires=IS_NOT_EMPTY()),
    Field('location', 'string', requires=IS_NOT_EMPTY()),
    Field('excel_file', 'upload', compute=create_excel),
    Field('created_by', 'reference auth_user',
          default=auth.user_id, readable=False, writable=False))

The above adds the excel_file upload field to your model and defines it as a computed field. When an insert is made, the create_excel function is called. It creates the workbook, but instead of using wb.save() to create the file directly, it uses save_virtual_workbook to convert the workbook to a string. It then writes the string to a StringIO object, which is ultimately passed to the .store() method of the upload field. The .store() method creates the file and returns the filename that web2py created for it, which is ultimately stored in the upload field itself. The second argument to .store() is the filename you will see when you download the file (but not the filename used to actually store the file on the filesystem).

The created_by field is a reference to auth_user, with the default set to the ID of the current user (it is not readable or writable, so will not appear in the form).

Then, in the controller:

@auth.requires_login()
def excelform():
    record = db.excelform(request.args(0))
    form = SQLFORM(db.excelform, record,
                   message_onsuccess='Thanks! The form has been submitted.',
                   message_onfailure='Please correct the error(s).').process()
    return dict(form=form)

The controller is now quite simple -- it just creates and processes the form -- the rest of the logic is handled in the model. Note, assuming being a registered user is required, you should use the @auth.requires_login() decorator here.

If you want to show only the files of the current user, you can do:

    grid = SQLFORM.grid(db.excelform.created_by == auth.user_id)

Anthony

[Aeta Gothno]

Thank you very much for explaining everything to me.

It makes a lot more sense doing it that way, however I will have to read up on StringIO since I have not used that before.
I commented out my functions and replaced it with what you have suggested and there are no errors, however nothing shows up in the uploads field when I view the database. It is empty, I am not sure if it is because I am missing something in regards to downloading the file.

Before I was able to view the excel form being changed in the background and I would open the new excel file and the user inputs would be in the correct cells, but now the files are blank. I am confused if it may have something to do with saving it as a virtual workbook?

Since there are no errors, I think it at least indicates that it is opening the excel file and writing to it, I just cannot find where it might be storing the file afterwards since it shows up empty.

I also was not aware that I can create functions like so within the models file, that is very convenient.

On Tue, Nov 24, 2015 at 12:43 PM, Anthony <abas...@gmail.com> wrote:

There is no need to store the data in the session and then redirect to another action to create the file. Instead, just do it all at once. Also, if you want to store the file (safely) and make it easily retrievable, then add an upload field to the database table and store it that way. Finally, if you want to associated files with their creators, just add a reference field to the excelform table to reference the auth_user record of the currently logged in user. This can all be simplified as follows:

In a model file:

def create_excel(row):
    from openpyxl import load_workbook
    from openpyxl.writer.excel import save_virtual_workbook
    from cStringIO import StringIO
    wb = load_workbook(filename='/home/../Documents/web2py/applications/../static/excel.xlsx')
    sheet_ranges = wb['Sheet1']
    sheet_ranges['C4'] = row.last_name
    sheet_ranges['C6'] = row.first_name
    sheet_ranges['C8'] = row.age
    sheet_ranges['C10'] = row.location
    excel_file = StringIO()
    excel_file.write(save_virtual_workbook(wb))
    excel_file.seek(0)
    return db.excelform.excel_file.store(excel_file, 'spreadsheet.xlsx')

db.define_table('excelform',
    Field('last_name', 'string', requires=IS_NOT_EMPTY()),
    Field('first_name', 'string', requires=IS_NOT_EMPTY()),
    Field('age', 'string', requires=IS_NOT_EMPTY()),
    Field('location', 'string', requires=IS_NOT_EMPTY()),
    Field('excel_file', 'upload', compute=create_excel),
    Field('created_by', 'reference auth_user',
          default=auth.user_id, readable=False, writable=False))

The above adds the excel_file upload field to your model and defines it as a computed field. When an insert is made, the create_excel function is called. It creates the workbook, but instead of using wb.save() to create the file directly, it uses save_virtual_workbook to convert the workbook to a string. It then writes the string to a StringIO object, which is ultimately passed to the .store() method of the upload field. The .store() method creates the file and returns the filename that web2py created for it, which is ultimately stored in the upload field itself. The second argument to .store() is the filename you will see when you download the file (but no the filename used to actually store the file on the filesystem).

The created_by field is a reference to auth_user, with the default set to the ID of the current user (it is not readable or writable, so will not appear in the form).

Then, in the controller:

@auth.requires_login()
def excelform():
    record = db.excelform(request.args(0))
    form = SQLFORM(db.excelform, record,
                   message_onsuccess='Thanks! The form has been submitted.',
                   message_onfailure='Please correct the error(s).').process()
    return dict(form=form)

The controller is now quite simple -- it just creates and processes the form -- the rest of the logic is handled in the model. Note, assuming being a registered user is required, you should use the @auth.requires_login() decorator here.

If you want to show only the files of the current user, you can do:

    grid = SQLFORM.grid(db.excelform.created_by == auth.user_id)

Anthony

--

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/B4H1Q6jV9S4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Anthony]

Works perfectly for me (the only difference is I don't have your template so am creating a workbook from scratch). How are you "viewing the database" and downloading the files?

Anthony

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Aeta Gothno]

I have added the following into my controller:

def download():
    file_id = request.args(0)
    import cStringIO
    import contenttype as c
    s=cStringIO.StringIO()
    
    (filename,file) = db.excelform.excel_file.retrieve(db.excelform[file_id].file)
    s.write(file.read()) 
    response.headers['Content-Type'] = c.contenttype(filename)
    response.headers['Content-Disposition'] = \
                "attachment; filename=%s" % filename 
    return s.getvalue()



def list_files():
    excel_file = db(db.excelform.id>0).select(db.excelform.excel_file)
    urls = [A('Download', _href=URL(r=request, c='default', f='download', args=[row.id]
                 )) for row in excel_file]
    return urls

However I keep getting the ticket :

<type 'exceptions.AttributeError'> 'NoneType' object has no attribute 'file'

I am not sure if this is because nothing appears to save in the first place, or if it is because I am using the function wrong.

[Anthony]

Get rid of your download function and just use the download function that comes in the welcome app -- it uses response.download which is designed to handle downloading from upload fields automatically. The URL arg, however, should be the file name stored in the upload field, not the record ID. (As an aside, there would be no need for a StringIO there anyway, as you can simply stream the contents of the file directly.)

Anthony

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Aeta Gothno]

Using the default download function doesn't work either, it just gives me a 404 Not Found. I don't understand why. It actually allows you to download and view the spreadsheets?

To unsubscribe from this group and all its topics, send an email to web2py+un...@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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/B4H1Q6jV9S4/unsubscribe.

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Anthony]

If you don't mind, please pack and attach your application (feel free to send privately if your prefer).

Anthony

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/B4H1Q6jV9S4/unsubscribe.

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Anthony]

In the model, you changed the field name from "location" to "located" (probably because "location" is a reserved word) but forgot to change it in the compute function:

    sheet_ranges['C10'] = row.location

should be:

    sheet_ranges['C10'] = row.located

When a compute function throws an exception, the DAL catches it, so your compute function was failing silently.

Anthony

[aetag...@gmail.com]

Of course I would do something like that. It works now! Thank you so much for your help and explaining everything.