SQL SELECT Query Window with Tkinter Treeview

This project creates a small database query viewer with Tkinter. The user enters a SQL SELECT query, Python executes the query through SQLAlchemy, and the returned columns and rows are displayed dynamically in a Treeview.

The same GUI can work with MySQL or SQLite because the Treeview code only needs two things from the query result:

column headingsrows of data

Required Libraries 🔝

Treeview and Scrollbar are available through ttk.

import tkinter as tkfrom tkinter import ttk, messagebox

For database access:

from sqlalchemy import create_engine, textfrom sqlalchemy.exc import SQLAlchemyError

The responsive version also uses:

import queueimport threading

Connect to MySQL or SQLite 🔝

MySQL

engine=create_engine('mysql+mysqldb://userid:password@localhost/db_name')

See Python MySQL connection for connection details.

SQLite

engine=create_engine('sqlite:///my_db.db')

See SQLite database connection.

Only one of these Engine definitions is required in the program.

Create the SQL Query Box 🔝

A multi-line Text widget is appropriate because SQL queries can span several lines.

query_box=tk.Text(root,height=5,width=80,font=('Times',13))query_box.grid(row=0,column=0,sticky='ew',padx=10,pady=10)

Read the query without the Text widget's trailing newline:

query=query_box.get('1.0','end-1c').strip()

Allow Straightforward SELECT Queries Only 🔝

This tutorial intentionally accepts only a single straightforward SELECT statement.

def validate_query(query):    cleaned=query.strip()    if not cleaned:        return False,'Enter a SELECT query.'    cleaned=cleaned.rstrip(';').strip()    if ';' in cleaned:        return False,'Enter one SQL statement only.'    if not cleaned.lower().startswith('select '):        return False,'Only SELECT queries are allowed in this viewer.'    return True,cleaned

This deliberately excludes statements such as:

UPDATEDELETEINSERTDROPALTER

The goal of this application is viewing reports, not modifying the database.

Execute the Query with SQLAlchemy 🔝

Use a short-lived connection:

with engine.connect() as conn:    result=conn.execute(text(query))    headers=list(result.keys())    rows=[tuple(row) for row in result.fetchmany(MAX_ROWS+1)]

result.keys() gives the returned column headings.

For example:

SELECT id,name,mark FROM student

can return:

['id', 'name', 'mark']

The Treeview can then create exactly three visible columns.

Keep the Tkinter Query Window Responsive 🔝

A database query can take time. If it runs directly inside the button callback, Tkinter cannot process normal window events while the query is waiting.

Run the database work in a worker thread:

threading.Thread(target=query_worker,args=(query,),daemon=True).start()

The worker puts plain result data into a queue:

result_queue.put(('ok',headers,rows,truncated))

Tkinter's main thread checks the queue with after() and updates the Treeview.

Create Treeview Columns from the Query Result 🔝

The returned database headings should be used as visible labels. Internally, use safe unique identifiers:

column_ids=[f'col{i}' for i in range(len(headers))]tree['columns']=column_ids

Configure the visible headings:

for i,(column_id,heading) in enumerate(zip(column_ids,headers),start=1):    heading_text=str(heading).strip() if heading is not None else ''    if not heading_text:        heading_text=f'Column {i}'    tree.heading(column_id,text=heading_text)    tree.column(column_id,width=120,minwidth=70,anchor='w')

Why Not Use row[0] as iid?

An arbitrary query can return:

SELECT class,COUNT(*) FROM student GROUP BY class

or:

SELECT name,name FROM student

The first returned value is not guaranteed to be a unique primary key.

For a general SQL report viewer, let Treeview create its own item IDs:

tree.insert('',tk.END,values=row)

This differs from our dedicated MySQL student-record Treeview, where we know that student.id is a unique primary key.

Vertical and Horizontal Scrollbars 🔝

A dynamic query may return many rows or many columns, so both scrollbars are useful.

ys=ttk.Scrollbar(result_frame,orient='vertical',command=tree.yview)xs=ttk.Scrollbar(result_frame,orient='horizontal',command=tree.xview)tree.configure(yscrollcommand=ys.set,xscrollcommand=xs.set)

Use a Custom Treeview Style 🔝

A named style prevents this project from changing unrelated Treeview widgets.

style=ttk.Style(root)if 'clam' in style.theme_names():    style.theme_use('clam')style.configure('Query.Treeview',background='black',fieldbackground='black',foreground='white',rowheight=26)style.configure('Query.Treeview.Heading',background='PowderBlue',foreground='black')

