UnboundLocalError: cannot access local variable 'insert_query' where it is not associated with a value

I’m making a login page code on Python (with Tkinter) that creates a database table where the users’ id and the time they logged in are registered whenever a user succesfully logs in. However, everytime I run it, I keep getting the same error, and I’m not sure if it’s because I’m using “Try Except” incorrectly or the way I’m inserting the foreign primery key in cur.execute(insert_query, …) Here’s the block of code of the .py file that I worked on where I’m encountering the most issues:

def login():
    if idEntry.get() == '' or passwdEntry.get() == '':
        messagebox.showerror('Alert', 'Please enter all entry fields!')
    else:
        db = pymysql.connect(host='127.0.0.1', user='root', password='password', database='testP')
        cur = db.cursor()
        query = 'select * from personaldata where passwrd=%s'
        cur.execute(query, (passwdEntry.get(),))
        roles = cur.fetchone()

        if roles == None:
            messagebox.showerror('Alert!', 'Incorrect username or password')
            return
        
        else:
            try:
                queryActi = 'use testP'
                cur.execute(queryActi)
                queryActi='create table if not exists Registro_Actividad (userId INT PRIMARY KEY NOT NULL,fecha DATETIME,FOREIGN KEY (userId) REFERENCES personaldata(id)) ' \
                
                cur.execute(queryActi)
            except: 
                loginTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                cur.execute('use testP')
                insert_query = 'insert into Registro_Actividad (userId, fecha) VALUES (%s, %s)'
                last_id = cur.lastrowid
            cur.execute(insert_query, (last_id,) + loginTime)
            db.commit()
            db.close
            messagebox.showinfo('success', 'Login Successful')
            # clear screen
            idEntry.delete(0, END)
            passwdEntry.delete(0, END)
            main_running = False
            for proc in psutil.process_iter():
                if 'Mainwin.py' in proc.name():
                    main_running = True
                    break

            if not main_running:
                subprocess.Popen(['python', 'Mainwin.py'])
                windows.destroy()

Note that this is not the full code of the file. This is just a part of the code where I’m having the most problem. Any advice?

The spot where you use insert_query comes after the try except construct where you define insert_query only in the except branch. Perhaps this statement is meant only for the except branch, and thus should be indented one more level?

Hopefully you’re aware of this already, but you published your MySQL root password here. I strongly recommend changing it immediately.

Here’s the part that’s causing you issues though:

            try:
                queryActi = 'use testP'
                cur.execute(queryActi)
                queryActi='create table if not exists Registro_Actividad (userId INT PRIMARY KEY NOT NULL,fecha DATETIME,FOREIGN KEY (userId) REFERENCES personaldata(id)) ' \
                
                cur.execute(queryActi)
            except: 
                loginTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                cur.execute('use testP')
                insert_query = 'insert into Registro_Actividad (userId, fecha) VALUES (%s, %s)'
                last_id = cur.lastrowid
            cur.execute(insert_query, (last_id,) + loginTime)

If any sort of exception happens during the queryActi - ANY exception whatsoever - you eat that, and set insert_query. However, you then attempt to execute insert_query regardless of the exception status. So if there’s NO exception, you attempt to execute a query from a variable you you haven’t set. Probably what you want here is to unindent the assignment, but it’s also possible you want to indent the execution.

By the way, this is also likely to be a problem:

db.close

Probably you meant db.close() ?

Debugging your code will be harder due to various inconsistencies, such as whether you cur.execute('use testP') or put use testP into a variable and then execute it. The line continuation backslash on the create table statement isn’t achieving anything as the next line is simply blank. And I’ve no idea what’s going on with the process search for Mainwin.py and if not found, an attempt to run the subprocess and destroy windows, but that also looks quite odd.

Recommendation: Make extensive use of with blocks, and try not to use bare except: clauses anywhere. In this case, I don’t think you want ANY try/except; saying “create table if not exists” is safe if the table does exist, so if any sort of error happens, it doesn’t make sense to carry on with the insertion and then a claim that the login succeeded.

But most importantly, don’t do this:

        query = 'select * from personaldata where passwrd=%s'
        cur.execute(query, (passwdEntry.get(),))

You’re storing your passwords in CLEAR TEXT for anyone to read. This is scary. Don’t do that, not even for a hobby project - because there’s nothing so permanent as a temporary solution. Encrypt your passwords properly, using bcrypt or better. You’re not currently even testing the user ID; maybe some code is missing, but it looks like you test to see if any user has the password you entered, and if so, you let the person log in. Instead, first look up the user and make sure that that is correct, then do a password verification using that user’s stored password hash.

Good luck.

Actually, on second reading: I think neither of these thoughts is correct. It looks like there’s a missing execute AND a missing assignment. This line makes no sense without a previous query:

                last_id = cur.lastrowid

and the only previous query is the “use” which has no useful row ID (so far as I know). So maybe there’s some other lookup that’s missing?

For context, I took reference from this video as I didn’t know how to insert foreign key values:

This is where that code came though I’m starting to wonder if it was necessary or not.

Also, thanks for the heads up, changed inmediately the password.

Nevermind, I finally managed to find a workaround. Here’s what I wrote:

def login():
    if idEntry.get() == '' or passwdEntry.get() == '':
        messagebox.showerror('Alert', 'Please enter all entry fields!')
    else:
        db = pymysql.connect(host='127.0.0.1', user='root', password='password', database='testP')
        cur = db.cursor()
        
        queryActi = 'use testP'
        cur.execute(queryActi)
        queryActi='create table if not exists Actividad (actId int auto_increment primary key not null, userId int,FOREIGN KEY (userId) REFERENCES personaldata(id),fecha DATETIME) ' \

        cur.execute(queryActi)

        query = 'select * from personaldata where passwrd=%s'
        cur.execute(query, (passwdEntry.get(),))
        roles = cur.fetchone()

        if roles == None:
            messagebox.showerror('Alert!', 'Incorrect username or password')
            return


        else:
            login_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            user_id = roles[0] # Assuming the ID is the first column in personaldata table
            insert_query = 'insert into Actividad (userId, fecha) VALUES ( %s, %s)'
            cur.execute(insert_query, (user_id, login_time))
            db.commit()
            messagebox.showinfo('success', 'Login Successful')
            # clear screen
            idEntry.delete(0, END)
            passwdEntry.delete(0, END)
            main_running = False
            for proc in psutil.process_iter():
                if 'Mainwin.py' in proc.name():
                    main_running = True
                    break

            if not main_running:
                # Launch the PyQt main page script using subprocess
                subprocess.Popen(['python', 'Mainwin.py'])
                # Close the original tkinter login window
                windows.destroy()

Should be:

login_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

else, you get an error.

I see. What’s the difference between datetime.now and datetime.datetime.now?

It may or may not be an error.

If you have import datetime then you need datetime.datetime.now(), but if you have from datetime import datetime then datetime.now() will work. That’s because it’s the datetime class that has the method .now(); the datetime module doesn’t have a now() function.

It could be a little confusing that the module is called datetime and that it has an identically-named class called datetime rather than, say, DateTime.

1 Like

Ahh, yes, @MRAB is correct. I was assuming you had import datetime at the top of your module.

Here are the different options:

from datetime import datetime

login_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

or

import datetime

login_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

or if you wish to use an alias

import datetime as dt  

login_time = dt.datetime.now().strftime('%Y-%m-%d %H:%M:%S')