[web2py] Bunch/Batch/Bulk insert Re-use db.define_table field definition in SQLFORM.factory() ?

[Richard]

Hello,

I would like to build a bunch insert form and I wouldn't have to duplicate fields definition for SQLFORM.factory

I try naively 

inputs = []

for i in range(0,10):

   inputs.append(db.table1.field1)

   inputs.append(db.table1.field2)

form = SQLFORM.factory(*inputs)

But even if I get 10 inputs of each in "inputs" they all refer to the same instance so when I render the form with SQLFORM.factory, I only get my 2 fields, but I would have 10 of each...

Is there a way to avoid doing this :

inputs = []

for i in range(0,10):

   inputs.append(Field('field1', 'type...', ...))

   inputs.append(Field('field2', 'type...', ...))

form = SQLFORM.factory(*inputs)

Because doing the last example would make my code much more less DRY.

Thanks

Richard

[Ricardo Pedroso]

On Wed, Mar 13, 2013 at 7:31 PM, Richard <ml.richa...@gmail.com> wrote:
> Hello,
>
> I would like to build a bunch insert form and I wouldn't have to duplicate
> fields definition for SQLFORM.factory
>
> I try naively
>

> <code>

> inputs = []
> for i in range(0,10):
> inputs.append(db.table1.field1)
> inputs.append(db.table1.field2)
>
> form = SQLFORM.factory(*inputs)

> </code>

>
> But even if I get 10 inputs of each in "inputs" they all refer to the same
> instance so when I render the form with SQLFORM.factory, I only get my 2
> fields, but I would have 10 of each...
>
> Is there a way to avoid doing this :
>

> <code>

> inputs = []
> for i in range(0,10):
> inputs.append(Field('field1', 'type...', ...))
> inputs.append(Field('field2', 'type...', ...))
>
> form = SQLFORM.factory(*inputs)

> </code>

>
> Because doing the last example would make my code much more less DRY.

Maybe something like this (not tested):

inputs = [Field('field%d' % i, 'type') for i in range(1,11)]

Ricardo

[Anthony]

SQLFORM.factory creates a dummy DAL Table object with the fields submitted, so you cannot use duplicate field names, as they are illegal in DAL tables. Note, technically your form can have input elements with the same name, but once the values are submitted to the server, the values will be joined together in a single list. For example, if you have three fields with the name "field1", request.vars.post_vars will be a list like [value1, value2, value3].

What are you really trying to do?

Anthony

On Wednesday, March 13, 2013 3:31:19 PM UTC-4, Richard wrote:

Hello,

I would like to build a bunch insert form and I wouldn't have to duplicate fields definition for SQLFORM.factory

I try naively 

inputs = []

for i in range(0,10):

   inputs.append(db.table1.field1)

   inputs.append(db.table1.field2)

form = SQLFORM.factory(*inputs)

But even if I get 10 inputs of each in "inputs" they all refer to the same instance so when I render the form with SQLFORM.factory, I only get my 2 fields, but I would have 10 of each...

Is there a way to avoid doing this :

inputs = []

for i in range(0,10):

   inputs.append(Field('field1', 'type...', ...))

   inputs.append(Field('field2', 'type...', ...))

form = SQLFORM.factory(*inputs)

Because doing the last example would make my code much more less DRY.

Thanks

Richard

[Richard Vézina]

Hello Anthony,

I know that, I just forget to take it in consideration in my example I guess...

What I want to do is to create a batch insert form for a table a kind of inline edition but for insert data into database... So I would have many rows in a html table that will contains the fields a given table and only one submit button.

I can build my form like this :

inputs = []

for i in range(0,10):

   inputs.append(Field('field1'+'_%s' %str(i), 'type...', ...))

   inputs.append(Field('field2'+'_%s' %str(i), 'type...', ...))

form = SQLFORM.factory(*inputs)

That way I get unique distinct inputs fields.

Then with custom form I can esealy build html table in view...

But what I would like to avoid is to have 2 instances for field definition : one in model and one in the batch insert function because it seems to me that I can't get the field definition propertieds from model...

I would like to know if I could get field properties from model something like that :

