CODEWITHSUNDEEP
databasemodel-view-controllerweb2py
madu
madu

Reputation: 5460

Web2Py - Create table from user input

I'm trying to create a table in a database in web2py. I'm new to this and trying to get a hold of the MVC structure and how to call in between. What I have done is in /modles/db.py I created a DB:

TestDB = DAL("sqlite://storage.sqlite")

Then in my /controllers/default.py I have:

def index():
    form = FORM(INPUT(_name='name', requires=IS_NOT_EMPTY()),
              INPUT(_type='submit'))

    if form.process().accepted:
        TestDB().define_table(form.vars.name, Field('testField', unique=True))
        return dict(form=form)

    return dict(form=form)

But this isn't working. Could somebody help me with understanding how to achieve this?

Thank you.

Upvotes: 1

Views: 1634

Answers (1)

Anthony
Anthony

Reputation: 25536

First, to define the table, it would be TestDB.define_table(...), not TestDB().define_table(...).

Second, table definitions don't persist across requests (of course, the database tables themselves persist, but the DAL table definitions do not). So, if you define a table inside the index() function, that is the only place it will exist and be accessible in your app. If you want to access the table elsewhere in your app, you'll need to store the metadata about the table definition (in this case, just the table name) somewhere, perhaps in the database or a file. You would then retrieve that information on each request (and probably cache it to speed things up) and use it to create the table definition.

Another option would be to generate the table definition code and append it to a model file so it will automatically be run on each request. This is roughly how the new application wizard in the "admin" app works when generating a new application.

Finally, you could leave the table definition in the index() function as you have it, and then when you create the database connection on each request, you can use auto_import:

TestDB = DAL("sqlite://storage.sqlite", auto_import=True)

That will automatically create the table definitions for all tables in TestDB based on the metadata stored in the application's /databases/*.table files. Note, the metadata only includes database-specific metadata, such as table and field names and field types -- it does not include web2py-specific attributes, such as validators, default values, compute functions, etc. So, this option is of limited usefulness.

Of course, all of this has security implications. If you let users define tables and fields, a particular submission could mistakenly or maliciously alter existing database tables. So, you'll have to do some careful checking before processing user submissions.

Upvotes: 2

Related Questions