Is there a way to sort a database so that the order will match an alphabetically sorted list box in Tkinter?

Question

I'm using Python, Tkinter in PyCharm.

This is a movie tracker program. It tracks movies I want to watch and have watched. There is a listbox in a frame on the left that contains the titles of movies. There is a movie frame on the right that contains some info about the movie that is selected in the list box. I want to keep the list box sorted alphabetically. All the movies and info are stored in a SQLite database.

Is there an easy way to keep the database sorted in alphabetical order, or what is the best way to make sure that the movie info on the right is the correct info for the selected movie on the left?

from tkinter import *
import sqlite3


class App(Tk):
    def __init__(self):
        super().__init__()

#   Try to open a database

        try:
            open('moviedb.db')
#            moviecursor = moviesdb.cursor()
            print ("File open")

#       If database won't open create one

        except IOError:
            moviesdb = sqlite3.connect("moviedb.db")
            moviecursor = moviesdb.cursor()
            moviecursor.execute("""CREATE TABLE movies (
                    title text
                    year text
                    length text
                    watched text
                   )""")

            moviesdb.commit()

            print ("file created")



        self.title("My Movies")
        self.iconbitmap("images/moviereel32.ico")

#       Designate hight and width of app

        app_width = 1200
        app_height = 800

        screen_width = self.winfo_screenwidth()
        screen_height = self.winfo_screenheight()
        print("screen width=", screen_width)
        print("screen height =", screen_height)

        x = (screen_width / 2) - (app_width / 2)
        y = (screen_height / 2) - (app_height / 2)
        self.geometry(f"{app_width}x{app_height}+{int(x)}+{int(y)}")
#        self.geometry("1200x800")
        print("x=",x)
        print("y=",y)
        print("")
#       Create menu Bar

        self.menu_bar = MenuBar(self)
        self.config(menu=self.menu_bar)

#       Define a common font

        self.myfont = font = ("Helvetica", 10, "bold")

#        Create Frame for list of movies. Anchored to left side on main window

        self.listframe = ListFrame(self, width=85, height=50, highlightbackground="blue", highlightthickness=3, relief=RAISED)


#       Add temporary movies to list for example purposes
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")
        self.listframe.listbox.insert(END, "The Creature From The Black Lagoon")
        self.listframe.listbox.insert(END, "The God's Must Be Crazy")


        #       Create Movie frame to hold info on selected movie. Anchored to right on main window

        self.movie_frame = MovieFrame(self, width=600, height=800, highlightbackground="red", highlightthickness=3, relief=SUNKEN)

#   Menu Bar Class

class MenuBar(Menu):
    def __init__(self, parent):
        super().__init__(parent)

        file_menu = Menu(self, tearoff=0)
        file_menu.add_command(label="New", command=self.new_command)
        file_menu.add_command(label="Save...", command=self.save_command)

        self.add_cascade(label="File", menu=file_menu)


    def new_command(self):
        print("New command triggered")

    def save_command(self):
        print("Save command triggered")

#   List Frame Class

class ListFrame(Frame):
    def __init__(self, parent, *args, **kwargs):
        super().__init__(parent, *args, **kwargs)

        #   Put frame on left side of window
        self.pack(side=LEFT)

        #   Create scrollbar and listbox

        self.listscrollbar = Scrollbar(self, orient=VERTICAL)
        self.listbox = Listbox(self, width=82, height=50, font=parent.myfont, yscrollcommand=self.listscrollbar.set)
        self.listbox.pack(side=LEFT)

        #   Bind listbox

        #        self.listbox.bind("<<ListboxSelect>>", on_select)

        #   Configure scrollbar
        self.listscrollbar.config(command=self.listbox.yview)
        self.listscrollbar.pack(side=RIGHT, fill=Y)

    def add_movie(self):
        pass

    def update_movie(self):
        pass

    def delete_movie(self):
        pass

    def on_selecttemp(event):
        selected_index = listbox.curselection()
        if selected_index:
            selected_item = self.listbox.get(selected_index)
            print(f"Selected item: {selected_item}")

#   Movie Frame Class

class MovieFrame(Frame):
    def __init__(self, parent, *args, **kwargs):
        super().__init__(parent, *args, **kwargs)

 #       self.pack(side=RIGHT)
        # Create Entry Box in movie frame for title of movie

        self.titletext = Entry(self, width=50, font=parent.myfont)
        self.titletext.place(relx=0.5, rely=0.1, anchor=CENTER)

    #       Insert temperary movie title for example purposes

        self.titletext.insert(0, "The Creature from the Black Lagoon")

    #      Create Entry Box for movie lenght

        self.lengthtext = Entry(self, width=10, font=parent.myfont)
        self.lengthtext.place(relx=0.8, rely=0.2, anchor=W)

        self.lengthtext.insert(0, "1:34")

    #      Create Entry Box for year of movie release

        self.yeartext = Entry(self, width=4, font=parent.myfont)
        self.yeartext.place(relx=0.1, rely=0.2, anchor=E)

        self.yeartext.insert(0, "1958")

    #       Create Entry box for whether or not I have watched the movie

        self.watchedtext = Entry(self, width=3, font=parent.myfont)
        self.watchedtext.place(relx=0.5, rely=0.3, anchor=CENTER)

        self.watchedtext.insert(0, "Yes")

    # Buttons
    # Add Button adds a movie to the list
    # Update Button updates the information for the current selection
    # Delete Button deletes the selected movie

        self.addbutton = Button(self, text="Add", command=add_movie)
        self.addbutton.place(relx=0.3, rely=0.5, anchor=W)
        self.updatebutton = Button(self, text="Update", command=update_movie)
        self.updatebutton.place(relx=0.5, rely=0.5, anchor=CENTER)
        self.deletebutton = Button(self, text="Delete", command=delete_movie)
        self.deletebutton.place(relx=0.7, rely=0.5, anchor=E)

        self.pack(side=RIGHT)



def add_movie():
    print("Add Button")


def update_movie():
    print("Update Button")


def delete_movie():
    print("Delete Button")


app = App()
app.mainloop()

When you select rows from the database you can use an ORDER BY clause. You should also have a look at the IF NOT EXISTS clause which makes your table creation code much "cleaner" — jackal
– jackal, Commented May 21 at 7:32

André · Accepted Answer · 2025-05-21 08:01:58Z

I would suggest the following approach:

Use an ORDER BY clause in your SQL queries to get movies sorted alphabetically, e.g.:

SELECT * FROM movies ORDER BY title ASC

To show correct info in the right frame, bind a function to the listbox's selection event (<>). When a movie is selected, fetch its info from the database using the title (or another unique ID), and update the right frame’s content.

Your on_sselecttemp() does not look quite right. It should be sth. like

def on_select(self, event):
        selected_index = self.listbox.curselection()
        if selected_index:
            selected_item = self.listbox.get(selected_index)
            print(f"Selected item: {selected_item}")

Add a binding to the constructor of the ListFrame class to make it work:

def __init__(self, parent, *args, **kwargs):

   ...

   self.listbox.bind("<<ListboxSelect>>", self.on_select)

Collectives™ on Stack Overflow

Is there a way to sort a database so that the order will match an alphabetically sorted list box in Tkinter?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related