FLASK-SQlite Python检查用户名是否已在数据库中

Question

嘿，有人能告诉我我的代码哪里做错了吗？我想检查数据库中是否有用户名。在

整个路线。它不会给我任何错误，但也不会填充我的数据库。在

@app.route('/regist', methods=['GET', 'POST'])
def regist():
    if request.method == "POST":
        with sql.connect("database.db") as con:
            cur = con.cursor()
        try:
            # flash("register attempted")

            username = request.form['username']
            password = request.form['password']
            passwordencr = request.form['password']
            email = request.form['email']

            x = cur.execute("SELECT * FROM users WHERE name = ?", (username))

            if int(len(x)) > 0:
                flash("That username is already taken, please choose another")
                return render_template('register.html')
            else:
                cur.execute("INSERT INTO users (name,password,email) VALUES (?,?,?)",(username,passwordencr,email) )

                con.commit()
                flash ("Successfully registrated")
        except:
            con.rollback()
            msg = "error in insert operation"

        finally:
            session['logged_in'] = True
            session['username'] = username
            gc.collect()
            msg = Message('Hello', sender='yourId@gmail.com', recipients=[email])
            msg.body = "your username for ak047 is: %s and your password is %s" %(username,password)
            mail.send(msg)
            return render_template("home.html", msg=msg)
            con.close()
            gc.collect()

Answer 1

首先，我想我有一个原始问题的工作代码示例。但是，我认为使用数据库中的约束可以更好地解决这个重复用户问题。看我答案的底部。在

首先让我们检查一下当前的代码。我在这里可以看到几个问题：

1. try/finally的不正确缩进意味着在try/finally期间没有活动的连接。在
2. 检查是否有用户名为的条目的方式不正确。在

缩进

在当前代码中，第6行的try/finally块需要再缩进一个，以便能够使用第4行with语句中建立的连接。在

由于目前的代码，连接在使用时已经关闭，因此所有数据库访问都将失败。在

正在检查用户

用于检查用户的代码将失败，引发异常，导致finally命中并执行rollback。无论是否有任何条目，execute的返回值在调用len时抛出异常。在

下面是我从python shell获得的信息，以说明我的意思：

>>> int(len(cur.execute("select * from people where name_last=:who", {"who": "mike"})))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: object of type 'sqlite3.Cursor' has no len()

相反，要检查select语句是否返回值，请使用fetchone并查看它是否返回None：

^{pr2}$

因此，我认为这样的方法可能会奏效：

def regist():
    if request.method == "POST":
        with sql.connect("database.db") as con:
            cur = con.cursor()
            try:
                # ... Collecting form info ...

                cur.execute("SELECT * FROM users WHERE name = ?", (username))

                if cur.fetchone() is not None:
                    flash("That username is already taken...")
                    return render_template('register.html')
                else:
                    cur.execute("INSERT INTO users (name,password,email) VALUES (?,?,?)",(username,passwordencr,email) )
                    con.commit()
                    flash (...)
             except:
                 con.rollback()

             finally:
                 session['logged_in'] = True
                 session['username'] = username
                 # ... mailing code ...

替代方法

让用户承担更多的责任来防止数据库重复。在

创建表时生成name列unique。然后插入一个具有相同用户名的记录将引发一个异常。以sqlite3 docs为例：

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print "couldn't add Joe twice"

Answer 2

做一个函数，谁检查用户名和电子邮件是否已经在数据库中。在

#Models
class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(15), unique=True, nullable=False)
    email = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(120), unique=True, nullable=False)
    created_on = db.Column(db.DateTime, server_default=db.func.now())
    updated_on = db.Column(db.DateTime, server_default=db.func.now(), server_onupdate=db.func.now())
    tasks = db.relationship('Task', backref='author', lazy='dynamic')

    @classmethod
    def is_user_name_taken(cls, username):
      return db.session.query(db.exists().where(User.username==username)).scalar()

    @classmethod
    def is_email_taken(cls, email):
      return db.session.query(db.exists().where(User.email==email)).scalar()

    def __repr__(self):
        return '<User %r>' % (self.username)

#User Signup Api
@app.route('/todo/api/v1.0/signup', methods=['POST'])
def signup():

    if 'username' not in request.json:
        return jsonify({'username': 'must include username'})
    if 'email' not in request.json:
        return jsonify({'email': 'must include email'})
    if 'password' not in request.json:
        return jsonify({'password' : 'must include password' })

    if User.is_user_name_taken(request.json['username']):
         return jsonify({'username': 'This username is already taken!'}), 409
    if User.is_email_taken(request.json['email']):
         return jsonify({'email': 'This email is already taken!'}), 409

    if request.json :
        hashed_password = generate_password_hash(request.json['password'], method='sha256')
        new_user = User(username=request.json['username'], email=request.json['email'], password=hashed_password)
        db.session.add(new_user)
        db.session.commit()
        return jsonify({'user': 'user created successfully'}), 201
    return jsonify({'username': 'must include username', 
            'password': 'must include password', 
            'email' : 'must include email' })

Answer 3

这段代码在第1行和第9行连接到database.db两次。可能这并不是最初的目的