inputs = []

for i in range(0,10):

    inputs.append(Field(db.table1.field1.name+'_%s' %str(i), db.table1.field1.type, ...)

    ...

Thanks

Richard

--
 
---
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/groups/opt_out.
 
 

[Richard Vézina]

I think I found the solution in my last example... I was guessing, but it seems to work.

Richard

[Anthony]

How about:

for i in range(0,10):
    inputs.append(db.table1.field1.clone(name='%s_%s' % (db.table1.field1.name, i)))

The .clone() method copies the field, but you can pass in arguments to replace some of the existing attributes (such as the name). I don't think this is documented, though.

Anthony

[Richard Vézina]

Hmm... That's interessting!

Since it is not documented yet could it be modified in a near future? I mean, if I start using it could I experiment bugs to trigger in my app in the future cause the syntax change or something like that?

Thanks for this tip Anthony.

RIchard

[Richard Vézina]

You know what Anthony, your tips is pretty fucking nice!!

:D

Sorry for slang language.

Richard

[Anthony]

Since it is not documented yet could it be modified in a near future? I mean, if I start using it could I experiment bugs to trigger in my app in the future cause the syntax change or something like that?

Good question. Perhaps Massimo could indicate whether Field.clone is intended to be part of the public API (and therefore remain backward compatible).

Anthony

[Massimo Di Pierro]

It will stay. It is intended for this purpose. Sorry it is undocumented. :-(

[黄祥]

nice solution, with this can solve the problem to add the multiple data simultanously within one form.

[Richard Vézina]

Yop!

I am almost finish, I will publish the code I come up with when it good enough...

:)

Richard

[黄祥]

that's good, thanks for sharing. that's what i'm looking for, because if i'm not wrong, there is no web2py appliances that show how to add multiple data simultaneously within 1 form.

[Richard Vézina]

I didn't find noting, but didn't search that much... Ask a couple of time. There is many problems to resolve, and I there is no single way to solve them. I develop something that may works for me, but may be not exactly what you are searching for. Anyway... As soon as I get something correct I show it here...

Richard

[黄祥]

i'm just trying the code myself but seems the result is not expected (no errors occured)

e.g.

db.py

db.define_table('company',

    Field('company'))

default.py

def index():

    inputs = []

    for i in range(0,10):

        inputs.append(db.company.company.clone(name='%s_%s' % (db.company.company.name, i)))

    form = SQLFORM.factory(*inputs)

    return dict(form=form)

index.html

{{extend 'layout.html'}}

{{form}}

when i view source and inspect elements there is no form html tag in it, is there something i missed?

thank you

[Anthony]

It's {{=form}}, not {{form}}.

Anthony

[黄祥]

yeah my mistake i didn't notice, thanks for correcting me, anthony. i've already test the form is work fine, but it can't insert it into database. i've tried to add some code for that but the result is not expected (no error occured), here is the database table content, after i input and submit it. the others strange is when i refresh the page, the table is fill it up with the data (not input by me).

company.id	company.company
1	None_0
2	None_1
3	None_2
4	None_0
5	None_1
6	None_2
7	None_0
8	None_1
9	None_2

db.py

db.define_table('company',

    Field('company'))

default.py

def index():

    inputs = []

    for i in range(0,3):

        inputs.append(db.company.company.clone(name='%s_%s' % (db.company.company.name, i)))

    form = SQLFORM.factory(*inputs)

    if form.process().accepted:

        for i in range(0,3):

            db.company.insert(company='%s_%s' % (form.vars.company, i))

    elif form.errors:

        response.flash = 'form has errors'

    return dict(form=form)

index.html

{{extend 'layout.html'}}

{{=form}}

is there something wrong in my code above?

thank you

[Anthony]

There is no form.vars.company -- it's form.vars.company_0, form.vars.company_1, etc. So:

db.company.insert(company=form.vars['company_%s' % i])

Anthony

[Richard Vézina]

Good catch Anthony...

@Steve it exactly what I have in mind, except in my case the form has to be generic for many differents tables, but if you only one table to bunch insert, that way it works fine...

Notice, if you create more inputs then the number your users will need (not 3 records for instance) you get in trouble if you have not null requires or IS_IN_DB or other validators)... So I use an other form to catch the number of input the user is needing and I pass it buy the URL as a vars something like that :

