Skip to content
Python Central Hub

GUI-based SQL Database Viewer

View on GitHub

Abstract

A database GUI is the kind of tool you reach for constantly — a mini DB Browser or phpMyAdmin you built yourself. This project connects to a SQLite database, runs whatever SQL you type, and renders the results in a proper table using Tkinter’s TreeviewTreeview widget. You’ll learn cursors, dynamic columns from cursor.descriptioncursor.description, and result rendering. Then you’ll make it safe and useful: guard destructive statements, support parameterized queries, browse the schema, and export results to CSV.

You will leave understanding:

  • The SQLite workflow: connectconnect → cursorcursor → executeexecute → fetchallfetchall.
  • How cursor.descriptioncursor.description gives you column names for any query.
  • How TreeviewTreeview displays tabular data with sortable headings.
  • Why running arbitrary SQL is powerful and dangerous — and how to fence it.

Prerequisites

  • Python 3.6 or above.
  • A text editor or IDE.
  • Standard library only — sqlite3sqlite3 and tkintertkinter are built in.
  • Basic SQL (SELECTSELECT, INSERTINSERT, CREATE TABLECREATE TABLE).
  • A SQLite .db.db file to open (or create one — see below).

Getting Started

Make a test database (optional)

make_db.py
import sqlite3
conn = sqlite3.connect("test.db")
conn.execute("CREATE TABLE users (id INTEGER, name TEXT, age INTEGER)")
conn.executemany("INSERT INTO users VALUES (?, ?, ?)",
                 [(1, "Alice", 30), (2, "Bob", 25), (3, "Carol", 41)])
conn.commit(); conn.close()
make_db.py
import sqlite3
conn = sqlite3.connect("test.db")
conn.execute("CREATE TABLE users (id INTEGER, name TEXT, age INTEGER)")
conn.executemany("INSERT INTO users VALUES (?, ?, ?)",
                 [(1, "Alice", 30), (2, "Bob", 25), (3, "Carol", 41)])
conn.commit(); conn.close()

Create the project

  1. Create a folder named sql-viewersql-viewer.
  2. Inside it, create gui_sql_database_viewer.pygui_sql_database_viewer.py.

Write the code

gui_sql_database_viewer.pySource
gui_sql_database_viewer.py
"""
GUI-based SQL Database Viewer
 
A Python application with a graphical user interface to view and interact with SQL databases. Features include:
- Connecting to a database.
- Executing SQL queries.
- Displaying query results in a table format.
"""
 
import sqlite3
from tkinter import Tk, Label, Entry, Button, Text, Scrollbar, messagebox, END
from tkinter.ttk import Treeview
 
 
class SQLDatabaseViewer:
    def __init__(self, root):
        self.root = root
        self.root.title("SQL Database Viewer")
 
        Label(root, text="Database Path:").grid(row=0, column=0, padx=10, pady=10)
        self.db_entry = Entry(root, width=50)
        self.db_entry.grid(row=0, column=1, padx=10, pady=10)
 
        Button(root, text="Connect", command=self.connect_to_db).grid(row=0, column=2, padx=10, pady=10)
 
        Label(root, text="Enter SQL Query:").grid(row=1, column=0, padx=10, pady=10)
        self.query_text = Text(root, height=5, width=60)
        self.query_text.grid(row=1, column=1, columnspan=2, padx=10, pady=10)
 
        Button(root, text="Execute", command=self.execute_query).grid(row=2, column=1, pady=10)
 
        self.result_tree = Treeview(root, columns=("#1", "#2", "#3"), show="headings")
        self.result_tree.grid(row=3, column=0, columnspan=3, padx=10, pady=10)
 
        scrollbar = Scrollbar(root, command=self.result_tree.yview)
        scrollbar.grid(row=3, column=3, sticky="ns")
        self.result_tree.configure(yscrollcommand=scrollbar.set)
 
        self.connection = None
 
    def connect_to_db(self):
        """Connect to the SQLite database."""
        db_path = self.db_entry.get()
        try:
            self.connection = sqlite3.connect(db_path)
            messagebox.showinfo("Success", "Connected to the database successfully.")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to connect to the database: {e}")
 
    def execute_query(self):
        """Execute the SQL query and display results."""
        if not self.connection:
            messagebox.showerror("Error", "Please connect to a database first.")
            return
 
        query = self.query_text.get("1.0", END).strip()
        if not query:
            messagebox.showerror("Error", "Please enter an SQL query.")
            return
 
        try:
            cursor = self.connection.cursor()
            cursor.execute(query)
            self.connection.commit()
 
            # Clear previous results
            for item in self.result_tree.get_children():
                self.result_tree.delete(item)
 
            # Display results
            columns = [description[0] for description in cursor.description] if cursor.description else []
            self.result_tree["columns"] = columns
 
            for col in columns:
                self.result_tree.heading(col, text=col)
 
            for row in cursor.fetchall():
                self.result_tree.insert("", END, values=row)
 
            messagebox.showinfo("Success", "Query executed successfully.")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to execute query: {e}")
 
 
