So I'm trying to retrieve information from a database using sqlite3 and python. I've tried to do so with the following code:
username=username_entry.get()
password=password_entry.get()
conn=sqlite3.connect("staff.db")
c=conn.cursor()
c.execute("SELECT * FROM staff WHERE staff_id=? AND staff_password=?", (username, password))
user=c.fetchone()
And then there's additional code after to handle what I've just received but the error I get seems to be originating before I reach that point. The "staff.db" is stored in the same directory as the python code with the table "staff" inside of it but the following error is flagged when I try to run the code in VS Code:
Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Users\lewis\AppData\Local\Programs\Python\Python310\lib\tkinter\__init__.py", line 1921, in __call__
return self.func(*args)
File "d:\CompSciProject\Master File.py", line 17, in login
c.execute("SELECT * FROM staff WHERE staff_id=? AND staff_password=?", (username, password))
sqlite3.OperationalError: no such table: staff
Using the SQLite extension by alexcvzz in visual studio code I can visualise the database and it gives me as follows:
Which is what I expected and should mean it should work as intended, but in VS Code for some reason it doesn't. However, then when I run the exact same code on a IDLE (both VS Code and IDLE are using Python 3.10.2) it works fine and reads the database and works without any errors, does anyone know what's happening with this? Thanks!
EDIT: Manage to find an answer myself, written it in an answer below if anyone else ever needs it! Thanks to anyone who took a look :)
ChatGPT to the rescue apparently! God AI makes me want to quit coding. It appears for some reason that VS Code and IDLE are trying to run the code from different working directory's. This can be checked with the following code:
import os
print(os.getcwd())
Which will print the working directory. If they're different the following code can be used to collect a specific location for the database you want to use to avoid this issue.
import os
dir_path = os.path.dirname(os.path.realpath(__file__))
db_path = os.path.join(dir_path, "staff.db")
conn=sqlite3.connect(db_path)
This finds the codes directory and adds it to the name of the file so that when the connection is made it uses the exact directory rather than leaving it up to python to decide. Why does it have to be so difficult!