URL(c='controller', f='bunch_func', vars=dict(nb_inputs=3))

Then I use it in range function :

for i in range(0, int(request.vars.nb_inputs))

    ...

Also, you have to use custom form in the view to put the fields in a table and display the field labels only in the th of the table header something like that :

{{=form.custom.begin}}

<div style="overflow-y:auto;" width="90%";>

<table class="batch_insert sortable" >

    <tr class="batch_insert">

        {{for i, field_name in enumerate(fields_subset_batch_insert):}}

            {{if field_name == 'field1':}}

                <th class="th0 batch_insert">{{=db[request.args(0)][field_name].label}}</th>

            {{else:}}

                <th class="batch_insert">{{=db[request.args(0)][field_name].label}}</th>

            {{pass}}

        {{pass}}

    </tr>

    {{for i in range(0,int(request.vars.nb_inputs)):}}

        <tr>

            {{for field_name in fields_subset_batch_insert:}}

                {{if field_name == 'field1':}}

                    <td class="td0 batch_insert">{{=form.custom.widget['%s_%s' %(field_name, i)]}}</td>

                {{else:}}

                    <td class="batch_insert">{{=form.custom.widget['%s_%s' %(field_name, i)]}}</td>

                {{pass}}

            {{pass}}

        </tr>

    {{pass}}

</table>

</div>

<br/>

{{=form.custom.submit}}

{{=form.custom.end}}

I was waiting to get a more detailed explanation with the CSS, etc.

But it take time for that :)

Richard

--

[黄祥]

@anthony : thank you for correcting me anthony

@richard : i'm learning to use 2 input form too but not work as expected (no error occured), the data is not insert after i submit it. any idea about this?

thank you

default.py

def test():

    inputs = []

    for i in range(0, int(request.vars.row)):

        inputs.append(db.company.company.clone(name='%s_%s' % (db.company.company.name, i)))

    form = SQLFORM.factory(*inputs)

    if form.process().accepted:

        for i in range(0, int(request.vars.row)):

            db.company.insert(company=form.vars['company_%s' % i])

    elif form.errors:

        response.flash = 'form has errors'

    return dict(form=form)

def index():

    return dict()

index.html

{{extend 'layout.html'}}

<form>

   <input name="row" onkeyup="ajax('test', ['row'], 'target')" />

</form>

<div id='target'></div>

[Richard Vézina]

You may need to db.commit() because the manual insert may not commit normally maybe??

    if form.process().accepted:

        for i in range(0, int(request.vars.row)):

            db.company.insert(company=form.vars['company_%s' % i])

        db.commit()

But I am not sure I understand you onkeyup ajax call what's the intent of it???

Richard

--

[Anthony]

Is that the entire view? You haven't included the form anywhere, so there are no company_0, company_1, etc. inputs.

Anthony

[Anthony]

No, db.commit() should not be necessary as long as the inserts are part of a request (committing happens automatically at the end of the request).

Anthony

[黄祥]

Is that the entire view? You haven't included the form anywhere, so there are no company_0, company_1, etc. inputs.

yes, the form is appear (please find attached file). the strange part in here is i can't see the <form> html tag in the page source except for the ajax row one (the form input for bunch insert company data is not in page source). when i inspect elements i can found the form input for bunch insert.

found in inspect elements but not in page source :

<input class="string" id="no_table_company_0" name="company_0" type="text" value="">

[Anthony]

When you enter a number in the "row" input, it sends an Ajax request to the test() function, which then loads an empty form in the "target" div. If you then submit that form, it will post back to the parent page function (i.e., index), and it won't send the value of "row" again.

Anthony

[ksotiris]

Hello web2py-users,  I am new to web2py framework. I have a question about the followings: 

 

@Steve it exactly what I have in mind, except in my case the form has to be generic for many differents tables, but if you only one table to bunch insert, that way it works fine...

