Title: Display data for a selected record from a MariaDB database in Python

[When you select a book from the list, the program displays that book's details]

My previous post Populate a listbox from a MariaDB database in Python explained how you can populate a listbox with the results of a query. This example shows how you can display the detail for a record selected from that list.

Building the User Interface

The program uses the following code to build its use interface.

def build_ui(self): '''Build the tkinter widgets.''' label = tk.Label(self.window, text=f'{table_name} Records:') label.pack(side=tk.TOP, anchor=tk.W, padx=5, pady=(5,0)) # Set exportselection=False to make the Listbox keep # its selection when the focus moves off of it. self.books_list = tk.Listbox(self.window, height=5, exportselection=False) self.books_list.pack(side=tk.TOP, expand=True, fill=tk.BOTH, padx=5, pady=(0,5)) self.books_list.bind('<>', self.show_record) # Make fields to display the selected record. record_frame = tk.Frame(self.window) record_frame.pack(side=tk.BOTTOM, anchor=tk.W, fill=tk.BOTH, padx=5, pady=(0,5)) field_names = ('Title', 'Year', 'ISBN', 'Publisher', 'Pages', 'URL') self.fields = {} label_width = 8 for name in field_names: frame = tk.Frame(record_frame) frame.pack(side=tk.TOP, anchor=tk.W, fill=tk.X) label = tk.Label(frame, text=f'{name}:', width=label_width, anchor=tk.W) label.pack(side=tk.LEFT) entry = tk.Entry(frame, state='readonly') entry.pack(side=tk.LEFT, expand=True, fill=tk.X) self.fields[name] = entry # Save the widget. # Bind the URL entry widget. entry = self.fields['URL'] entry.bind('', self.open_url) entry.config(cursor='hand2')

This code creates a label and then the program's listbox. Note that it sets exportselection=False. If you don't do that, then whenever focus leaves the listbox, it forgets its selection and no longer displays that selection as highlighted. (Which all seems like a pretty stupid default behavior, but no one asked me.) After creating the listbox, the code binds its selection event to the show_record method (described shortly).

Next, the code creates a frame to hold all of the record's detail. It then builds a list of the record's field names and makes an empty self.fields dictionary.

The code then loops through the field names. For each field, the program creates a frame to hold that field's label and entry widget, and then it creates those widgets. Notice that the entry widget is readonly. The loop finishes by saving the entry widget in the self.fields dictionary using the field's name as the key.

After it finishes building the record display widgets, the code gets the URL entry widget from the self.fields dictionary, binds its left mouse down event to the open_url method (shown shortly), and sets its cursor to a hand.

After this method finishes building the user interface, the main program calls the populate_list function described in the previous post to fill its listbox with book titles.

Displaying Records

When you select an item from the listbox, the following code executes.

def show_record(self, event): '''Display the selected record's values.''' # Clear any previous values. self.clear_fields() # Display the selected record's values. listbox = event.widget selection = listbox.curselection() if selection: index = selection[0] data = listbox.get(index) # Get the selected record. with mariadb.connect(**user_info) as conn: with conn.cursor(dictionary=True) as cur: # Fetch the data. query = f'SELECT * FROM {table_name} WHERE Title=?' params = (data,) cur.execute(query, params) row = cur.fetchone() # Display the results. for key, value in row.items(): set_readonly_value(self.fields[key], value)

This code first calls the clear_fields method (described later) to clear all of the display widgets. It then gets the listbox's current selection.

If the selection exists, the code executes the SQL query SELECT * FROM Books WHERE Title=?. (The variable table_name is set to Books earlier in the program.) The params tuple holds parameters that should be matched with ? placeholders in the query. In this example, the single parameter is the value selected in the listbox. More generally, using parameters instead of building the value right into the query is safer because it prevents dangerous values from being misinterpreted, for example, in an SQL injection attack.

The code then executes the query and uses fetchone to retrieve the query's single result. The fetchone method returns a dictionary where keys are field names and values are the field values. The code loops through the keys and values. It uses the key to fetch the corresponding entry widget from the self.fields dictionary and calls set_readonly_value (described next) to display the field's value in that entry widget.

Setting Read-Only Values

The program's record detail entry widgets are all readonly. That prevents the user from modifying their contents, but it also prevents the program from modifying their contents, too.

<rant>That was another weird decision but, again, no one asked me. If preventing the program from changing the value was so important (it isn't), they could have at least provided a separate method to do so that made it obvious that you meant to do this.</rant>

Anyway, the following function lets the program change the value displayed in a read-only entry widget.

def set_readonly_value(entry, value): '''Set this read-only field's value.''' entry.config(state='normal') entry.delete(0, tk.END) entry.insert(0, value) entry.config(state='readonly')

This function first sets the widget's state to normal. It clears the widget, inserts the value into it, and sets its state back to readnly.

The following clear_fields method uses this function to clear all of the program's display widgets.

def clear_fields(self): '''Clear all of the display fields.''' for field in self.fields.values(): set_readonly_value(field, '')

Recall that self.fields is a dictionary holding the entry widgets that the program uses to display the selected record. This method loops through those widgets and uses set_readonly_value to make each hold a blank string.

Opening URLs

The final new piece of the program is the following open_url method.

import webbrowser ... def open_url(self, event): '''Navigate to the current URL.''' entry = self.fields['URL'] url = entry.get().strip() if url: webbrowser.open(url)

When you click the read-only URL entry widget, this method gets that widget from the self.fields dictionary. It then strips whitespace from that value and, if there's anything left, it calls webbrowser.open to open the URL in the system's default browser.

Conclusion

That's about it for this program. When you select a record, the program executes the SQL query to fetch the data for the listbox's selection. It loops through the returned fields and displays their values in the read-only entry widgets.

There are many ways you could modify this example. For example, instead of hard-coding in the list of fields, you could have the program query the database to figure out what fields are in the Books table. You could also use different widget types for different field data types. For example, you might display fields with relational constraints in combo boxes, limit the length of inputs for fields with a certain length, or use some kind of numeric widget for numeric values. All of that seems like a lot of work, however, so I won't unless someone really needs that.

Download the example to experiment with it and to see additional details.