[Tkinter] Dynamic checkbox treeview issue

[OogieM]

I'm trying to use a checkbox treeview to display the results of an SQLite query. I build the query's SELECT, JOIN and WHERE clauses on the fly based on user inputs as to which columns to display and what restrictions (the where) they want to apply. I have a series of checkboxes and I get whether a checkbox is selected or not. Then I use that to add to the text variables that are pieces of the eventual query. To make sure that I can always have a known select item at the beginning my start includes the primary key from the table I am doing the select from. I have one problem with how I build my query when I have an item I am searching for. I can create it using string operations but that isn't a secuew way to build the query to prevent SQL injection. That is a different problem from the treeview display though. I'll go post about the SQLite issue elsewhere.

I am having 2 problems with the treeview.
1. I can't figure out how to dynamically set up the number of columns when I create the treeview
2. I can't figure out how to start filling the treeview rows at item[1] in my cursor not item[0]

This is the create statement but it always creates all the columns I want to somehow only put the number I really need. I know how many columns I actually want to display. Column zero is used for the checkbox for each row.

CheckboxTreeview(self.bottomdisplay.scrollable_frame,column=('column0', 'column1', 'column2', 'column3', 'column4', 'column5'  
 , 'column6', 'column7', 'column8', 'column9'), show=('headings','tree'),selectmode='extended')  
checkboxheaders = self.get_column_headers()
contact_search_tree.column('#0', minwidth=35, width=35)

I name the columns according to what they will contain

for i,name in enumerate(checkboxheaders):  
    print(i)  
    contact_search_tree.heading('#' + str(i+1), text=name)  
    contact_search_tree.column('#'+ str(i+1),minwidth=1)

I execute my query the query problem comes in when I have cmd variables but I can get a proper query by not making a where clause and so I get all the data in the table. Figures i'd deal with one problem at a time. When I fill in the row in the if row in results step I get item [0} under my column 1 and so on.

if cmdvariables:  
    resultcursor.execute(cmd, cmdvariables)  
else:  
    resultcursor.execute(cmd) 
results = resultcursor.fetchall()

for row in results:  
    # todo need to figure out how to start the values at item 1 in the row not item zero  
 	contact_search_tree.insert('', 'end', values=row)  
    print(row)  
contact_search_tree.config(height=len(results))  
contact_search_tree.pack()

Any assistance is appreciated.

[Larz60+]

I can't figure out how to dynamically set up the number of columns when I create the treeview

see: https://stackoverflow.com/a/64567297

[OogieM]

I can't figure out how to dynamically set up the number of columns when I create the treeview

see: https://stackoverflow.com/a/64567297

I had seen that one but that doesn't seem to work when the data are in a cursor as rows not data as columns. Do I really have to read the cursor and split out all the fields into columns of data to make it work? Isn't that going tobe a big performance hit compared to entering in a row?

I was hoping for some sort of start at row[1] enumerate type thing to strip the first item and then add it.

[deanhystad]

Why a performance hit? Rows and columns are grid terms. Trees have branches. There are no rows, or columns, in a tree.

[OogieM]

Why a performance hit? Rows and columns are grid terms. Trees have branches. There are no rows, or columns, in a tree.

A tree view DOES have rows and columns. Otherwise the moving and adding columns wouldn't work but that structure is built into the treeview.

The performance issue is that you would have to go through the query as many times as you have columns to display to get the values for each column adding it to the treeview and then repeating that same re-reading of the cursor for the next column of values.

One of the big advantages of treeview is that you can just pass it a cursor result and it works. Except that it expectsto display the entire cursor not just some of the fields in each row of the cursor.

Yes trees can have branches down but treeview is also a great way to display a matrix of rows and columns like you get from a database cursor.

[deanhystad]

Then don't pass a cursor to the tree. Do the query, pass results to a function, function modifies the tree.

[OogieM]

I did get this working the way I wanted it to.

Set up my empty lists

self.checkbox_list = []
self.display_name_list = []

First, to get the headers for each item that can be displayed I have code like this. The display checkbooxes are in a left sidebar.

        display_first_name_val = tk.BooleanVar()
        display_first_name_val.set(False)
        display_contact_first_name = tk.Checkbutton(self.leftsidebar, text="First Name", var=display_first_name_val, width=15,
                                              anchor="w")
        display_contact_first_name.grid(row=10, column=0, sticky="W")
        self.checkbox_list.append(display_first_name_val)
        self.display_name_list.append("First Name")