def main():
    root = Tk()
    app = SQLDatabaseViewer(root)
    root.mainloop()
 
 
if __name__ == "__main__":
    main()
gui_sql_database_viewer.py
"""
GUI-based SQL Database Viewer
 
A Python application with a graphical user interface to view and interact with SQL databases. Features include:
- Connecting to a database.
- Executing SQL queries.
- Displaying query results in a table format.
"""
 
import sqlite3
from tkinter import Tk, Label, Entry, Button, Text, Scrollbar, messagebox, END
from tkinter.ttk import Treeview
 
 
class SQLDatabaseViewer:
    def __init__(self, root):
        self.root = root
        self.root.title("SQL Database Viewer")
 
        Label(root, text="Database Path:").grid(row=0, column=0, padx=10, pady=10)
        self.db_entry = Entry(root, width=50)
        self.db_entry.grid(row=0, column=1, padx=10, pady=10)
 
        Button(root, text="Connect", command=self.connect_to_db).grid(row=0, column=2, padx=10, pady=10)
 
        Label(root, text="Enter SQL Query:").grid(row=1, column=0, padx=10, pady=10)
        self.query_text = Text(root, height=5, width=60)
        self.query_text.grid(row=1, column=1, columnspan=2, padx=10, pady=10)
 
        Button(root, text="Execute", command=self.execute_query).grid(row=2, column=1, pady=10)
 
        self.result_tree = Treeview(root, columns=("#1", "#2", "#3"), show="headings")
        self.result_tree.grid(row=3, column=0, columnspan=3, padx=10, pady=10)
 
        scrollbar = Scrollbar(root, command=self.result_tree.yview)
        scrollbar.grid(row=3, column=3, sticky="ns")
        self.result_tree.configure(yscrollcommand=scrollbar.set)
 
        self.connection = None
 
    def connect_to_db(self):
        """Connect to the SQLite database."""
        db_path = self.db_entry.get()
        try:
            self.connection = sqlite3.connect(db_path)
            messagebox.showinfo("Success", "Connected to the database successfully.")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to connect to the database: {e}")
 
    def execute_query(self):
        """Execute the SQL query and display results."""
        if not self.connection:
            messagebox.showerror("Error", "Please connect to a database first.")
            return
 
        query = self.query_text.get("1.0", END).strip()
        if not query:
            messagebox.showerror("Error", "Please enter an SQL query.")
            return
 
        try:
            cursor = self.connection.cursor()
            cursor.execute(query)
            self.connection.commit()
 
            # Clear previous results
            for item in self.result_tree.get_children():
                self.result_tree.delete(item)
 
            # Display results
            columns = [description[0] for description in cursor.description] if cursor.description else []
            self.result_tree["columns"] = columns
 
            for col in columns:
                self.result_tree.heading(col, text=col)
 
            for row in cursor.fetchall():
                self.result_tree.insert("", END, values=row)
 
            messagebox.showinfo("Success", "Query executed successfully.")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to execute query: {e}")
 
 
def main():
    root = Tk()
    app = SQLDatabaseViewer(root)
    root.mainloop()
 
 
if __name__ == "__main__":
    main()

Run it

command
C:\Users\Your Name\sql-viewer> python gui_sql_database_viewer.py
# Enter the path to a .db file, click Connect.
# Type "SELECT * FROM users" and click Execute.
command
C:\Users\Your Name\sql-viewer> python gui_sql_database_viewer.py
# Enter the path to a .db file, click Connect.
# Type "SELECT * FROM users" and click Execute.

Step-by-Step Explanation

1. Connecting

gui_sql_database_viewer.py
self.connection = sqlite3.connect(db_path)
gui_sql_database_viewer.py
self.connection = sqlite3.connect(db_path)

sqlite3.connectsqlite3.connect opens (or creates) a database file and returns a connection. Everything else flows through it. Wrapping it in try/except sqlite3.Errortry/except sqlite3.Error turns a bad path into a friendly dialog instead of a crash.

2. Executing a query

gui_sql_database_viewer.py
cursor = self.connection.cursor()
cursor.execute(query)
self.connection.commit()
gui_sql_database_viewer.py
cursor = self.connection.cursor()
cursor.execute(query)
self.connection.commit()

A cursor runs SQL and holds the result set. commit()commit() saves changes for write statements (INSERTINSERT/UPDATEUPDATE/DELETEDELETE); it’s harmless for SELECTSELECT.

3. Dynamic columns

gui_sql_database_viewer.py
columns = [description[0] for description in cursor.description] if cursor.description else []
self.result_tree["columns"] = columns
for col in columns:
    self.result_tree.heading(col, text=col)
gui_sql_database_viewer.py
columns = [description[0] for description in cursor.description] if cursor.description else []
self.result_tree["columns"] = columns
for col in columns:
    self.result_tree.heading(col, text=col)

This is the clever bit. cursor.descriptioncursor.description lists metadata about each result column — its [0][0] is the name. Because you read it after every query, the table adapts to any SQL automatically. For non-SELECTSELECT statements descriptiondescription is NoneNone, hence the guard.