Notice, if you create more inputs then the number your users will need (not 3 records for instance) you get in trouble if you have not null requires or IS_IN_DB or other validators)... So I use an other form to catch the number of input the user is needing and I pass it buy the URL as a vars something like that :

URL(c='controller', f='bunch_func', vars=dict(nb_inputs=3))

Then I use it in range function :

for i in range(0, int(request.vars.nb_inputs))

Is there another more easy way to do this ?

For example why not to have an option before validate the posted data to delete emtpy row - fields of the form and post only row - fields with no empty values ???

I have already try a lot of things but nothing is working. 

The batch input form is usefull and i think must be an easy way for this.

The above is working but is not so practical for the end user of the batch input form.

[ksotiris]

Hello web2py-users,  I am new to web2py framework. I have a question about the followings: 

Notice, if you create more inputs then the number your users will need (not 3 records for instance) you get in trouble if you have not null requires or IS_IN_DB or other validators)... So I use an other form to catch the number of input the user is needing and I pass it buy the URL as a vars something like that :

URL(c='controller', f='bunch_func', vars=dict(nb_inputs=3))

Then I use it in range function :

for i in range(0, int(request.vars.nb_inputs))

 

Why not to have an option before validate the posted data to delete emtpy row - fields of the form and post only row - fields with no empty values ???

I have already try a lot of things but nothing is working. 

The batch input form is usefull and i think must be an easy way to do this.

The above solution (2 forms) is a solution but is not so practical for end user of batch input form.

[Richard Vézina]

That an option... The other option is to wrap IS_IN_DB() into IS_EMPTY_OR()... But it is not garanti that the concerning field will be filled...

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)

[Ramkrishan Bhatt]

Hi All,

  My question is about expanding form on dynamically for example. 

we have two tables. company and address 

now did SQLFORM.factory(db.company,db.address)

Now requirement is says that where company can have multiple address so i need a button for add more address. On click we need to append form. 

Can anybody provide the specific solutions. 

[Richard Vézina]

This should do what you want : https://groups.google.com/d/msg/web2py/oiyOIC0IH04/6-NvVOJECU8J

I remember having post this solution somewhere else (other thread) and other web2py users had posting many other solution but can find it back... You have at least this solution that work very well and is completly web2py base (server side) and not imply ajax calback or ajax insertion (with the validation that it requires beyound the scene).

Other references :

https://groups.google.com/d/msg/web2py-developers/thQiiC3-2LQ/s8AiuSpUmh0J

Richard

For more options, visit https://groups.google.com/d/optout.

[Sujata Aghor]

I am also searching for -

"How to insert multiple rows on single form submit in web2py"

Anyone please please help me.
Any clue will help!!!
Thanks in advance!!!

[黄祥]

just an idea, why not use web2py component?
example 1
first component for add the details using sqlform.factory or whatever and store it on sessions
second component is for modifying the sessions (in case you want to edit the detail e.g. quantity, price, etc), the submit to insert in database can goes here or another page in case you want to have seperate page for header (date, customer, notes, etc)

example 2
first component for add the details using sqlform.factory or whatever and store it on sessions
second component is for modifying the sessions (in case you want to edit the detail e.g. quantity, price, etc)
third component for input header (date, customer, notes, etc) and insert if form has no error to database (header and detail)

best regards,
stifan

[Richard Vézina]

The basic concept of what you need is presented here : https://groups.google.com/d/msg/web2py/oiyOIC0IH04/6-NvVOJECU8J

You just have to adapt it... Add more field and change field/table names, etc.

But it does basically what you need...

Richard

On Wed, Mar 9, 2016 at 5:04 PM, Yoel Baez <jbae...@gmail.com> wrote:

Thanks dude,

Yoel

2016-03-09 18:01 GMT-04:00 Richard Vézina <ml.richa...@gmail.com>:

I think I had publish something in the past that address your requirement...

search for inline form or something... I don't have time to search now...

Can have a look tomorrow what can I share...

On Wed, Mar 9, 2016 at 11:22 AM, Yoel Baez <jbae...@gmail.com> wrote:

Hey Richard,

I need to do something like this... 

can you help me with a sample code, please?

Regards,

Yoel

--

Saludos,

YB