Unable to insert data to the database but it returns success every time i run it

Question

I want to insert data, which i found by iterating using for loop in jinja template, to a table. when i submit the form returns SUCCESS but nothing is registered on the database.

@app.route('/submit', methods=['GET','POST'])
def submit():
    if request.method == 'POST':
        cur = connection.cursor()
        userDetails = request.form
        oracle_id = userDetails.getlist('id')
        name = userDetails.getlist('name')
        status = userDetails.getlist('status')
        remark = userDetails.getlist('remark')
        for i, id in enumerate(oracle_id):
            stmt = "insert into attendance( oracle_id, name, status, shift, remark) VALUES(%s, %s, %s, %s, %s)"
            cur.executemany(stmt, (id,oracle_id[i], name[i], status[i], remark[i]))
            cur.commit()
        return 'That is success'
    return render_template('take_attendance.html')

The funny part is once or twice i found the data in my table while testing, but after wards nothing goes there. Is there any error i made here?

The HTML form i used is

<form action="{{ url_for('submit') }}" method="POST" name="attendance">

<table cellpadding="2" width="20%" bgcolor="#F7F7F7" align="center" cellspacing="10">
    <tr>
        <td colspan=2>
        <center><font size=4><p>Attendance</p></font></center>
        </td>
    </tr>
    <tr>
        <td align="center">ID</td>
        <td align="center">Name</td>
        <td align="center">Status</td>
        <td align="center">Remark</td>
    </tr>

    {% for data in userDetails %}
    <tr>
        <td><input type="text" name="oracle_id" id="oracle_id" size="6" value="{{data.oracle_id}}"></td>
        <td><input type="text" name="name" id="name" size="20" value="{{data.name}}"></td>
        <td>
        <select name="status">
            <option value="-1">select..</option>
            <option value="A">1130</option>
            <option value="W" selected>1122</option>
        </select></td>
        <td><textarea rows="1" cols="20" name="remark"></textarea></td>
    </tr>
 {% endfor %}

    <tr>
        <td>
            <input type="reset">
        </td>
        <td colspan="2">
            <input type="submit" value="Submit">
        </td>
    </tr>
</table>

</form>

I was trying for over a week but nothing changed. Thanks in advance for your effort.

Answer 1

Execute many is used if you prepare a sql statement without data and feed it multiple rows of values - you feed it one tuple, not a list of tuples.

@app.route('/submit', methods=['GET','POST'])
def submit():
    if request.method == 'POST':
        cur = connection.cursor()
        userDetails = request.form
        oracle_id = userDetails.getlist('id')
        name = userDetails.getlist('name')
        status = userDetails.getlist('status')
        remark = userDetails.getlist('remark')

        # marry the right data:
        data = list(zip(range(len(oracle_id)),oracle_id,name,status,remark))

        # range(len(oracle_id)) delivers the same ids as your code does
        # wich is most probably **wrong** on many levels...

        stmt = "insert into attendance( oracle_id, name, status, shift, remark) VALUES(%s, %s, %s, %s, %s)"
        cur.executemany(stmt, data) # provide the list of rows to execute many
        cur.commit()
        return 'That is success'
    return render_template('take_attendance.html')

That said, its probably incorrect - you insert as id the index gathered from enumerating(oracle_id) - which will always start at 0 for each new iteration, so you are inserting the same id over different calls to def submit().

Use How to debug small programs to debug your code.