Scroll through a Database

Examine the parts of our code from the previous lesson that pull each row and column data:

rows[0][1]
rows[0][2]
rows[0][3]
rows[0][4]

What we need to do to get the next record is to change the 0 into a 1. The column numbers can stay the same:

rows[1][1]
rows[1][2]
rows[1][3]
rows[1][4]

All we need to do is to set up a variable and increment (add 1 to) it when the forward button is clicked, or decrement (take 1 away from) it when the back button is clicked. The variable can go in place of the row number:

rows[row_counter][1]

Whatever number is currently stored in row_counter will be used to pull a row from the table. To get started, add the row_counter variable near the top of your code with the other variables. Set it to 0:

file_name = "default.png"
path = db_config.PHOTO_DIRECTORY + file_name
rows = None
num_of_rows = None
row_counter = 0

We'll add a function to scroll forward, first.

Set up a function called scroll_forward. We need to access the row_counter variable and num_of_rows. Make them global:

def scroll_forward():

global row_counter
global num_of_rows

What we need to do with this function is test if the row counter variable is greater than the number of rows in our table. The row counter variable will get incremented. If it's higher than the number of rows in the table then the program will crash. We have to stop this from happening.

Add these lines to your function:

if row_counter >= (num_of_rows - 1):

messagebox.showinfo("Database Error", "End of database")

Our if part is this:

if row_counter >= (num_of_rows - 1):

We got the number of rows when we loaded the database. The rows in a database table start at 0, however. So we have to deduct 1 from the num_of_rows variable. That's because the number of rows might be 4, but they go from 0 to 3. You don't want the row_counter to get up to the num_of_rows. If you did, your program would crash.

But we just display a message if we've reached the end of the database.

We can add an else part to our if statement. If it's not the end of the database, it means we can display a new record. Add this to your if statement:

else:

row_counter = row_counter + 1
fName.set(rows[row_counter][1])
fam.set(rows[row_counter][2])
job.set(rows[row_counter][3])

The first thing we do is to increment the row_counter. It starts out at 0. We've already got the first record in the table. We need the records from 0. Which is why we increment row_counter now. The other three lines do what we did before: load the data into the Entry textboxes using the set method.

Your function should look like this:

A Python function to scroll forward through database table records

We haven't loaded a photo, though. We can do that in a try … except block. That way, if the photo can't be found, we can load the default instead. (Of course, this assumes that the default photo can be found.)

Add this just below the job line, on the same indent:

try:

ph_path = db_config.PHOTO_DIRECTORY + rows[row_counter][4]
load_photo_tab_one(ph_path)

except FileNotFoundError:

load_photo_tab_one(db_config.PHOTO_DIRECTORY + "default.png")

The first two lines do exactly the same thing we did before: get the photo directory and the image name, then call load_photo_tab_one. The except part tries to catch a FileNotFoundError error.

Your function should look like this:

Python code to load an image from a directory

If there are rows to display, we try to load the information into the text boxes and label. That's all the function does. We need to call it, though.

Scroll down and locate your buttonForward line. We can add a command as a parameter:

buttonForward = tk.Button(tab1, text="Forward", command=scroll_forward)

Add the parts in bold above. When the button is clicked, the scroll_forward function will be called.

Try it out. When your form displays, click your forward button. You should see the next record appear

Tkinter database form

(If you get the default image, make sure you have copied over the images you downloaded to the correct directory.)

Keep clicking the forward button. You should hit the error message:

An End of Database error message

In the next lesson, you'll learn how to scroll back through the database table.

Scroll Back through a Database >