4. Filling the table

gui_sql_database_viewer.py
for item in self.result_tree.get_children():   # clear old rows
    self.result_tree.delete(item)
for row in cursor.fetchall():                   # add new rows
    self.result_tree.insert("", END, values=row)
gui_sql_database_viewer.py
for item in self.result_tree.get_children():   # clear old rows
    self.result_tree.delete(item)
for row in cursor.fetchall():                   # add new rows
    self.result_tree.insert("", END, values=row)

Clear, then insert. fetchall()fetchall() pulls every result row as a tuple; each becomes a Treeview row.

Safety: Fence Off Destructive SQL

Running any SQL means a typo’d DROP TABLEDROP TABLE is one click away. Add a confirmation for write/DDL statements:

guard.py
DESTRUCTIVE = ("drop", "delete", "update", "alter", "truncate")
 
def is_destructive(query):
    return query.strip().lower().split()[0] in DESTRUCTIVE
 
# before executing:
if is_destructive(query):
    if not messagebox.askyesno("Confirm", "This modifies data. Continue?"):
        return
guard.py
DESTRUCTIVE = ("drop", "delete", "update", "alter", "truncate")
 
def is_destructive(query):
    return query.strip().lower().split()[0] in DESTRUCTIVE
 
# before executing:
if is_destructive(query):
    if not messagebox.askyesno("Confirm", "This modifies data. Continue?"):
        return

For a pure viewer, consider opening read-only: sqlite3.connect("file:db.sqlite?mode=ro", uri=True)sqlite3.connect("file:db.sqlite?mode=ro", uri=True).

Parameterized Queries

Typed queries are fine for exploration, but if you add input fields (e.g. “find user by name”), never f-string values into SQL:

params.py
# WRONG — SQL injection
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# RIGHT — parameterized
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))
params.py
# WRONG — SQL injection
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# RIGHT — parameterized
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

The ?? placeholder lets SQLite handle escaping safely.

Browse the Schema

List tables and columns so users don’t have to guess:

schema.py
def tables(conn):
    return [r[0] for r in conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table'")]
 
def columns(conn, table):
    return [r[1] for r in conn.execute(f"PRAGMA table_info({table})")]
schema.py
def tables(conn):
    return [r[0] for r in conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table'")]
 
def columns(conn, table):
    return [r[1] for r in conn.execute(f"PRAGMA table_info({table})")]

Show these in a side panel; double-click a table to auto-fill SELECT * FROM <table>SELECT * FROM <table>.

Export Results to CSV

export.py
import csv
def export(tree, path="results.csv"):
    cols = tree["columns"]
    with open(path, "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(cols)
        for item in tree.get_children():
            w.writerow(tree.item(item)["values"])
export.py
import csv
def export(tree, path="results.csv"):
    cols = tree["columns"]
    with open(path, "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(cols)
        for item in tree.get_children():
            w.writerow(tree.item(item)["values"])

Common Mistakes

ProblemCauseFix
Accidental data lossRan DROPDROP/DELETEDELETE by mistakeConfirm destructive SQL; open read-only
Old + new rows mixedDidn’t clear the Treeviewdeletedelete all children before inserting
Crash on INSERTINSERT/CREATECREATEcursor.descriptioncursor.description is NoneNoneGuard before reading column names
SQL injection riskf-stringing inputs into SQLUse ?? parameterized queries
Changes don’t persistForgot commit()commit()Commit after write statements
UI freezes on big resultFetching huge tables at oncePage results / fetchmanyfetchmany, or add LIMITLIMIT

Variations to Try

  1. Read-only mode — open the DB so no query can modify it.
  2. Schema browser — clickable tree of tables and columns.
  3. Query history — recall and re-run past queries.
  4. Sortable columns — click a heading to sort by that column.
  5. Multiple DB engines — add MySQL/PostgreSQL via their drivers.
  6. Export/import — CSV and JSON round-trips.
  7. Pagination — LIMITLIMIT/OFFSETOFFSET for large tables.

Real-World Applications

  • Database administration — lightweight DB Browser / TablePlus clone.
  • Internal admin tools — quick data inspection for support teams.
  • Data exploration — ad-hoc querying during development.
  • Teaching SQL — a safe sandbox for learners.

Educational Value

  • Database programming — connections, cursors, transactions.
  • Dynamic UIs — building tables from query metadata.
  • Security — injection, parameterization, read-only access.
  • TreeviewTreeview mastery — Tkinter’s most capable data widget.

Next Steps

  • Add a destructive-SQL confirmation or open read-only.
  • Support parameterized input fields.
  • Build a schema browser and CSV export.
  • Add query history and column sorting.

Conclusion

You built a SQL database viewer that runs arbitrary queries and renders them in a self-adapting table — then made it safe with confirmations, parameterization, and read-only access. The connect → cursor → execute → describe → renderconnect → cursor → execute → describe → render pipeline is the backbone of every database tool, and the cursor.descriptioncursor.description trick is how real viewers handle queries they’ve never seen. Full source on GitHub. Explore more database projects on Python Central Hub.

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did