How I get the values for use later

    def get_column_headers(self):
        headers = list()
        #  zip adds 2 lists together
        for cb, col_name in zip(self.checkbox_list, self.display_name_list):
            if cb.get():
                headers.append(col_name)
        return headers

    def get_search_field_values(self):
        values = list()
        values.append(self.contact_name.cget())
        values.append(self.farm_prefix.cget())
        values.append(self.state_premise_id.cget())
        return values

    def get_checkbox_values(self):
        values = list()
        for cb in self.checkbox_list:
            values.append(cb.get())
            # print(cb.get())
        return values

Start of the code that is the search button click

    def search_contact(self):
        checkboxvalues = self.get_checkbox_values()
        # Set up to build the database query by first getting the start and then adding in clauses as required depending
        # on whether the checkbox is selected or not and on what is entered in the search criteria
        cmd = AnimalTrakker_Query_Code.display_start_select
        cmdjoinclause = ""
        cmdwhereclause = ""
        if checkboxvalues[0] :
            cmd = cmd + AnimalTrakker_Query_Code.display_member_first_name_true_select
        if checkboxvalues[1]:
            cmd = cmd + AnimalTrakker_Query_Code.display_member_last_name_true_select
        if checkboxvalues[2]:
            cmd = cmd +AnimalTrakker_Query_Code.display_farm_name_true_select
        if checkboxvalues[3]:
            cmd = cmd + AnimalTrakker_Query_Code.display_flock_prefix_true_select
            cmdjoinclause = AnimalTrakker_Query_Code.join_flock_prefix_table

There are more display options but they follow the same structure

Then the code that builds the treeview and fills it

The first line is where I fix the query by removing the leading comma no matter what my first display item is.

        cmd = cmd[:cmd.index(",")]+ cmd[cmd.index(",")+1:]
        cmd = cmd + "FROM contacts_table "
        cmd = cmd + cmdjoinclause
        cmdvariables = []
        search_name_data_entry = self.contact_name.get()
        if search_name_data_entry:
            cmdwhereclause = 'WHERE contacts_table.contact_last_name LIKE (?)'
            cmdvariables.append('%'+search_name_data_entry+'%')
        search_prefix_data_entry = self.farm_prefix.get()
        if search_prefix_data_entry:
            if search_name_data_entry:
                cmdwhereclause = cmdwhereclause + 'AND flock_prefix_table.flock_prefix LIKE (?)'
                cmdvariables.append('%' + search_prefix_data_entry + '%')
            else:
                cmdwhereclause = cmdwhereclause + 'WHERE flock_prefix_table.flock_prefix LIKE (?)'
                cmdvariables.append('%' + search_prefix_data_entry + '%')
        search_state_premise_data_entry = self.state_premise_id.get()
        if search_state_premise_data_entry:
            if search_name_data_entry or search_prefix_data_entry:
                cmdwhereclause = cmdwhereclause + 'AND contacts_premise_table.state_premise_id LIKE (?)'
            else:
                cmdwhereclause = cmdwhereclause + 'WHERE contacts_premise_table.state_premise_id LIKE (?)'
                cmdvariables.append('%' + search_state_premise_data_entry + '%')
        cmd = cmd + cmdwhereclause
        connection = sqlite3.connect(config.currentdatabase)
        resultcursor = connection.cursor()
        if cmdvariables:
            resultcursor.execute(cmd, cmdvariables)
        else:
            resultcursor.execute(cmd)
        results = resultcursor.fetchall()
        checkboxheaders = self.get_column_headers()
        if self.contact_search_tree is not None:
            self.contact_search_tree.destroy()
        self.contact_search_tree = CheckboxTreeview(self.bottomdisplay.scrollable_frame,
                                               column=checkboxheaders,
                                               show=('headings', 'tree'), selectmode='extended')
        self.contact_search_tree.delete(*self.contact_search_tree.get_children())
        self.contact_search_tree.column('#0', minwidth=35, width=35)
        for i,name in enumerate(checkboxheaders):
            self.contact_search_tree.heading('#' + str(i+1), text=name)
            self.contact_search_tree.column('#'+ str(i+1),minwidth=1)
        for row in results:
            self.contact_search_tree.insert('', 'end', values=row)
        self.contact_search_tree.config(height=len(results))
        self.contact_search_tree.pack()
        connection.close()

In the class I keep all my query code The bits are all like these

display_start_select ="""
	SELECT
	"""
display_member_first_name_true_select ="""
    , contacts_table.contact_first_name
	"""
