Tkinter SQLite Database Viewer with Sorting and CSV Export

This project combines Tkinter, Pandas and SQLite to create a desktop database viewer. The user selects an SQLite database, chooses a table, displays its records in a Treeview, sorts the DataFrame by clicking column headings and exports the current result to CSV.

It extends our Pandas Treeview column-sorting project by replacing the Excel or CSV input with an SQLite database.

SQLite database
       |
       v
Available tables
       |
       v
Select table
       |
       v
Pandas DataFrame
       |
       v
Treeview
   |       |
   |       +--> Export CSV
   |
   +--> Click heading
             |
             v
       sort_values()
             |
             v
       Refresh rows

Import Required Libraries Top ↑

import os
import tkinter as tk
from tkinter import filedialog,ttk

import pandas as pd

from sqlalchemy import create_engine,inspect
from sqlalchemy.exc import SQLAlchemyError

• Tkinter creates the database browser, Listbox, Buttons and Treeview.
• Pandas creates and sorts the DataFrame and exports it to CSV.
• SQLAlchemy connects to SQLite and inspects the available database tables.

Connect to an SQLite Database Top ↑

The user selects an existing SQLite database with the Tkinter file dialog.

db_path=filedialog.askopenfilename(
    title='Select SQLite database',
    filetypes=[
        ('SQLite database','*.db'),
        ('SQLite files','*.sqlite *.sqlite3'),
        ('All files','*.*')
    ]
)

The SQLAlchemy Engine is created only after a file is selected.

engine=create_engine(
    'sqlite:///'+db_path
)

If another database is selected later, the previous Engine is disposed before creating the new one.

Load Available Table Names Top ↑

Instead of manually querying sqlite_master, SQLAlchemy can inspect the database.

inspector=inspect(
    engine
)

tables=inspector.get_table_names()

SQLite system-style names can be excluded:

tables=[
    table
    for table in tables
    if not table.startswith(
        'sqlite_'
    )
]

The available table names are displayed using a Listbox.

Select a Table from the Listbox Top ↑

The original version used:

table_listbox.get(tk.ACTIVE)

The active Listbox item is not always the same as an explicitly selected item. The revised version uses:

selection=table_listbox.curselection()

if not selection:
    status_var.set(
        'Select a table.'
    )
    return

selected_table=table_listbox.get(
    selection[0]
)

This makes the user's table selection explicit.

Create a Pandas DataFrame from the Table Top ↑

The original program built a query using the table name:

query=f"SELECT * FROM {selected_table}"

Because this project needs the complete selected table, Pandas can read it directly:

df=pd.read_sql_table(
    selected_table,
    con=engine
)

The GUI then displays the table name and DataFrame dimensions:

details_var.set(
    f'Table: {selected_table}   '
    f'Rows: {df.shape[0]}   '
    f'Columns: {df.shape[1]}'
)

Display the DataFrame in Treeview Top ↑

The Treeview is created once when the application starts. Loading another table only changes its columns and rows.

Safe internal column IDs are generated:

column_ids=[
    f'c{i}'
    for i in range(
        len(df.columns)
    )
]

The actual SQLite column names remain visible as the headings.

tree.heading(
    column_id,
    text=str(column_name),
    command=lambda name=column_name:
        sort_column(name)
)

Rows are inserted without using database values as Treeview item IDs:

for row in df.itertuples(
    index=False,
    name=None
):
    tree.insert(
        '',
        tk.END,
        values=row
    )

Sort Treeview Columns using Pandas Top ↑

Clicking a heading calls Pandas sort_values().

sorted_df=df.sort_values(
    by=column_name,
    ascending=ascending,
    na_position='last',
    kind='stable'
)

Each column gets its own sorting state:

sort_state={}

The first click on a column sorts ascending:

ascending=sort_state.get(
    column_name,
    True
)

The next click reverses that column:

sort_state[column_name]=not ascending

This is better than one global order variable shared by every column.

Show the Current Sort Direction

After sorting, the heading can display:

Name (ASC)

or:

Name (DESC)

Add Vertical and Horizontal Scrollbars Top ↑

A database table can have many rows and columns, so the Treeview uses both scroll directions.

y_scroll=ttk.Scrollbar(
    data_frame,
    orient='vertical',
    command=tree.yview
)

x_scroll=ttk.Scrollbar(
    data_frame,
    orient='horizontal',
    command=tree.xview
)

tree.configure(
    yscrollcommand=y_scroll.set,
    xscrollcommand=x_scroll.set
)

Export the Sorted DataFrame to CSV Top ↑

The Export to CSV Button is disabled until a table has been loaded.

After sorting, the DataFrame itself contains the new row order. Therefore the CSV export uses the same sorted data shown in Treeview.

df.to_csv(
    file_path,
    index=False
)

The Tkinter Save As dialog lets the user choose the destination.

Complete SQLite Database Viewer Top ↑

Video: SQLite Table Viewer with Sorting and Export Top ↑

How the Revised Version Improves the Viewer Top ↑

One Treeview Is Reused

The old program destroyed and recreated the complete Treeview area whenever another table was loaded. The new application keeps the Treeview and scrollbars in place and replaces only the columns and rows.

Every Column Has Its Own Sort Direction

The original single order Boolean affected every heading. The new sort_state dictionary tracks each column independently.

The Selected Table Is Read Directly

The old code constructed:

f"SELECT * FROM {selected_table}"

The new version uses:

pd.read_sql_table(
    selected_table,
    con=engine
)

Listbox Selection Is Explicit

curselection() confirms that the user has actually selected a table instead of relying on the currently active Listbox row.

Export Matches the Current Sorted Data

Sorting updates the DataFrame itself. Therefore exporting after sorting produces the same row order currently shown in Treeview.

Another Database Can Be Opened

The previous SQLAlchemy Engine is disposed before connecting to another SQLite file.

Large Database Tables Top ↑

This tutorial loads the selected table into one Pandas DataFrame because that keeps the sorting and CSV-export workflow easy to understand.

For very large database tables, a production application can extend this design with:

• SQL pagination;
• LIMIT and OFFSET;
• server-side sorting;
• filters before loading;
• loading only selected columns;
• background data loading.

The current example is best suited to learning projects and database tables that comfortably fit in memory.

Continue the Tkinter Pandas Data Viewer Top ↑

Add DataFrame searching and display only matching rows:

Let users choose which DataFrame columns should remain visible:

Apply similar sorting to files and directories:

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: