Remove Quotes and Brackets from SQL Query in Python tkinter Treeview [closed]

by

I am wanting to clean up the formatting of a query result in a window that’s been created using Treeview in tkinter (image attached)

Output Sample

SQL query uses pyodbc and the relevant python bits are:

    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    
    # Use parameterized query to avoid SQL injection
    cursor.execute('''
    SELECT First_Name,Name,Phone_1 FROM [Archive].[dbo].[Client_Tbl]
    WHERE [Archive].[dbo].[Client_Tbl].First_Name = ? OR [Archive].[dbo].
    [Client_Tbl].Name = ? OR [Archive].[dbo].[Client_Tbl].Phone_1 = ?
    ''', first_name, last_name, phone_number)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result
    
    ....

    # Function to update the Treeview with query results
    def display_results(results):
    for i in tree.get_children():
    tree.delete(i)  # Clear existing entries in the treeview
    for row in results:
    tree.insert('', 'end', values=row)  # Insert new entries into the 
    treeview

    .....
    
    # Results display (Treeview)
columns = ('First Name', 'Last Name', 'Phone Number')
tree = ttk.Treeview(frame, columns=columns, show='headings')
tree.heading('First Name', text="First Name")
tree.heading('Last Name', text="Last Name")
tree.heading('Phone Number', text="Phone Number")

tree.grid(row=3, column=0, sticky='nsew')

    .....

Any suggestions?

I’ve tried using tuple, but I can’t seem to be able to get it to work properly. (admittedly I’m fairly new to Python)

  • 3

    Where is the part of the code where you “load” the result into tree? It looks like you convert the each tuple (each record) to string and then somehow populate the tree. Actually the DB part is completely irrelevant and simple list of tuples would do for minimal reproducible example

    – 




  • I suppose that would be usefukl :)# Function to update the Treeview with query results def display_results(results): for i in tree.get_children(): tree.delete(i) # Clear existing entries in the treeview for row in results: tree.insert(”, ‘end’, values=row) # Insert new entries into the treeview

    – 




  • Please edit your question to add relevant info, don’t leave it in the comments

    – 

  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it’s currently written, it’s hard to tell exactly what you’re asking.

    – 
    Bot

I haven’t used pyodbc, but as it seems in the picture you attached, you’re inserting directly the result that you’re retrieving from pyodbc.

In this case I’ll recommend you to first unpack the values into different variables (as you’re starting to use python) and then insert them in your treeview.

If your result variable contains a tuple like: (‘Dale’, ‘Doe’, ‘XXXXXX’), just try doing the following:

for row in results:
    first_name, last_name, phone_number = row
    tree.insert(parent="", index=0, values=[first_name, last_name, phone_number])

This is a little bit overkilling but as I told you, if you’re not so much confident with python It can give you at least an idea of one of the most relevant feature, the packing, unpacking variables.

Something very important is that this will work if your rows are actually tuples, you can simply check this executing this:

print(type(row))

By the way in the worst case, if the actual type is not a tuple or it’s a string you can go over two ways, I haven’t worked with pyodbc, but mainly all the database frameworks work pretty simmilar, and I’m very confident that if you make a little bit of research, you will find some kind of cursor to get a better formatted rows, or at least more suitable with what you want.

By the way, in the worst of the scenarios, if the row is an string like “(‘Dale’, ‘Doe’, ‘XXXXXX’)” you can apply the following transformations to clean the string, at least to just start practising.

row = "('Dale', 'Doe', 'XXXXXX')"
first_name, last_name, phone_number = row.replace("(", "").replace(")", "").replace("'", "").split(",")

Hope it can help you :).
Happy Codding.

Leave a Comment