See Treeview style for more options.

Limit Large Query Results 🔝

An unrestricted SELECT query can return thousands or millions of rows. Displaying all of them in one desktop Treeview is usually not useful.

The complete example uses:

MAX_ROWS=500

and retrieves at most:

MAX_ROWS+1

rows for the GUI.

If the extra row exists, the interface reports:

Showing first 500 rows. Add LIMIT to the SQL query for a smaller result.

Complete SQL SELECT Query Viewer 🔝

import queueimport threadingimport tkinter as tkfrom tkinter import ttk, messageboxfrom sqlalchemy import create_engine, textfrom sqlalchemy.exc import SQLAlchemyErrorengine=create_engine('mysql+mysqldb://userid:password@localhost/db_name')# SQLite alternative:# engine=create_engine('sqlite:///my_db.db')MAX_ROWS=500result_queue=queue.Queue()root=tk.Tk()root.geometry('900x620')root.title('SQL SELECT Query Viewer - plus2net')root.rowconfigure(1,weight=1)root.columnconfigure(0,weight=1)query_frame=ttk.Frame(root)query_frame.grid(row=0,column=0,sticky='ew',padx=10,pady=10)query_frame.columnconfigure(0,weight=1)query_box=tk.Text(query_frame,height=5,font=('Times',13),wrap='none')query_box.grid(row=0,column=0,sticky='ew')query_box.insert('1.0','SELECT id,name,class,mark,gender FROM student ORDER BY id LIMIT 20')button_frame=ttk.Frame(query_frame)button_frame.grid(row=0,column=1,sticky='ns',padx=(10,0))run_btn=tk.Button(button_frame,text='Run SELECT',command=lambda:run_query())run_btn.pack(fill=tk.X,pady=(0,6))clear_btn=tk.Button(button_frame,text='Clear',command=lambda:query_box.delete('1.0',tk.END))clear_btn.pack(fill=tk.X)style=ttk.Style(root)if 'clam' in style.theme_names():    style.theme_use('clam')style.configure('Query.Treeview',background='black',fieldbackground='black',foreground='white',rowheight=26)style.configure('Query.Treeview.Heading',background='PowderBlue',foreground='black')result_frame=ttk.Frame(root)result_frame.grid(row=1,column=0,sticky='nsew',padx=10)result_frame.rowconfigure(0,weight=1)result_frame.columnconfigure(0,weight=1)tree=ttk.Treeview(result_frame,show='headings',selectmode='browse',style='Query.Treeview')tree.grid(row=0,column=0,sticky='nsew')ys=ttk.Scrollbar(result_frame,orient='vertical',command=tree.yview)ys.grid(row=0,column=1,sticky='ns')xs=ttk.Scrollbar(result_frame,orient='horizontal',command=tree.xview)xs.grid(row=1,column=0,sticky='ew')tree.configure(yscrollcommand=ys.set,xscrollcommand=xs.set)status_var=tk.StringVar(value='Ready - SELECT queries only')status_label=tk.Label(root,textvariable=status_var,anchor='w')status_label.grid(row=2,column=0,sticky='ew',padx=10,pady=8)def validate_query(query):    cleaned=query.strip()    if not cleaned:        return False,'Enter a SELECT query.'    cleaned=cleaned.rstrip(';').strip()    if ';' in cleaned:        return False,'Enter one SQL statement only.'    if not cleaned.lower().startswith('select '):        return False,'Only SELECT queries are allowed.'    return True,cleaneddef run_query(event=None):    query=query_box.get('1.0','end-1c')    valid,result=validate_query(query)    if not valid:        messagebox.showwarning('Query',result)        return    run_btn.config(state=tk.DISABLED)    clear_btn.config(state=tk.DISABLED)    query_box.config(state=tk.DISABLED)    status_var.set('Running query...')    threading.Thread(target=query_worker,args=(result,),daemon=True).start()def query_worker(query):    try:        with engine.connect() as conn:            result=conn.execute(text(query))            headers=list(result.keys())            rows=[tuple(row) for row in result.fetchmany(MAX_ROWS+1)]        truncated=len(rows)>MAX_ROWS        if truncated:            rows=rows[:MAX_ROWS]        result_queue.put(('ok',headers,rows,truncated))    except SQLAlchemyError as e:        detail=str(e.orig) if getattr(e,'orig',None) else str(e)        result_queue.put(('error',detail,None,False))def show_results(headers,rows):    for item in tree.get_children():        tree.delete(item)    column_ids=[f'col{i}' for i in range(len(headers))]    tree['columns']=column_ids    tree['show']='headings'    for i,(column_id,heading) in enumerate(zip(column_ids,headers),start=1):        heading_text=str(heading).strip() if heading is not None else ''        if not heading_text:            heading_text=f'Column {i}'        width=max(90,min(220,len(heading_text)*10+30))        tree.heading(column_id,text=heading_text)        tree.column(column_id,width=width,minwidth=70,anchor='w')    for row in rows:        tree.insert('',tk.END,values=row)def check_results():    try:        status,data,rows,truncated=result_queue.get_nowait()    except queue.Empty:        pass    else:        run_btn.config(state=tk.NORMAL)        clear_btn.config(state=tk.NORMAL)        query_box.config(state=tk.NORMAL)        if status=='ok':            headers=data            show_results(headers,rows)            if truncated:                status_var.set(f'Showing first {MAX_ROWS} rows. Add LIMIT to the query for a smaller result.')            else:                status_var.set(f'Query complete: {len(rows)} row(s), {len(headers)} column(s).')        else:            status_var.set('Query failed.')            messagebox.showerror('SQL Error',data)    root.after(100,check_results)def on_close():    engine.dispose()    root.destroy()query_box.bind('<Control-Return>',run_query)root.protocol('WM_DELETE_WINDOW',on_close)root.after(100,check_results)root.mainloop()

