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

Interactive Treeview with Copy Functionality in Tkinter

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.

Copy selected or all rows from a Treeview dynamically populated with data from an SQLite database

Code Breakdown

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

1. Importing Required Libraries

# Importing required libraries
from tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine, text # Database connection
from 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 database
engine = 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 window
my_w = tk.Tk()
# Setting dimensions of the window
my_w.geometry("720x600")  
# Setting the title of the window
my_w.title("www.plus2net.com")

# Font style for the Text entry box
font1 = ['Times', 14, 'normal']  

# Creating a multi-line Text input box
t1 = 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 query
b1 = 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 widget
font2 = ['Times', 14, 'normal']  

# Adding style to the Treeview widget
style = ttk.Style(my_w)
style.theme_use("clam")  # Setting the theme to clam
style.configure("Treeview", 
    background="black", 
    fieldbackground="black", 
    foreground="white", 
    font=font2)

# Customizing the Treeview header style
style.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:

4. Copy Selected Rows


# Function to copy selected rows from Treeview to clipboard
def 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 binding
copy_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 clipboard
def 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 binding
select_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 loop
my_w.mainloop()

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

from tkinter import ttk
import tkinter as tk
from sqlalchemy import create_engine, text  # connection to database
from sqlalchemy.exc import SQLAlchemyError

# Database connection
engine = create_engine('sqlite:///E:\\testing\\sqlite\\my_db.db', echo=True)
my_conn = engine.connect()

# Creating tkinter window
my_w = tk.Tk()
my_w.geometry("720x600")  # width and height of the window
my_w.title("www.plus2net.com")
font1 = ['Times', 14, 'normal']  # font style for Text entry box

# Text input box
t1 = 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 query
b1 = 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 Treeview
font2 = ['Times', 14, 'normal']
style = ttk.Style(my_w)
style.theme_use("clam")  # set theme to clam
style.configure("Treeview", background="black", fieldbackground="black", foreground="white", font=font2)
style.configure('Treeview.Heading', background="PowderBlue")  # Header style

# Copy selected rows
def 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 copy
def 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 results
def 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 loop
my_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


Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer