Dynamic webform to send data to SQL Server

[soumik chaudhuri]

Hello Members,

I'm new to python and web2py. Here, I'm trying to build a dynamic webform, through which users will be able to select a few values from drop-downs and input different values into sql database through multiple input boxes. Right now, I'm trying to insert data into database. While my code does not return any error, it does not store the values in the table at the database  either.

I'm using Python 3.5(32 bit) and sql server 2008. My tables are created at the backend and sql server management studio is able to push data and retrieve from the tables.

I have also tested the sql server connection from python and it is working fine. I have used this to fetch data:

import pyodbc
cnxn=pyodbc.connect('Driver={SQL SERVER}; SERVER=<server name>;DATABASE=<db name>;UID=<user name>:PWD=<password>')
cursor=cnxn.cursor()
cursor.execute("select * from schema.table_name")
row=cursor.fetchall()
for row in rows:
    print (row)


The code I am trying to use to insert data:

#Models - db.py

db=DAL('mssql3//:Driver={SQL SERVER}; SERVER=<server name>;DATABASE=<db name>;UID=<user name>:PWD=<password>'')
db.define_table('User_Input',
    Field('UID',requires=IS_NOT_EMPTY()),
    Field('U_Name',requires=IS_NOT_EMPTY()),
    Field('PID',requires=IS_NOT_EMPTY()),
    Field('P_Name',requires=IS_NOT_EMPTY()))


#Controllers - default.py

def form_user():
    request.vars._formname='db.User_Input'
    form=SQLFORM(db.User_Input)
    if form.accepts(request.vars):
        id=db.User_Input.insert(**db.User_Input._filter_fields(form.vars))
    return dict(form=form)


#Views - default/form_user.html

<h5> User Input Form </h5>
<br/>{{=form}}<br/>


Above code produces output as:

User Input Form


UID
|Input Box|

U Name
|Input Box|

PID
|Input Box|

P Name
|Input Box|

<Submit Button>


Problems:

1. Submit does not submit any value back to the table 'User_Input' at sql server database, and it does not produce any error.

2. I would like to have the input boxes in one row instead of in one column. I'm lost between all the CSS and html files. Could you please show me a way to do it?


Thank you.

[soumik chaudhuri]

Update:

I have modified the code in controller, still I cannot insert into mssql server. I don't understand what is the problem here.

#default.py

def form_user():   

    form=SQLFORM(db.User_Input)
    if form.accepts(request.vars):
        db.User_Input.insert(**db.User_Input._filter_fields(form.vars))
    return dict(form=form)

I have tried inserting some values into MSSQl database using the below code, through a .py file. And it inserts the data.

cursor=cnxn.cursor()
cursor.execute("Insert into ....")
cnxn.commit()

Thanks in advance.