python - 为什么我的 mysql 数据库没有更新(使用 flask/python)

Question

我目前正在尝试在用户输入用户名和密码时将用户添加到数据库中。但是，数据库根本没有更新，数据库中的所有信息都保持不变。我已经测试了代码并且它确实运行了但是 mysql.connect().commit() 没有将代码提交到数据库。

我的 flask 文件:

from flask import Flask, jsonify, render_template, request, Response, json, redirect, url_for
from flaskext.mysql import MySQL
import re
from MyFunction import *
from get_tv_name import *

mysql = MySQL()
app = Flask(__name__, static_folder="", static_url_path='')
app.config['SECRET_KEY'] = 'F34TF$($e34D';
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ""
app.config['MYSQL_DATABASE_DB'] = 'accounts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/register.html/', methods=['GET', 'POST'])
def register():
    error = None
    if request.method == 'POST':
        cursor = mysql.connect().cursor()
        cursor.execute("SELECT * from _accounts where Username='" + request.form['username'] + "' and Password='" + request.form['password'] + "'")
        data = cursor.fetchone()
        if data == None:
            cursor.execute("INSERT INTO _accounts VALUES (null,  '" + request.form['username'] + "', '" + request.form['password'] + "')")
            mysql.connect().commit
            redirect(url_for('index'))
        else:
            error = "it is complete"
return render_template('/register.html/', error=error)

if __name__ == '__main__':
    app.run(port=8080, debug=True)

我的 html:

<div class="container">
<div class="row">
    <div class="col-md-6 centered">
        <div class="panel panel-default">
            <div class="panel-heading"> <strong class="">Create an Account!</strong>

            </div>
            <div class="panel-body">
                <form class="form-horizontal" role="form" action="" method="post">
                    <div class="form-group">
                        <label for="inputFirstName3" class="col-sm-3 control-label">First Name</label>
                        <div class="col-sm-9">
                            <input type="email" class="form-control" id="inputEmail3" placeholder="First Name" required="">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="inputLastName3" class="col-sm-3 control-label">Last Name</label>
                        <div class="col-sm-9">
                            <input type="email" class="form-control" id="inputEmail3" placeholder="Last Name" required="">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="inputEmail3" class="col-sm-3 control-label">Username</label>
                        <div class="col-sm-9">
                            <input type="text" required="" placeholder="Username" class="form-control" name="username" value="{{
      request.form.username }}">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="inputPassword3" class="col-sm-3 control-label">Password</label>
                        <div class="col-sm-9">
                           <input type="password" class="form-control" required="" placeholder="Password" name="password" value="{{
      request.form.password }}">
                        </div>
                    </div>
                    <div class="form-group last">
                        <div class="col-md-offset-3 col-md-3">
                            <button type="submit" class="btn btn-success btn-sm">Create account</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

Answer 1

数据库没有更新的原因是您没有提交事务。您使用 mysql.conn() 创建连接，然后通过添加 .cursor() 从中获取游标。当您提交更改时，您会通过再次调用 mysql.conn() 获得一个全新的连接。

您要做的是捕获对连接的引用，以便稍后使用。您还需要使用参数化查询来避免诸如 SQL 注入(inject)攻击之类的事情。

conn = mysql.connect()
cursor = conn.cursor()

data = cursor.fetchone()
if data is None:
    cursor.execute("INSERT INTO _accounts VALUES (null, %s, %s)", (request.form['username'], request.form['password']))
    conn.commit()
    redirect(url_for('index'))  # I'm guessing you want to put a return here.
else:
    error = "it is complete"    # snip

最后，在与 None 进行比较时，您需要使用 is 运算符而不是相等性。 From PEP8

Comparisons to singletons like None should always be done with is or is not, never the equality operators.