display_member_last_name_true_select ="""
	, contacts_table.contact_last_name
	"""
display_farm_name_true_select = """
    , contacts_table.contact_company
	"""
display_flock_prefix_true_select = """
    , flock_prefix_table.flock_prefix
    """
join_flock_prefix_table = """
	LEFT JOIN owner_registration_table
    	ON contacts_table.id_contactsid = owner_registration_table.id_contactsid
    LEFT JOIN flock_prefix_table 
    	ON flock_prefix_table.id_flockprefixid = owner_registration_table.id_flockprefixid
	"""

And the result is that no matter what is selected to display and what the search criteria are the treeview gets filled properly with only the correct number of columns.

[deanhystad]

Happy to hear it is working!

Your code can be shorter. This

def get_checkbox_values(self):
    values = list()
    for cb in self.checkbox_list:
        values.append(cb.get())
        # print(cb.get())
    return values

Can be this

def checkbox_values(self):
    return [cb.get() for cb in self.checkboxes]

And this:

def get_column_headers(self):
    headers = list()
    #  zip adds 2 lists together
    for cb, col_name in zip(self.checkbox_list, self.display_name_list):
        if cb.get():
            headers.append(col_name)
    return headers

Can be this:

def selected_column_headers(self):
    return [name for cb, name in zip(self.checkboxes, self.display_names) if cb.get()]

And this:

def get_search_field_values(self):
    values = list()
    values.append(self.contact_name.cget())
    values.append(self.farm_prefix.cget())
    values.append(self.state_premise_id.cget())
    return values

Can be this:

def get_search_field_values(self):
    return self.contact_name.cget(), self.farm_prefix.cget(), self.state_premise_id.cget()

Or if you really want them in a list instead of a tuple:

def search_field_values(self):
    return [self.contact_name.cget(), self.farm_prefix.cget(), self.state_premise_id.cget()]

Notice how the order is more obvious when the list is built this way as opposed to using append(). And on the other side of the method call, instead of unpacking the values like this:

    checkboxvalues = self.get_checkbox_values()
    cmd = AnimalTrakker_Query_Code.display_start_select
    cmdjoinclause = ""
    cmdwhereclause = ""
    if checkboxvalues[0] :
        cmd = cmd + AnimalTrakker_Query_Code.display_member_first_name_true_select
    if checkboxvalues[1]:
        cmd = cmd + AnimalTrakker_Query_Code.display_member_last_name_true_select
    if checkboxvalues[2]:
        cmd = cmd +AnimalTrakker_Query_Code.display_farm_name_true_select
    if checkboxvalues[3]:
        cmd = cmd + AnimalTrakker_Query_Code.display_flock_prefix_true_select
        cmdjoinclause = AnimalTrakker_Query_Code.join_flock_prefix_table

You can do this:

        cmd = AnimalTrakker_Query_Code.display_start_select
    cmdjoinclause = ""
    cmdwhereclause = ""
    first_name_selected, last_name_selected, farm_selected, flock_selected = self.checkbox_values()  # <- Method needs a better name.  selected_fields()?
    if first_name_selected:
        cmd += AnimalTrakker_Query_Code.display_member_first_name_true_select  # Attribute needs a better name too.  select_first_name()?
    if last_name_selected:
        cmd += AnimalTrakker_Query_Code.display_member_last_name_true_select
    if farm_selected:
        cmd += AnimalTrakker_Query_Code.display_farm_name_true_select
    if flock_selected]:
        cmd += AnimalTrakker_Query_Code.display_flock_prefix_true_select
        cmdjoinclause = AnimalTrakker_Query_Code.join_flock_prefix_table

You may have noticed that I don't like using type identifiers in variable names. I don't care that you are using a list to store display names. I'm fine with a tuple or really anything that I can index and iterate. The "display names" part is important. The type of container holding the display names is not. If it is not important and does nothing to aid understanding, it does not belong in the variable name.

I also dislike (mild dislike) "get". I want to know the selected column names. I don't really care that I have to "get" them, I just want to use them. I think this reads better

for name in self.selected_column_names():

Than this:

for name in self.get_column_names():

I guess what I really dislike is typing. If I can leave words off without reduces understanding, I will leave them off. But if I can replace a word with one that enhances understanding (replace generic "get" with "selected") I am fine if it adds a few key strokes. I'm complicated.

Glad to see comments, but I think this comment.

def search_contact(self):
    checkboxvalues = self.get_checkbox_values()
    # Set up to build the database query by first getting the start and then adding in clauses as required depending
    # on whether the checkbox is selected or not and on what is entered in the search criteria

Should be a docstring.

def search_contact(self):
    """
    Set up to build the database query by first getting the start and then adding in clauses as required depending
    on whether the checkbox is selected or not and on what is entered in the search criteria
    """
    checkboxvalues = self.get_checkbox_values()

Ok, maybe this was a code comment instead of a method comment. But there should be a comment that describes what the method does. Comments describing blocks of code inside a function/method should be fairly rare. Code should be written to be easy to understand and comments (and doc strings) should provide context that is not expressed in the code. For most functions/methods a docstring at the top is adequate for this purpose.

I prefer a docstring because the convention is that you use a docstring to describe what a function/method does. When I see """ """ directly under a function/method declaration I know the enclosed text is going to tell me about the function/method. Unless I want to know implementation details I probably don't have to even look at the code.

A comment can serve the same purpose as a docstring, but a comment can appear anywhere. I am not sure if "# Set up to build the database query.." is describing the entire method, or if it is describing what is going on in the if statements that follow.

[OogieM]

maybe this was a code comment instead of a method comment. But there should be a comment that describes what the method does. Comments describing blocks of code inside a function/method should be fairly rare.

I generally start any code by writing a comment that is the pseudo code of what I am trying to accomplish and in some cases why or other notes from my specification that I need handy for me to work with. Making those things single line comments is a single keystroke, I'll investigate making a shortcut to make them docstrings instead. Generally once the code is "done" I go back and either delete or clean upthose comments and remove excess print statements. As I'm getting more proficient with the debugger I am using far fewer print statements but some of the early stuff still has a lot of them in it.

I am not sure if "# Set up to build the database query.." is describing the entire method, or if it is describing what is going on in the if statements that follow.

Just the statements that follow.

Thanks for the code suggestions. I am the primary programmer on what is a huge project. My most recent coding has been Android Java for the mobile app that is the companion to the Python app. The mobile app is used to collect field data chute side with the animals including printing identification labels with bar codes for all tissue samples that link back to the official federal ID of the animal. The Python app is the desktop that creates the required official laboratory submission forms for those tissue samples. The users are veterinarians. I'm a farmer first and a coder second. (Retired from high tech but that was 20+years ago) and trying to learn Python as fast as possible to get this project done. The only other person working on the project is my husband who does all the low level driver code on the mobile app to handle the various hardware we have to support and some Java coding. I'm the only one doing naytign in Python.

---

You may have noticed that I don't like using type identifiers in variable names. I don't care that you are using a list to store display names. I'm fine with a tuple or really anything that I can index and iterate. The "display names" part is important. The type of container holding the display names is not. If it is not important and does nothing to aid understanding, it does not belong in the variable name.

Circling back to this to explain why I won't be taking that advice.

In my system I have many cases where variable names are similar because they are in general working with similar data. So I like knowing that this is the list of display names not that this is a single display name nor is it a cursor of display names etc. I also try to keep variable names consistent across both the Python desktop app and the Java Mobile app so I don't exactly follow either system's conventions for naming. I find that for me, that makes it less likely that I will make stupid programming errors by confusing variables.

Some of the simpler code things you mention are being implemented now. The cmd += for example.

I also dislike (mild dislike) "get". I want to know the selected column names. I don't really care that I have to "get" them

This I am considering implementing. Only issue is that I have a very large set of code where consistent naming includes getting the item(s) in question so that system of including the verb for what I do with the item is sprinkled all over everywhere. To change it in one place and not another seems wrong to me and I'm not sure whether to go through the effort of changing them all now.

To explore that I'm looking at the refactoring tools in PyCharm to see if I can do it easily that way. I've been a bit leary of using some of those automatic renaming/refactoring options mostly because I've seen things get messed up by partial strings being refactored incorrectly. It's been a while (a number of years) but the scars from trying to recover are still with me.

The comments as does make sense but my muscle memory is of always making single line comments. I haven't been particularly successful in changing that but I may go back and convert them later. As I said I do a comment block initialy to get me started with coding.

Thanks again for all the suggestions

PS

I just realized that part of why I do display_first_name and not selected_first_name (as an example) is because I will be further selecting from the treeview shown to do more things. The search is to get the group down to a smaller set that I will then further select from by selecting individual records. SO in my mind I am not selecting first names I'm displaying them and select is another level down. Hard to explain but as I was trying ti implement your ideas I was fightinh it and when I went back to the specification I see the clear dsplay this and then selct from that structure so the naming makes more sense with that in mind.