Cannot get data from database

Question

not sure why this code isn't working for my library management system. seems like everything should work but every single time I run it I get the error messagebox. Anybody have any ideas?

It's for a library management system coded in pycharm, I've imported everything I need and it works fine for deleting and adding books into the system however it doesn't seem to work with this viewing one. When I run the query select * from bookTable; in heidiSQL it works so that doesn't really seem like the issue. I'm not really sure what to do from here.

def viewBook():

global canvas, cursor, connection, bookTable, app, entry_bookID, entry_booktitle, entry_bookauthor, entry_bookstatus, entry_isbn, entry_phonenumber, entry_issuedate, entry_duedate, entry_returndate
app = Tk()
app.title("Placeholder")
app.minsize(width=400, height=400)
app.geometry("600x500")

host_address = "127.0.0.1"  # "localhost"
db_username = "root"  # db user name
db_password = "placeholder"
db_name = "LibraryDB"
port_num = int(3306)

connection = mariadb.connect(host=host_address, user=db_username,
                             password=db_password,
                             port=port_num,
                             database=db_name,
                             autocommit=False)
cursor = connection.cursor()

# enter table names here
bookTable = "bookTable"

# create the canvas for info
canvas = Canvas(app)
canvas.config(bg="#ff6e40")
canvas.pack(expand=True, fill=BOTH)

# add a heading frame
headingFrame = Frame(app, bg="#FFBB00", bd=5)
headingFrame.place(relx=0.25, rely=0.1, relwidth=0.5, relheight=0.13)
headingLabel = Label(headingFrame, text="View Books", bg="white", fg="black", font=('Courier', 15))
headingLabel.place(relx=0, rely=0, relwidth=1, relheight=1)

# frame for form
LabelFrame = Frame(app, bg="gray")
LabelFrame.place(relx=0.1, rely=0.3, relwidth=0.8, relheight=0.6)

label_topBar = Label(LabelFrame, text="ID                   Title                    Author                     Status")
label_topBar.place(relx=0.05, rely=0.05, relheight=0.04)
y = 0.25

getbooks = "select * from" + bookTable
print(getbooks)

try:
    cursor.execute(getbooks)
    connection.commit()
    connection.close()

    for i in cursor:
        label_viewBooks = Label(LabelFrame, text = (i[0], i[1], i[2], i[3]), bg = 'gray')
        label_viewBooks.place(relx=0.05, rely=y, relheight = 0.04)
        y+=0.1
except:
    messagebox.showinfo("Error", "Failed to get files from database")

# Quit button
QuitButton = Button(app, text="Quit", bg="#f7f1e3", fg="black", command=app.destroy)
QuitButton.place(relx=0.4, rely=0.9, relwidth=0.18, relheight=0.08)

app.mainloop()

Answer 1

i don't know what connector to the database you are using... i have used mysql-connector and in mysql-connector the command to fetch information from the database after selecting * is:

cursor.fetchall()

and only after doing this can you get the information...i'm not sure how it works in other connectors

but try this before your for loop in the try-except statement

try:
    cursor.execute(getbooks)
    connection.commit()
    connection.close()

    for i in cursor.fetchall():
        label_viewBooks = Label(LabelFrame, text = (i[0], i[1], i[2], i[3]), bg = 'gray')
        label_viewBooks.place(relx=0.05, rely=y, relheight = 0.04)
        y+=0.1
except:
    messagebox.showinfo("Error", "Failed to get files from database")

hope it helps!