SQLite:编辑帐户

Question

我试图根据用户id编辑一个用户帐户。我可以从数据库中检索用户数据，但是在单击“保存”按钮后，尝试将数据更新到数据库时出现了问题。我不知道为什么会出现这个问题。我能知道我在哪里搞错了吗？

app.py中的sql语句

@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 class="text-center fw-bold fs-2 mb-4 p-0 m-0">Edit Account</h2>
          {% for user in users %}
            <div class="input-group justify-content-between">
                
                <div class="form-floating mb-4" style="min-width: 230px;">
                    <label for="firstName">First name</label>
                    <input id="first_name" name="first_name" class="form-control" placeholder="John" value= "{{ user[1] }}"  />
                    {% if form.first_name.errors %}
                        <ul class="errors text-danger">
                        {% for error in form.first_name.errors %}
                            <li>{{ error }}</li>
                        {% endfor %}
                        </ul>
                    {% endif %}
                </div>

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

            </div>

            <div class="form-floating mb-4">
                <label for="email">Email address</label>
                <input id="email" name="email" class="form-control" type="email" placeholder="name@example.com" value= "{{ user[3] }}" />
                {% if form.email.errors %}
                    <ul class="errors text-danger">
                    {% for error in form.email.errors %}
                        <li>{{ error }}</li>
                    {% endfor %}
                    </ul>
                {% endif %}
            </div>

            <div class="form-floating mb-4">
                <label for="password">Password</label>
                <input id="password" name="password" class="form-control" type="Password" placeholder="Enter your password" />
                {% if form.password.errors %}
                    <ul class="errors text-danger">
                    {% for error in form.password.errors %}
                        <li>{{ error }}</li>
                    {% endfor %}
                    </ul>
                {% endif %}
            </div>

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

[

​

这是处理update sql语句时发生的问题。

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 -

输出的()行

​

Answer 1

错误消息表示不支持参数4的类型。计数以0开始，所以我们讨论的是id。调试输出显示id包含单项python元组 (2,)。

这对于select语句来说没有问题，因为它可以同时使用单个值和元组来传递一个参数。但是，如果您传递的不仅仅是id，比如update语句，那么您将得到一个包含tuple的元组。

请检查您如何设置session['id']。如果您只想传递一个id，那么就不要传递一个元组。如果您想传递一个元组，那么在将第一个值id[0]传递给update语句时获得它！