[Solved]Help with SQLite Login Form

[Extra]

Hello,

I'm trying to make a login form using Python and SQLite as a database. I am able to match the Name and Password to get a successful login, but I am trying to add a third variable (Privilege) which will take the user to a different menu depending on their privilege status (admin or StandardUser ).

This is what I have so far:

My CreateUserTable.py

import sqlite3

def createDatabase():
        #Create a database (users.db)
        connection = sqlite3.connect("users.db")
        cursor = connection.cursor()

        table = """CREATE TABLE IF NOT EXISTS Users
                (ID INTEGER PRIMARY KEY  AUTOINCREMENT,
                Name           TEXT    NOT NULL,
                Password       INT     NOT NULL,
                Privilege      TEXT    NOT NULL);"""

        #Execute the creation of the table
        cursor.execute(table)
        #print("The database has been created")
        #Commit the changes
        connection.commit()
        #Close the connection
        connection.close() 

What's in my Table right now:

Output:
[('Admin', 1234, 'admin')]

A snippet of my Login Code:

 import sqlite3
        connection = sqlite3.connect("users.db")
        cursor = connection.cursor()
        cursor.execute(f"SELECT Name from users WHERE Name ='{userInputName}' AND Password = '{userInputPassword}';")
        connection.commit()
        if not cursor.fetchone():  # An empty result evaluates to False.
            print("Login failed")
        else:
            print("Welcome")
        
        #Close the connection
        connection.close()

I need to grab the Privilege column from the table and use it to determine which menu it will take the user to.

If the user is an admin call AdminMenu()
else if the user is a StandardUser call MainMenu()
else call CredentialsError()

Any way I can do this?

Thanks in advance.

[menator01]

Add privilege to your select statement then show the correct menu occordding to the returned result.

[Extra]

Add privilege to your select statement then show the correct menu occordding to the returned result.

Ok, I got that part.
(This is what I have)

        #----------------------------------
        import sqlite3
        connection = sqlite3.connect("users.db")
        cursor = connection.cursor()
                
        # cursor.execute('''
        #         insert into Users (Name, Password, Privilege)
        #         values ('Admin',1234,'admins')
        #         ''')
        cursor.execute("SELECT*FROM Users")
        print(cursor.fetchall())
        
        cursor.execute(f"SELECT Privilege from Users WHERE Name ='{userInputName}' AND Password = '{userInputPassword}';")
        connection.commit()
        privilege = cursor.fetchone()
        print("Result: ",privilege)

        if privilege == "admins":
            print("Hello admin User")
        elif privilege == "user":
            print("Hello standard user")
        else:
            print("Try again")
        
        #Close the connection
        connection.close()
        #----------------------------------

This is what it outputs if I type in Name:Bob, & Password:1234:

Output:
[(1, 'Admin', 1234, 'admins'), (2, 'Admin', 1234, 'admins'), (3, 'Bob', 1234, 'user')]
Result:  ('user',)
Try again

Why doesn't it print("Hello standard user")?

Thanks in advance.

[deanhystad]

('user', ) is a tuple. A tuple will never equal a string.

[Extra]

('user', ) is a tuple. A tuple will never equal a string.

So how do I make it a string?
Is there a way to convert tuples to strings?

[Extra]

Never mind.

Used str.join()

        privilege1 = cursor.fetchone()
        print("Result: ",privilege1)

        privilege = ''.join(privilege1)

Thanks for the help.

[ndc85430]

You shouldn't be using f-strings to use variables in your queries (line 13 above). Instead, you should be using the driver's placeholders - see https://docs.python.org/3/library/sqlite3.html, specifically from "Instead, use the DB-API’s parameter substitution".

[deanhystad]

Join is wrong. A tuple is like a list. Use indexing to get the values.