SQLite Database Viewer with Sorting and Export Feature Using Tkinter

1. Importing Libraries

    import tkinter as tk
    from tkinter import filedialog, ttk
    import pandas as pd
    from sqlalchemy import create_engine, text
    from sqlalchemy.exc import SQLAlchemyError
    

• Tkinter is used to build the graphical user interface (GUI).
• Pandas is used to manipulate and display tabular data.
• SQLAlchemy is used to connect and interact with the SQLite database.

2. Global Variables

    global my_conn, order, trv, df
    my_conn = None  # Connection object for SQLite database
    order = True  # Tracks sorting order
    trv = None  # Treeview widget reference
    df = None  # Pandas DataFrame reference
    

• my_conn: Stores the connection object for the SQLite database.
• order: Tracks sorting order for the data displayed in the Treeview widget.
• trv: Represents the Treeview widget for displaying table data.
• df: Holds the Pandas DataFrame for the selected table.

3. Connecting to SQLite Database

    def my_connect():
        """Connect to an SQLite database and load table names."""
        global my_conn
        f_types = [("Database files", "*.db"), ("All Files", "*.*")]
        file = filedialog.askopenfilename(filetypes=f_types)

        if file:
            try:
                engine = create_engine(f"sqlite:///{file}")
                my_conn = engine.connect()
                l1_path.config(text=f"Connected to: {file}", fg="green")
                load_tables()
            except SQLAlchemyError as e:
                error = str(e.__dict__['orig'])
                l1_path.config(text=f"Error: {error}", fg="red")
                print("Error:", error)
    

• Prompts the user to select an SQLite database file through a Tkinter file dialog.
• Establishes a connection to the selected database using SQLAlchemy.
• Updates the UI with the connection status or displays an error message if the connection fails.

4. Fetching and Displaying Table Names

    def load_tables():
        """Fetch table names from the database and display them."""
        global my_conn, table_listbox
        try:
            query = "SELECT name FROM sqlite_master WHERE type='table'"
            result = my_conn.execute(text(query))
            tables = [row[0] for row in result]

            # Clear existing table names
            table_listbox.delete(0, tk.END)

            # Add new table names
            for table in tables:
                table_listbox.insert(tk.END, table)
        except Exception as e:
            print("Error fetching table names:", e)
    

• Executes an SQLite query to retrieve all table names from the database.
• Displays the fetched table names in a Listbox for user selection.
• Handles errors gracefully if table names cannot be fetched.

5. Displaying Table Data in Treeview

    def display_table_data():
        """Displays the selected table's data in a Treeview widget."""
        global my_conn, trv, df

        selected_table = table_listbox.get(tk.ACTIVE)
        if not selected_table:
            print("No table selected.")
            return

        try:
            query = f"SELECT * FROM {selected_table}"
            df = pd.read_sql(query, my_conn)

            # Clear previous Treeview widgets
            for widget in frame_data.winfo_children():
                widget.destroy()

            # Create a Treeview widget
            columns = list(df.columns)
            trv = ttk.Treeview(
                frame_data, columns=columns, show="headings", height=15
            )
            trv.pack(fill="both", expand=True)

            # Configure Treeview headers
            style = ttk.Style()
            style.configure("Treeview.Heading", background="yellow", font=('Arial', 10, 'bold'))

            for col in columns:
                trv.heading(col, text=col, command=lambda c=col: sort_column(c))
                trv.column(col, anchor="center", width=100)

            # Insert rows into Treeview
            for row in df.itertuples(index=False):
                trv.insert("", "end", values=row)

            # Enable the export button
            btn_export.config(state="normal")

        except Exception as e:
            print("Error displaying data:", e)
    

• Fetches data from the selected table into a Pandas DataFrame.
• Displays the data in a Treeview widget for better visualization.
• Adds column headers and implements sorting functionality.

6. Sorting Data Dynamically

    def sort_column(col):
        """Sorts the displayed data by the selected column."""
        global df, order

        order = not order  # Toggle sorting order
        df.sort_values(by=[col], ascending=order, inplace=True)

        # Refresh Treeview with sorted data
        for row in trv.get_children():
            trv.delete(row)

        for row in df.itertuples(index=False):
            trv.insert("", "end", values=row)
    

• Sorts the data displayed in the Treeview when a column header is clicked.
• Toggles between ascending and descending sorting orders.

7. Exporting Data to a CSV File

    def export_to_csv():
        """Exports the displayed data to a CSV file."""
        global df

        if df is None:
            print("No data to export.")
            return

        file_path = filedialog.asksaveasfilename(defaultextension=".csv", filetypes=[("CSV files", "*.csv")])
        if file_path:
            df.to_csv(file_path, index=False)
            print(f"Data exported successfully to {file_path}")
        else:
            print("Export cancelled.")
    

• Allows the user to save the displayed data as a CSV file.
• Prompts the user to specify the file path and name using a save dialog.
• Uses Pandas to_csv() to export the data.

8. Tkinter GUI Layout and Functionality

    my_w = tk.Tk()  # Create the main application window
    my_w.title("SQLite Database Viewer with Sorting: www.plus2net.com")
    my_w.geometry("900x600")  # Set the size of the main window

    # Add a header label to the top of the window
    font1 = ('Arial', 20, 'bold')
    l0 = tk.Label(
        my_w, 
        text="SQLite Database Viewer", 
        font=font1, 
        bg="lightblue", 
        fg="black"
    )
    l0.pack(side="top", fill="x")

    # Label to display the connection status
    l1_path = tk.Label(
        my_w, 
        text="No database connected", 
        font=('Arial', 12), 
        anchor="w", 
        wraplength=800
    )
    l1_path.pack(pady=5)

    # Frame to organize buttons
    frame_buttons = tk.Frame(my_w)
    frame_buttons.pack(pady=5)

    # Button to connect to the database
    btn_connect = tk.Button(
        frame_buttons, 
        text="Connect to Database", 
        width=20, 
        command=my_connect
    )
    btn_connect.grid(row=0, column=0, padx=5)

    # Button to export data to CSV (disabled initially)
    btn_export = tk.Button(
        frame_buttons, 
        text="Export to CSV", 
        width=20, 
        command=export_to_csv, 
        state="disabled"
    )
    btn_export.grid(row=0, column=1, padx=5)

    # Frame to display table names in a Listbox
    frame_listbox = tk.LabelFrame(
        my_w, 
        text="Tables", 
        font=('Arial', 12, 'bold')
    )
    frame_listbox.pack(side="left", fill="y", padx=10, pady=5)

    # Listbox to display table names
    table_listbox = tk.Listbox(frame_listbox, width=30, height=20)
    table_listbox.pack(side="left", fill="y", padx=5)

    # Scrollbar for the Listbox
    table_scrollbar = tk.Scrollbar(
        frame_listbox, 
        orient="vertical", 
        command=table_listbox.yview
    )
    table_scrollbar.pack(side="right", fill="y")
    table_listbox.config(yscrollcommand=table_scrollbar.set)

    # Button to load data from the selected table
    btn_load = tk.Button(
        frame_listbox, 
        text="Load Data", 
        command=display_table_data
    )
    btn_load.pack(pady=5)

    # Frame to display data in a Treeview widget
    frame_data = tk.LabelFrame(
        my_w, 
        text="Data View", 
        font=('Arial', 12, 'bold')
    )
    frame_data.pack(side="right", fill="both", expand=True, padx=10, pady=5)

    # Start the GUI event loop
    my_w.mainloop()
    

• Creates the main application window and sets its title and dimensions.
• Includes a header label, a path label for connection status, and frames for organizing buttons, table list, and data display.
• Configures buttons for database connection, exporting data, and loading table data.
• Integrates a Treeview widget for displaying data and a Listbox for table selection.
• Ensures the layout is user-friendly and responsive.

Dynamic SQLite Table Viewer and Data Sorting with Tkinter and Pandas #Tkinter #Pandas #DataExport

import tkinter as tk
from tkinter import filedialog, ttk
import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

# Global variables
my_conn = None  # Connection object for SQLite database
order = True  # Toggle variable for sorting
trv = None  # Global Treeview reference
df = None  # Global DataFrame reference

# Function to connect to SQLite database
def my_connect():
    """ Opens a file dialog to select an SQLite database and connects to it. """
    global my_conn # database connection object 
    f_types = [('Database files', "*.db"), ('All Files', '*.*')]
    file = filedialog.askopenfilename(filetypes=f_types)

    if file:
        try:
            # Create database connection
            engine = create_engine(f"sqlite:///{file}")
            my_conn = engine.connect() # Database connection
            l1_path.config(text=f"Connected to: {file}", fg="green")
            load_tables() # function to show tables inside database
        except SQLAlchemyError as e:
            # Handle any database connection errors
            error = str(e.__dict__['orig'])
            l1_path.config(text=f"Error: {error}", fg="red")
            print("Error:", error) # show errors if any 

# Function to fetch and display table names
def load_tables():
    """ Retrieves table names from the SQLite database and displays them. """
    global my_conn, table_listbox
    try:
        query = "SELECT name FROM sqlite_master WHERE type='table'"
        result = my_conn.execute(text(query))
        tables = [row[0] for row in result] # list of available tables

        # Clear existing table names
        table_listbox.delete(0, tk.END)

        # Add new table names
        for table in tables:
            table_listbox.insert(tk.END, table)
    except Exception as e:
        print("Error fetching table names:", e)

# Function to display table data in Treeview
def display_table_data():
    """ Displays the selected table's data in a Treeview widget. """
    global my_conn, trv, df

    # Get the user selected table from left side
    selected_table = table_listbox.get(tk.ACTIVE)
    if not selected_table:
        print("No table selected.")
        return

    try:
        # Fetch table data into a Pandas DataFrame
        query = f"SELECT * FROM {selected_table}"
        df = pd.read_sql(query, my_conn) # create DataFrame using table

        # Clear previous Treeview widgets
        for widget in frame_data.winfo_children():
            widget.destroy()

        # Create a Treeview widget to display table data
        columns = list(df.columns)
        trv = ttk.Treeview(
            frame_data, columns=columns, show="headings", height=15
        )
        trv.pack(fill="both", expand=True)

        # Configure header background color for Treeview
        style = ttk.Style()
        style.configure("Treeview.Heading", background="yellow", font=('Arial', 10, 'bold'))

        # Configure columns and sorting
        for col in columns:
            trv.heading(
                col, text=col, command=lambda c=col: sort_column(c)
            )
            trv.column(col, anchor="center", width=100)

        # Insert table data to Treeview
        for row in df.itertuples(index=False):
            trv.insert("", "end", values=row)

        # Enable the Export button after data is loaded
        btn_export.config(state="normal")

    except Exception as e:
        print("Error displaying data:", e)

# Function to sort data by column
def sort_column(col):
    """ Sorts the displayed data by the selected column. """
    global df, order

    # Toggle sorting order 
    order = not order

    # Sort DataFrame
    df.sort_values(by=[col], ascending=order, inplace=True)

    # Refresh Treeview data ( delete previous data)
    for row in trv.get_children():
        trv.delete(row)

    for row in df.itertuples(index=False):
        trv.insert("", "end", values=row)

# Function to export displayed data to CSV
def export_to_csv():
    """ Exports the displayed data to a CSV file. """
    global df
    if df is not None:
        file_path = filedialog.asksaveasfilename(
            defaultextension=".csv",
            filetypes=[("CSV files", "*.csv"), ("All files", "*.*")]
        )
        if file_path:
            df.to_csv(file_path, index=False) # save as csv file 
            print(f"Data exported successfully to {file_path}")
        else:
            print("Export cancelled.")
    else:
        print("No data to export.")

# Main window setup
my_w = tk.Tk()
my_w.title("SQLite Database Viewer with Sorting : www.plus2net.com")
my_w.geometry("900x600")

# Header Label
font1 = ('Arial', 20, 'bold')
l0 = tk.Label(
    my_w, text="SQLite Database Viewer",
    font=font1, bg="lightblue", fg="black"
)
l0.pack(side="top", fill="x")

# Path Label
l1_path = tk.Label(
    my_w, text="No database connected",
    font=('Arial', 12), anchor="w", wraplength=800
)
l1_path.pack(pady=5)

# Frame for Buttons
frame_buttons = tk.Frame(my_w)
frame_buttons.pack(pady=5)

# Connect Button
btn_connect = tk.Button(
    frame_buttons, text="Connect to Database",
    width=20, command=my_connect
)
btn_connect.grid(row=0, column=0, padx=5)

# Export Button (initially disabled)
btn_export = tk.Button(
    frame_buttons, text="Export to CSV",
    width=20, command=export_to_csv,
    state="disabled"
)
btn_export.grid(row=0, column=1, padx=5)

# Frame for Table Listbox
frame_listbox = tk.LabelFrame(
    my_w, text="Tables", font=('Arial', 12, 'bold')
)
frame_listbox.pack(side="left", fill="y", padx=10, pady=5)

# Listbox for Table Names
table_listbox = tk.Listbox(frame_listbox, width=30, height=20)
table_listbox.pack(side="left", fill="y", padx=5)

# Scrollbar for Listbox
table_scrollbar = tk.Scrollbar(
    frame_listbox, orient="vertical",
    command=table_listbox.yview
)
table_scrollbar.pack(side="right", fill="y")
table_listbox.config(yscrollcommand=table_scrollbar.set)

# Load Table Data Button
btn_load = tk.Button(
    frame_listbox, text="Load Data", command=display_table_data
)
btn_load.pack(pady=5)

# Frame for Treeview Data Display
frame_data = tk.LabelFrame(
    my_w, text="Data View", font=('Arial', 12, 'bold')
)
frame_data.pack(side="right", fill="both", expand=True, padx=10, pady=5)

# Start the main event loop
my_w.mainloop()