Sample SELECT Queries 🔝

Display Student Records

SELECT id,name,class,mark,genderFROM studentORDER BY idLIMIT 20

Select Students by Mark

SELECT id,name,class,markFROM studentWHERE mark >= 75ORDER BY mark DESC

Group Records by Class

SELECT class,COUNT(*) AS total_studentsFROM studentGROUP BY classORDER BY class

Change the Returned Columns

SELECT name,markFROM studentORDER BY mark DESCLIMIT 10

The Treeview automatically changes from five columns to two because the layout is generated from the query result.

Date Query Exercises

More SELECT queries can be tested using the existing Plus2net SQL exercises:

Common SQL Query Window Mistakes 🔝

1. Allowing DELETE, UPDATE or DROP from an Open Query Box

A report viewer should normally use SELECT-only credentials and a SELECT-only interface.

2. Treating a String Check as Database Security

This:

query.lower().startswith('select ')

is an interface restriction, not a replacement for database permissions.

3. Using engine.execute()

Use:

with engine.connect() as conn:    result=conn.execute(text(query))

4. Keeping One Database Connection Open

Let the Engine manage connections and open them only when required.

5. Recreating Treeview after Every Query

Create the widget once. Change its columns and replace its rows.

6. Assuming the First Returned Column Is Unique

A reporting query can return aggregates, names, dates or duplicated values. Let Treeview generate item IDs.

7. Using Database Headings as Internal Treeview IDs

Queries can return blank or duplicate column labels. Use internal IDs such as col0 and col1.

8. Running a Slow Query in Tkinter's Main Thread

The GUI can become unresponsive. The complete example performs database work in a worker thread.

9. Returning Too Many Records

Use WHERE and LIMIT in the SQL query rather than trying to display an unnecessarily large report.

10. Using grid_forget() to Remove Old Query Results

There is no need to repeatedly destroy or hide the result widgets. Reuse one Treeview and its existing scrollbars.

Frequently Asked Questions 🔝

SQL Query Treeview Summary 🔝

• The project accepts SQL SELECT queries and displays their results in Treeview.
• Use read-only database credentials whenever possible.
• Do not treat GUI query validation as the database security boundary.
• Use SQLAlchemy engine.connect() and text().
• Use short-lived database connections.
• result.keys() provides query-result column headings.
• Create safe internal Treeview IDs such as col0 and col1.
• Use database headings only as visible column labels.
• Do not assume the first returned column is a unique record ID.
• Create the Treeview and scrollbars once.
• Replace only the columns and rows after each query.
• Use vertical and horizontal scrollbars for large results.
• A custom ttk style prevents unrelated Treeviews from being changed.
• Run database queries outside Tkinter's main event thread.
• Update Tkinter widgets only from the main thread.
• Limit the number of rows sent to the Treeview.
• Use SQL WHERE and LIMIT clauses to keep database queries efficient.
• The same dynamic result viewer can work with MySQL or SQLite.