Create a Variable from SQL query in Python

Question

I'm trying to create a very basic login. To do this I need to store the "id" that matches a "username" in my "users" table. It will only give me back a list though no matter what I've tried.

I'm following an online course and I can't use most SQLAlchemy shortcuts--just good old fashioned SQL commands. I'm using Python 3 and Flask to build it out.

@app.route("/login", methods=["GET","POST"])

def login():
if request.method == "GET":
    return render_template("login.html")

if request.method == "POST":
    #find user in users table
    username = request.form["username"]
    rows = db.execute("SELECT id FROM users WHERE username=:username",{"username": username}).fetchall()
    session["user_id"] = rows.id
    return render_template("success.html", rows=rows)

if the id is 1 it literally returns: '(1,)'

You could try using fetchval() instead of fetchall(). (fetchval() is a pyodbc extension to the DB API.) — Gord Thompson
– Gord Thompson, Commented Mar 6, 2018 at 0:10

Simeon Ikudabo · Accepted Answer · 2018-03-06 00:39:30Z

You'll have to use fetchall(). SO for example:

rows = db.execute("""SELECT id FROM users WHERE username='%s'"""
                 %(str(username)))
username_string = rows.fetchall()

#You should have a list dictionary.

If you want to grab the ID from username_string you can do something like this:

user_id = username_string[0]
user_id_final = user_id['id']

What I did here is we created the row variable which is just the result of using the "SELECT" statement within the db.execute function.
We stored the results which should be the id that corresponds with that username (1). We used the fetchall method to grab it. We stored this in a variable called username_string(or you can call it id_string).
We create another variable called user_id, which is set equal to the first index in username_string. That should get you the key/value pair for the id that you are looking for.
The last variable is user_id_final, which stores the value of 'id' in the key/value pair. That should be equal to '1' if that is the ID that corresponds with that username and you should be done.

Collectives™ on Stack Overflow

Create a Variable from SQL query in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related