Making text clickable with binding

[DT2000]

I am in a bit of a stumped situation. I have a small program that I am writing and so far so good however I am having a little difficulty with a binding and a SQLite database query.

The basics of the program is a window contains a few widgets and the first widget is populated with different words. The user will click on 1 of the words and it should query the database and then insert only the result in to a second widget.

I have it binding and going to the query however it is not pulling the data correctly because I am having difficulty finding the correct way to define a variable for the words in the widget so it can pull only the correct data from the correct column in the DB.

How can I use or assign a variable to be used with the binding and query so I can click on any word and it query the DB for only that information?

Here is the binding:

category_label = Label(root, text='Categories', font = ('arial', 8, 'bold'), bd = 2, width = '20', fg = '#FFFFFF', bg='#284287', relief = RAISED)
category_label.place(x=975,y=33)
category = Listbox(root, font = ('arial', 8, 'bold'), bg='#FFFFDF', fg='#000000', bd=3, width=45, height=13, highlightthickness=0, relief=SUNKEN)
category.place(x=975,y=55)
category.bind('<ButtonRelease-1>', getSnippet)

Here is the def statement for the query:

def getSnippet(event):
    try:
        selection = var.get()
        c.execute('SELECT List FROM Categories WHERE List =  :selection ORDER BY Code DESC',
        {'selection': selection})
        results = c.fetchall()
        for result, in results:
            snippet_list.insert(1.0, result)
    except:
        messagebox.showerror('PBSnippet', 'Failed to load category')

Much appreciated as always.

[Denni]

What is the GUI Tinker, PySide, etc.... and an MRE/MUC would be very helpful to us in helping you with your issue.

[Larz60+]

you have much more flexibility if you use the text widget
see: https://anzeljg.github.io/rin2/book2/240.../text.html to have a look

[Denni]

By the way @DT2000, and sorry I missed this to begin with, if you are directly tying your sqlite3 data to your GUI by any means you are doing it wrong and will only experience issues sooner or later. Have you ever heard of MVC Methodology if not you might want to look into that. Also I have generic Database template that was designed to interact with a SQLite3 database and has all the basic elements you would want in a Database Class if interested in that just let me know and I could possible post it here. However if you want more extensive help with working with SQLite3 and python-qt then just shoot me a PM

[DT2000]

Denni, I appreciate the insight and will give it consideration.

Larz60+, I had the initial widget set to a Text widget but changed it because I was having troubles getting the data from the Db to populate properly. It was displaying in a single line such as a tuple and not individual words under each other as a menu.

The problem I am working on is using a variable in the widget so it can be used in the query such as:

selection = var.get()

I am trying to find the way to have a variable assigned for the clicked item so it will then query the correct column in the DB and populate another widget with that data.

There are 24 items to choose from so when 1 is clicked the variable would need to be used to query the DB for the correct column data.

[Larz60+]

You should use fetchone (which will get specific table row), rather that fetchall (which will get you entire table results)
Better yet, use SqlAlchemy which will take abit of work, but once implemented will reward you with simplifying dynamic query building.
tutorial here: https://python-forum.io/Thread-SqlAlchem...-Data-Load

Please post schema for the Categories table that are querying so I can show example (non SqlAlchemy) example.

Denni, if you have something to share, please post for all to benefit from rather than asking user to PM you.

[Denni]

Well @Larz60+ the problem with sharing the entirety of MVC Methodology along with how to implement it using sqlite3 and python-qt as well as other quality programming techniques is that it takes a lot more than a simple reply. That is why I set up a free classroom for folks to get more extensive help. It is kind of like you come to the forum to get a fish but you join the Python-Qt classroom to learn to fish.

[Larz60+]

we have a tutorials section.

[Denni]

Hmm okay @Larz60+ that is interesting and this is the first I have heard of it -- as it does not seem to be promoted much -- and as such probably many others are not even aware it exists -- so who is in charge of that and why is it not promoted more ??

[DT2000]

I appreciate the responses and regretfully I have been too busy to do any coding in the past month however with the pandemic I now have a little time to get back to this piece of code.

So... I am using tkinter and my intent is to insert data from the DB (sqlite3) into a Listbox widget, all of this works fine. It is loading a category list of code snippet category names. Once the data is inserted into the widget I am trying to have it where a user can then click on 1 of the category names and it will then query the DB for all rows containing that match that category name and insert the data into another widget. Once again it would allow the user to then click a selection from that widget, which will in turn queries the DB again to insert the data into a third widget.

As an example to better follow what I am doing.
The program runs and displays 3 widgets (1 Listbox which contains the category list already loaded, 1 Listbox which will contain the code list to be inserted after the initial click of a word in the first Listbox, 1 Text widget which will have the data inserted once a selection is made from Listbox #2)

A user will program runs and will already have the Category widget populated with the list of available choices.
The user would then click one of the categories and it would then query the DB to insert all data under that to the code list widget.
Now a user will click on the code name in that widget and it will query the DB and insert the data for the code name selected.

I believe that when the initial Category list is loaded and displayed in the first Listbox each of the items would need to be a variable for the button click to then use the variable assigned to do the query for the code names. I am not sure who to go about this.

What I am having trouble with is finding the correct way to assign a variable for the query for each category and then each code name once populated.

My Listbox:

categoryFrame = LabelFrame(root, highlightbackground="black", highlightthickness=1, bg = '#FFFFDF', width = 278, height = 202, bd = 2)
categoryFrame.place(x=978,y=55)
category_label = Label(root, text='Categories', font = ('arial', 8, 'bold'), bd = 2,width = '20', fg = '#FFFFFF', bg='#284287', relief = RAISED)
category_label.place(x=978,y=33)
category = Listbox(root, font = ('arial', 8, 'bold'), bg='#FFFFDF', fg='#000000', width=45, height=13, highlightthickness=0, relief=SUNKEN)
category.place(x=980,y=57)
category.bind('<Button-1>', getList)

My Second Listbox:

snippetFrame = LabelFrame(root, highlightbackground="black", highlightthickness=1, bg = '#FFFFDF', width = 278, height = 203, bd = 2)
snippetFrame.place(x=978,y=291)
snippet_list_label = Label(root, text='Snippet List', font = ('arial', 8, 'bold'), bd = 2, width = '20', fg = '#FFFFFF', bg='#284287', relief = RAISED)
snippet_list_label.place(x=978,y=269)
snippet_list = Listbox(root, font = ('arial', 8, 'bold'), bg='#FFFFDF', fg='#000000', bd=0, width=45, height=13, highlightthickness=0, relief=SUNKEN)
snippet_list.place(x=980,y=296)
snippet_list.bind('<ButtonRelease-1>', getCode)

The DB query can only be written once I know how to assign a variable to the items in the category list but I believe it should be something like this.

selection = category.get() #this is not the correct "get"
c.execute('SELECT Snippet_Name FROM Code Type WHERE Code_Type = :selection ORDER BY Snippet_Name DESC',
{'selection': selection})
results = c.fetchall()
for result, in results:
    snippet_list.insert(0,result)

Thanks for the assistance.