Copy Selected or All Rows of Treeview Data from SQLite Database query result

";require "templates/body_start.php";?>

Introduction

Treeview is a powerful widget in Tkinter for displaying hierarchical or tabular data. In this tutorial, we demonstrate how to:

• Create an interactive Treeview connected to an SQLite database.
• Display query results in a user-friendly GUI interface.
• Add functionality to copy selected rows or all rows to the clipboard.

Code Breakdown

Below, we break the code into logical sections to explain each functionality step-by-step:

1. Importing Required Libraries

# Importing required librariesfrom tkinter import ttkimport tkinter as tkfrom sqlalchemy import create_engine, text # Database connectionfrom sqlalchemy.exc import SQLAlchemyError

• tkinter: Used to create the graphical user interface.
• sqlalchemy: Provides tools for connecting to and querying the SQLite database.

2. Database Connection

# Establishing a connection to the SQLite databaseengine = create_engine('sqlite:///E:\\testing\\sqlite\\my_db.db', echo=True)my_conn = engine.connect()

• create_engine: Connects to the database located at the specified path.
• my_conn: Represents the connection used for executing queries.

3. Creating the Main Application Window

# Creating the main Tkinter windowmy_w = tk.Tk()# Setting dimensions of the windowmy_w.geometry("720x600")  # Setting the title of the windowmy_w.title("www.plus2net.com")# Font style for the Text entry boxfont1 = ['Times', 14, 'normal']  # Creating a multi-line Text input boxt1 = tk.Text(my_w, height=5, width=70, bg='yellow', font=font1)t1.grid(row=0, column=0, padx=5, pady=10)# Creating a Button to execute the queryb1 = tk.Button(my_w, text='GO', font=18,     command=lambda: my_query(t1.get("1.0", 'end')))b1.grid(row=0, column=1)# Font style for Treeview widgetfont2 = ['Times', 14, 'normal']  # Adding style to the Treeview widgetstyle = ttk.Style(my_w)style.theme_use("clam")  # Setting the theme to clamstyle.configure("Treeview",     background="black",     fieldbackground="black",     foreground="white",     font=font2)# Customizing the Treeview header stylestyle.configure('Treeview.Heading', background="PowderBlue")

Explanation:

This section defines the main components of the Tkinter window.

• Creating the Window:
  • tk.Tk(): Initializes the main application window.
  • my_w.geometry(): Sets the dimensions of the window to 720x600 pixels.
  • my_w.title(): Sets the title of the window.
• Text Input Box:
  • tk.Text(): Creates a multi-line text box for user input.
  • grid(): Positions the text box in the first row and column of the window.
• Query Execution Button:
  • tk.Button(): Creates a button labeled "GO" to execute SQL queries.
  • command: Calls the function my_query() with the input from the text box when the button is clicked.
• Styling the Treeview:
  • ttk.Style(): Customizes the appearance of the Treeview widget.
  • style.configure(): Sets the background, foreground, and font for the Treeview and its header.

4. Copy Selected Rows

# Function to copy selected rows from Treeview to clipboarddef copy_selected_rows(trv):    # Get selected rows in the Treeview    selected_items = trv.selection()      if selected_items:        # Create an empty list to store copied rows        copied_data = []                # Loop through selected items        for item in selected_items:            row = trv.item(item, "values")            copied_data.append("\t".join(row))  # Join values with tabs                # Copy the rows to the clipboard        my_w.clipboard_clear()  # Clear the clipboard        my_w.clipboard_append("\n".join(copied_data))  # Add rows to clipboard        my_w.update()  # Update clipboard content                # Print copied data for debugging        print("Copied selected rows:", copied_data)    else:        # If no rows are selected, print a message        print("No rows selected!")

Explanation:

This function allows users to copy selected rows from a Treeview to the clipboard. It provides flexibility in handling multiple row selections and ensures the data is formatted for easy pasting into other applications.

• Retrieve Selected Rows:
  • trv.selection(): Retrieves the IDs of all selected rows in the Treeview.
• Format the Data:
  • trv.item(item, "values"): Fetches the data of each selected row as a tuple.
  • "\t".join(row): Converts the tuple into a tab-separated string, ideal for pasting into Excel or similar tools.
• Copy to Clipboard:
  • my_w.clipboard_clear(): Clears any existing content in the clipboard.
  • my_w.clipboard_append(): Adds the formatted rows to the clipboard.
  • my_w.update(): Updates the clipboard content to make it available for pasting.
  Learn More on Managing Clipboard from Tkinter →
• Feedback:
  • If rows are copied successfully, their content is printed to the console for debugging.
  • If no rows are selected, a message is printed indicating that no rows were selected.

Example Usage:

This function can be linked to a button in the Tkinter GUI to enable users to copy selected rows. Here’s how you can use it:

# Example of button bindingcopy_button = tk.Button(my_w, text='Copy Selected Rows',     command=lambda: copy_selected_rows(treeview_widget))copy_button.pack()

5. Select all rows and copy

# Function to select all rows in the Treeview and copy them to the clipboarddef select_all_and_copy(trv):    # Iterate through all rows in the Treeview    for item in trv.get_children():        trv.selection_add(item)  # Programmatically select each row        # Copy the selected rows to the clipboard    copy_selected_rows(trv)

Purpose:

This function allows users to select all rows in the Treeview widget and copy their data to the clipboard in one click. It automates the selection process, ensuring all rows are included without manual effort.

How It Works:

• Select All Rows:
  • trv.get_children(): Retrieves all row IDs from the Treeview.
  • trv.selection_add(item): Adds each row ID to the selection.
• Copy Data:
  • copy_selected_rows(trv): Calls the existing copy_selected_rows function to copy the selected rows to the clipboard.

Example Usage:

You can bind this function to a "Select All and Copy" button in the Tkinter GUI:

# Example of button bindingselect_all_button = tk.Button(my_w,     text='Select All and Copy',     command=lambda: select_all_and_copy(treeview_widget))select_all_button.pack()

6. Creating the Query Execution Function

def my_query(query):    # Logic for executing SQL queries and displaying results    try:        r_set = my_conn.execute(text(query))         l1 = [r for r in r_set.keys()]    except SQLAlchemyError as e:        # Handle SQL errors

• my_query: Executes the SQL query and fetches results to display in the Treeview.
• try-except: Handles errors encountered during query execution.

7. Running the Tkinter Main Loop

# Run the Tkinter event loopmy_w.mainloop()

Download the sample my_db.db database file with the student table from here.

Full code : Copy to clipboard

from tkinter import ttkimport tkinter as tkfrom sqlalchemy import create_engine, text  # connection to databasefrom sqlalchemy.exc import SQLAlchemyError# Database connectionengine = create_engine('sqlite:///E:\\testing\\sqlite\\my_db.db', echo=True)my_conn = engine.connect()# Creating tkinter windowmy_w = tk.Tk()my_w.geometry("720x600")  # width and height of the windowmy_w.title("www.plus2net.com")font1 = ['Times', 14, 'normal']  # font style for Text entry box# Text input boxt1 = tk.Text(my_w, height=3, width=70, bg='bisque1', font=font1)t1.grid(row=0, column=0, padx=5, pady=10)# Button to execute queryb1 = tk.Button(my_w, text='GO', font=18, command=lambda: my_query(t1.get("1.0", 'end')))b1.grid(row=0, column=1)# Adding style to Treeviewfont2 = ['Times', 14, 'normal']style = ttk.Style(my_w)style.theme_use("clam")  # set theme to clamstyle.configure("Treeview", background="black", fieldbackground="black", foreground="white", font=font2)style.configure('Treeview.Heading', background="PowderBlue")  # Header style# Copy selected rowsdef copy_selected_rows(trv):    selected_items = trv.selection()  # Get selected rows    if selected_items:        copied_data = []        for item in selected_items:            row = trv.item(item, "values")            copied_data.append("\t".join(row))  # Tab-separated row        # Copy to clipboard        my_w.clipboard_clear()        my_w.clipboard_append("\n".join(copied_data))        my_w.update()  # Update clipboard        print("Copied selected rows:", copied_data)    else:        print("No rows selected!")# Select all rows and copydef select_all_and_copy(trv):    for item in trv.get_children():        trv.selection_add(item)  # Select all rows    copy_selected_rows(trv)  # Copy selected rows# Function to execute the query and display resultsdef my_query(query):    # Clear any widgets in row 1 and row 2    for w in my_w.grid_slaves():        if int(w.grid_info()["row"]) > 0:            w.grid_forget()    try:        # Execute the query        r_set = my_conn.execute(text(query))  # Execute query and get record set        l1 = [r for r in r_set.keys()]  # List of columns from database table        r_set = list(r_set)  # List of rows of records    except SQLAlchemyError as e:  # Database error handling        # Extract error message        if 'orig' in e.__dict__:            error_message = str(e.__dict__['orig'])        else:            error_message = str(e)        # Display error message in red        error_label = tk.Label(my_w, text=f"Error: {error_message}", fg='red', font=('Arial', 14))        error_label.grid(row=1, column=0, columnspan=2, padx=20, pady=20)        print("Database error:", error_message)  # Log error to the console    else:  # If no error, display the Treeview        # Create Treeview widget        trv = ttk.Treeview(my_w, selectmode='extended', columns=l1, show='headings', height=15)        trv.grid(row=1, column=0, columnspan=2, padx=20, pady=20)        # Define Treeview columns        for i in l1:            trv.column(i, anchor='w', width=100)            trv.heading(i, text=i)        # Insert rows into Treeview        for row in r_set:            trv.insert('', 'end', iid=row[0], text=row[0], values=list(row))        # Add vertical scrollbar to the Treeview        vs = ttk.Scrollbar(my_w, orient="vertical", command=trv.yview)        trv.configure(yscrollcommand=vs.set)        vs.grid(row=1, column=1, sticky='ns')  # Place scrollbar to the right of Treeview        # Buttons for copying rows        button_frame = tk.Frame(my_w)  # Frame for buttons below Treeview        button_frame.grid(row=2, column=0, sticky='w', padx=20, pady=10)        copy_button = tk.Button(button_frame, text="Copy Selected Rows", font=14, bg='yellow',command=lambda: copy_selected_rows(trv))        copy_button.pack(side=tk.LEFT, padx=5)        copy_all_button = tk.Button(button_frame, text="Select All and Copy", font=14, bg='lightgreen',command=lambda: select_all_and_copy(trv))        copy_all_button.pack(side=tk.LEFT, padx=5)# Run the Tkinter event loopmy_w.mainloop()

Conclusion

This tutorial demonstrates how to integrate Treeview with SQLite, adding functionality to copy rows to the clipboard. It provides a user-friendly way to manage and interact with database data. Try implementing it and explore its customization options for your projects!

Part III : Query window to get reports by joining multiple tables in SQLite →
Select rows of Treeview without holding the Ctrl Key →