CODEWITHSUNDEEP
pythonsqliteflasksqlalchemyflask-sqlalchemy
deloco
deloco

Reputation: 43

From Sqlite3 to Sqlalchemy

i have been developing a flask app with a sqlite3 database. Now to publish it online i am switching the database from sqlite3 to Sqlalchemy and i am very lost. I want to create a users table ("Usuarios") and prevent duplicates on register.

I have already create the table :

class Usuarios(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    hash = db.Column(db.String(120), unique=True, nullable=False)
    provincia = db.Column(db.String(80))
    mail = db.Column(db.String(80), nullable=False)
    def __init__(self, username, hash, provincia, mail):
        self.username = username
        self.hash = hash
        self.provincia = provincia
        self.mail = mail

And i think that in the username column the data should be unique so i set unique=True

This is my python code:

@app.route("/regescuela", methods=["GET", "POST"])
def register():
    session.clear()

    if request.method == "POST":
        if not request.form.get("mail"):
            return apology("No ha introducido el correo electrónico!")
        if "@" not in request.form.get("mail"):
            return apology("No ha introducido un correo electrónico valido!")
        if not request.form.get("username"):
            return apology("No ha introducido un nombre de usuario!")
        elif not request.form.get("password"):
            return apology("No ha introducido una contraseña!")
        elif request.form.get("password") != request.form.get("confirmation"):
            return apology("Las contraseñas no coinciden.")
    else:
        usumayu = request.form.get("username")
        return render_template("regescuela.html")

    nuevaentrada = Usuarios(username = request.form.get("username").upper(), hash = generate_password_hash(request.form.get("password")), provincia = request.form.get("provincia"), mail = request.form.get("mail"))
    db.session.add(nuevaentrada)
    db.session.commit()



    if not nuevaentrada:
        return apology("Este usuario ya existe! Prueba con otro!")

    session["user_id"] = nuevaentrada
    flash("Registrado!")
    return redirect("/")

The problem is that if i register an username with a username already taken it gives me this error: 

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "usuarios_username_key"

Which is logical because i set unique = True. But I don't want to get that Error, i want to return the apology if the username is taken and that is why i added the if not nuevaentrada: return apology() but it doesn't seem to be working and i don't know why. In sqlite3 my code was : 

db.execute("INSERT INTO usuarios (username, hash, provincia, mail) VALUES(:username, :hash, :provincia, :mail)", username=request.form.get("username").upper(),
hash=generate_password_hash(request.form.get("password")), provincia=request.form.get("provincia"), mail=request.form.get("mail"))

and it worked perfectly.

I think the problem is with that if not. What do you think? Thanks in advance

Upvotes: 0

Views: 509

Answers (1)

bgse
bgse

Reputation: 8587

If you examine the stacktrace you are getting carefully, you'll find that your IntegrityError is raised before you are even arriving at your check that is supposed to decide if an apology should be shown.

This isn't surprising, as the database can't perform the insert operation with a duplicate username.

The IntegrityError communicates this, and would be a natural candidate for a try-except, but there isn't really a good clean way to go about it this way.

Just handing over the error message to the user is somewhat problematic:

  • it might be cryptic to someone not familiar with relational databases
  • it might be in a different language than their selected one if your application is multi-lingual
  • it might be a security concern as it could expose information about inner workings of your application
  • probably a host of other concerns I can't think of right now

Parsing the error message is also problematic, since it might be a different message when switching the underlying database, besides an approach like that will lead to messy, hard to maintain code even in the best case scenario.

That being said, I'd suggest you treat this as a business logic concern, and perform the necessary checks this would dictate before the insert attempt.

For example, the duplicate username check could look somewhat like this:

username = request.form.get("username").upper()

...

dup_username = db.session.query(db.exists().where(Usarios.username == username)).scalar()

if dup_username:
    return apology("Este usuario ya existe! Prueba con otro!")

...
# possibly more checks, e.g. email already in use or something    
...

# since we didn't drop out so far with a failed check, we can perform the insert

nuevaentrada = Usuarios(...)
db.session.add(nuevaentrada)
db.session.commit()

This approach gives you better control, as it avoids messy code trying to figure out what happened, and is more robust should you decide to move to another database later, or details in the database adapter implementation change, etc.

Upvotes: 1

Related Questions