FLASK SQlite Python - Checking if username is already in database

Question

Hey can anyone tell me what I'm doing wrong in my code? I want to check whether an username is already in the database or not.

Whole Route. It wont give me any errors but also wont fill my database.

@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

Make one function, who check username and email is already in database or not.

#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 2

First, I think I have a working code sample for the original problem. However, I think you can solve this duplicate user problem better using constraints within the database. See the bottom of my answer.

First let's check the current code. There are a couple of issues I can see here:

1. Incorrect indent for try/finally means there's no connection active during the try/finally.
2. The way you're checking for whether there's an entry with the username isn't correct.

Indent

In the current code, the try/finally block at line 6 needs to be indented one further in order to be able to use the connection established by the connection made in the with statement on line 4.

As the code currently stands, the connection will have been closed by the time it's used, so all database access will fail.

Checking for a user

The code used to check for a user will fail, throwing an exception which will cause the finally to be hit and the rollback to be executed. The return value from execute throws an exception when you call len, whether or not there are any entries.

Here's what I get from a python shell to show what I mean:

>>> 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()

Instead, to check whether a select statement returns a value, use fetchone and see if it returns None:

# No users named `fred`:
>>> cur.execute("select * from people where name_last=:who", {"who": "fred"}) 
<sqlite3.Cursor object at 0x10bbd0180>
>>> cur.fetchone() is None
True

# At least one user named `mike`:
>>> cur.execute("select * from people where name_last=:who", {"who": "mike"})
<sqlite3.Cursor object at 0x10bbd0180>
>>> cur.fetchone() is None
False

I think therefore something like this might work:

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 ...

Alternative approach

A more robust approach would be to let the database take responsibility for preventing duplicate users.

Make the name column unique when creating the table. Then inserting a record with the same username will throw an exception. Taking an example from the 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 3

this code connects to database.db twice at lines 1 and 9. Probably this is not what was initially intended