I'm trying to edit an user account based on the user id. I'm able to retrieve the user data from the database but there is a problem occurred when trying to update the data into the database after clicking the save button. I have no idea why the problem will occurred. May I know where did I make mistake?

sql statement in app.py

@app.route('/edit',methods = ['GET','POST'])
def edit():
    id = session['id']
    conn = get_db_connection()
    users = conn.execute('SELECT * FROM User WHERE id = ?',(id)).fetchall()

    form = EditForm(request.form)
    if request.method == 'POST' and form.validate():
        hashed_pw = generate_password_hash(form.password.data,"sha256")
        print(form.first_name.data, form.last_name.data, form.email.data, hashed_pw,  id)
        conn.execute('UPDATE User SET first_name = ?,last_name = ?,email = ?, password = ? WHERE id = ?',(form.first_name.data, form.last_name.data, form.email.data, hashed_pw,  id))
        conn.commit()
        conn.close()
        message = "Data has been modify successfully"
        flash(message,'edited')
        return redirect(url_for('edit'))
    return render_template('edit.html',users = users, form = form)

edit.html

<form action="/edit" method="POST">
        <div>
            <h2 >Edit Account</h2>
          {% for user in users %}
            <div >
                
                <div  style="min-width: 230px;">
                    <label for="firstName">First name</label>
                    <input id="first_name" name="first_name"  placeholder="John" value= "{{ user[1] }}"  />
                    {% if form.first_name.errors %}
                        <ul >
                        {% for error in form.first_name.errors %}
                            <li>{{ error }}</li>
                        {% endfor %}
                        </ul>
                    {% endif %}
                </div>

                <div  style="min-width: 230px;">
                    <label for="lastName">Last name</label>
                    <input id="last_name" name="last_name"  placeholder="Doe" value= "{{ user[2] }}"/>
                    {% if form.last_name.errors %}
                        <ul >
                        {% for error in form.last_name.errors %}
                            <li>{{ error }}</li>
                        {% endfor %}
                        </ul>
                    {% endif %}
                </div>

            </div>

            <div >
                <label for="email">Email address</label>
                <input id="email" name="email"  type="email" placeholder="[email protected]" value= "{{ user[3] }}" />
                {% if form.email.errors %}
                    <ul >
                    {% for error in form.email.errors %}
                        <li>{{ error }}</li>
                    {% endfor %}
                    </ul>
                {% endif %}
            </div>

            <div >
                <label for="password">Password</label>
                <input id="password" name="password"  type="Password" placeholder="Enter your password" />
                {% if form.password.errors %}
                    <ul >
                    {% for error in form.password.errors %}
                        <li>{{ error }}</li>
                    {% endfor %}
                    </ul>
                {% endif %}
            </div>

            <div >
                <label for="passwordConfirmation">Confirm password</label>
                <input id="password_confirmation" name="password_confirmation"  type="password" placeholder="Enter your password again" />
                {% if form.password_confirmation.errors %}
                    <ul >
                    {% for error in form.password_confirmation.errors %}
                        <li>{{ error }}</li>
                    {% endfor %}
                    </ul>
                {% endif %}
            </div>
            
            {% endfor %}

[

This is the problem that occured when processing the update sql statement

Traceback (most recent call last):
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 2525, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 1822, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 1820, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 1796, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "C:\Users\user\Desktop\PhoneBuddy\app.py", line 127, in edit
    conn.execute('UPDATE User SET first_name = ?,last_name = ?,email = ?, password = ? WHERE id = ?',(form.first_name.data, form.last_name.data, form.email.data, hashed_pw,  id))
sqlite3.InterfaceError: Error binding parameter 4 - probably unsupported type.
127.0.0.1 - - [28/Nov/2022 16:29:50] "POST /edit HTTP/1.1" 500 -

Output for the print() line

CodePudding user response：

The error message says that the type of parameter 4 is unsupported. The counting starts with 0, so we are talking about the id. Your debug output shows that id contains the one-item python tuple (2,).

This is no problem for the select-statement where you only pass one parameter, because it can coup with both, single values and tuples. But if you pass more than just id like in your update statement, you get a tuple that contains a tuple.

Please check how you set session['id']. If you want to pass just one id then don't pass a tuple. If you want to pass a tuple then get the first value id[0] when you pass it to the update statement!