Tkinter SQLite Query Window with SQLAlchemy

Q: How do I open a child query window in Tkinter?

Use tk.Toplevel(). The child window can contain a Text widget for entering SQL and Buttons for running or clearing the statement.

Q: Why use SQLAlchemy text() when executing SQL?

text() converts the SQL string into an executable SQLAlchemy text statement for the current execution API.

Q: How do I know whether an SQL statement returned rows?

Check result.returns_rows. When it is true, result column names and records can be read and displayed.

Tkinter Toplevel query window for SQLite connector

This project extends the Tkinter SQLite connector from Part 1. After connecting to an SQLite database, the user can open a child window using Toplevel, enter an SQL statement and display the result in the main Tkinter window.

Queries that return records are displayed in a Treeview. Statements such as INSERT, UPDATE, DELETE or CREATE are committed and the number of affected rows is reported when available.

SQLite database
      |
      v
connection.py
      |
      v
Main Tkinter window
      |
      +--> table list
      |
      +--> Query button
              |
              v
          Toplevel
              |
              v
          SQL Text
              |
              v
       SQLAlchemy text()
              |
              v
       result.returns_rows
         /           \
       yes            no
        |              |
     Treeview       commit
                     |
                 rows affected

Database changes: this is a local SQL query tool. INSERT, UPDATE, DELETE, CREATE, DROP and other non-read-only statements can modify the selected database. The application asks for confirmation before executing them.

Show Table of Contents

Connection from Part 1
Display SQLite Tables
Open the Toplevel Query Window
Retain and Edit SQL
Execute SQL with text()
Detect Result Rows Correctly
Display Query Results in Treeview
Commit Database Changes
Rollback after SQL Errors
Limit Large Result Displays
Complete Connector Part 2
Example SQL Statements
Video Tutorial
Continue to Part 3
FAQs

Use the SQLite Connection from Part 1 Top ↑

The database browser and connection logic remain in the connection.py file created in Connector Part 1.

The application imports:

from connection import my_connect

and receives:

new_conn,output,file_path=my_connect(
    status
)

The existing Part 1 interface is therefore retained:

my_connect('existing')
my_connect('new')

Display SQLite Table Names Top ↑

After connecting, query SQLite's schema table:

table_sql=text(
    "SELECT name FROM sqlite_master "
    "WHERE type='table' "
    "AND name NOT LIKE 'sqlite_%' "
    "ORDER BY name"
)

result=my_conn.execute(
    table_sql
)

The returned table names are displayed in a Listbox.

Double-clicking a table prepares a query such as:

SELECT * FROM "student" LIMIT 100

The table name comes from SQLite's schema listing rather than free-form user input.

Open the Toplevel Query Window Top ↑

The Query button creates a child window:

query_window=tk.Toplevel(
    root
)

query_window.title(
    'SQLite Query - plus2net'
)

A multiline Text widget is used because SQL statements can contain several lines.

editor=tk.Text(
    query_window,
    width=75,
    height=12,
    wrap='none'
)

Keep the SQL Available for Editing Top ↑

The older version destroyed the child window immediately after Submit. The revised query editor remains open so the same SQL can be modified and executed again.

The current SQL is also stored in a StringVar:

sql_str.set(
    query
)

write query
    |
    v
Run
    |
    v
inspect result
    |
    v
edit query
    |
    v
Run again

Ctrl+Enter also executes the SQL from the query editor.

Execute SQL using SQLAlchemy text() Top ↑

The older code passed SQL directly:

my_conn.execute(query)

The revised version creates an executable SQLAlchemy text statement:

statement=text(
    query
)

result=my_conn.execute(
    statement
)

Because this page is an SQL editor, the user deliberately enters the complete SQL statement. In normal application forms, values collected through Entry, Combobox and other widgets should be passed as SQL parameters rather than concatenated into SQL text.

Use result.returns_rows instead of Searching for SELECT Top ↑

The previous application used:

if query.lower().find(
    'select'
)!=-1:
    ...

This is unreliable. SQLAlchemy already reports whether the executed statement returned records:

if result.returns_rows:
    columns=list(
        result.keys()
    )

    rows=result.fetchmany(
        MAX_DISPLAY_ROWS+1
    )

Display Query Results in Treeview Top ↑

Column names are taken directly from the SQL result:

columns=list(
    result.keys()
)

Safe internal Treeview identifiers are generated:

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

The actual database column names remain visible as Treeview headings.

Previous result rows are removed before showing another query:

tree.delete(
    *tree.get_children()
)

Display BLOB Values Safely Top ↑

Binary database values are displayed using their byte length instead of dumping the complete BLOB into Treeview.

<2048 bytes>

Commit INSERT, UPDATE and DELETE Statements Top ↑

Database-changing statements are committed explicitly:

my_conn.commit()

When rowcount is available, the interface can display:

Rows affected: 3

After schema changes, the table Listbox is refreshed so newly created or removed tables appear immediately.

Rollback after SQL Errors Top ↑

Execution is protected with exception handling.

except SQLAlchemyError as e:
    if my_conn.in_transaction():
        my_conn.rollback()

    original=getattr(
        e,
        'orig',
        e
    )

This avoids depending on an internal expression such as:

e.__dict__['orig']

Limit Large SQL Results in the GUI Top ↑

A query can return a very large number of rows. The GUI therefore uses:

MAX_DISPLAY_ROWS=500

and fetches one additional record:

rows=result.fetchmany(
    MAX_DISPLAY_ROWS+1
)

If more than 500 rows are available, the interface displays the first 500 and reports that the result has been limited.

Complete Tkinter SQLite Connector Part 2 Top ↑

This code continues to use the connection.py file from Part 1. It assumes my_connect(status) returns the SQLAlchemy Connection, status text and database path used by the existing connector project.

import tkinter as tk
from tkinter import messagebox,ttk

from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError

from connection import my_connect

MAX_DISPLAY_ROWS=500

my_conn=None
db_file=''
query_window=None

root=tk.Tk()
root.title('plus2net.com SQLite Connector')
root.geometry('950x620')
root.minsize(760,500)

root.columnconfigure(0,weight=1)
root.rowconfigure(1,weight=1)

sql_str=tk.StringVar(value='SELECT * FROM student LIMIT 20')
path_var=tk.StringVar(value='No database connected')
status_var=tk.StringVar(
    value='Browse and connect to an SQLite database.'
)

def rollback_if_needed():
    if my_conn is not None:
        try:
            if my_conn.in_transaction():
                my_conn.rollback()
        except Exception:
            pass

def close_connection():
    global my_conn

if my_conn is None:
        return

rollback_if_needed()

try:
        my_conn.close()
    except Exception:
        pass

my_conn=None

def clear_results():
    tree.delete(*tree.get_children())
    tree['columns']=()

def display_value(value):
    if value is None:
        return ''

if isinstance(value,(bytes,bytearray,memoryview)):
        return f'<{len(value)} bytes>'

value=str(value)

if len(value)>200:
        return value[:197]+'...'

return value

def show_result(columns,rows):
    clear_results()

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

tree['columns']=column_ids

for column_id,column_name in zip(column_ids,columns):
        tree.heading(
            column_id,
            text=str(column_name)
        )

tree.column(
            column_id,
            width=150,
            minwidth=80,
            anchor='w',
            stretch=False
        )

for row in rows:
        values=[
            display_value(value)
            for value in row
        ]

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

def load_tables(update_status=True):
    if my_conn is None:
        return

table_list.delete(0,tk.END)

try:
        statement=text(
            "SELECT name FROM sqlite_master "
            "WHERE type='table' "
            "AND name NOT LIKE 'sqlite_%' "
            "ORDER BY name"
        )

result=my_conn.execute(statement)

tables=[
            row[0]
            for row in result
        ]

rollback_if_needed()

except SQLAlchemyError as e:
        rollback_if_needed()

original=getattr(
            e,
            'orig',
            e
        )

status_var.set(
            f'Unable to read table list: {original}'
        )

return

for table in tables:
        table_list.insert(
            tk.END,
            table
        )

if update_status:
        status_var.set(
            f'Connected. Tables found: {len(tables)}.'
        )

def connect_database(status):
    global my_conn,db_file

try:
        new_conn,output,file_path=my_connect(
            status
        )
    except Exception as e:
        status_var.set(
            f'Connection error: {e}'
        )
        return

if new_conn is None or output!='ok':
        status_var.set(
            str(output)
            if output
            else 'Database connection cancelled.'
        )
        return

close_connection()

my_conn=new_conn
    db_file=file_path

path_var.set(file_path)
    query_button.config(state=tk.NORMAL)

clear_results()
    load_tables()

def first_keyword(query):
    parts=query.lstrip().split(None,1)

return (
        parts[0].upper()
        if parts
        else ''
    )

def execute_query(query):
    if my_conn is None:
        status_var.set(
            'Connect to an SQLite database first.'
        )
        return

query=query.strip()

if not query:
        status_var.set(
            'Enter an SQL statement.'
        )
        return

keyword=first_keyword(query)

read_only=keyword in {
        'SELECT',
        'EXPLAIN',
        'PRAGMA'
    }

if not read_only:
        confirmed=messagebox.askyesno(
            'Execute database-changing SQL?',
            'This statement may change the SQLite database.\n\n'
            f'{keyword or "SQL"} statement:\n\n'
            f'{query[:300]}\n\n'
            'Execute it?'
        )

if not confirmed:
            status_var.set(
                'SQL execution cancelled.'
            )
            return

clear_results()

try:
        result=my_conn.execute(
            text(query)
        )

if result.returns_rows:
            columns=list(
                result.keys()
            )

rows=result.fetchmany(
                MAX_DISPLAY_ROWS+1
            )

truncated=(
                len(rows)>MAX_DISPLAY_ROWS
            )

rows=rows[
                :MAX_DISPLAY_ROWS
            ]

show_result(
                columns,
                rows
            )

if read_only:
                rollback_if_needed()
            else:
                my_conn.commit()
                load_tables(
                    update_status=False
                )

status_var.set(
                f'Rows displayed: {len(rows)}'
                +(
                    f' | Limited to first '
                    f'{MAX_DISPLAY_ROWS} rows.'
                    if truncated
                    else ''
                )
            )

else:
            rowcount=result.rowcount

my_conn.commit()

load_tables(
                update_status=False
            )

if (
                rowcount is not None
                and rowcount>=0
            ):
                status_var.set(
                    f'Rows affected: {rowcount}.'
                )
            else:
                status_var.set(
                    'Statement executed successfully.'
                )

except SQLAlchemyError as e:
        rollback_if_needed()

original=getattr(
            e,
            'orig',
            e
        )

status_var.set(
            f'SQL error: {original}'
        )

def close_query_window():
    global query_window

if (
        query_window is not None
        and query_window.winfo_exists()
    ):
        query_window.destroy()

query_window=None

def open_query_window():
    global query_window

if my_conn is None:
        status_var.set(
            'Connect to an SQLite database first.'
        )
        return

if (
        query_window is not None
        and query_window.winfo_exists()
    ):
        query_window.lift()
        query_window.focus_force()
        return

query_window=tk.Toplevel(root)
    query_window.title(
        'SQLite Query - plus2net'
    )
    query_window.geometry(
        '700x340'
    )
    query_window.transient(root)

query_window.columnconfigure(
        0,
        weight=1
    )

query_window.rowconfigure(
        1,
        weight=1
    )

ttk.Label(
        query_window,
        text='Enter one SQL statement:'
    ).grid(
        row=0,
        column=0,
        sticky='w',
        padx=10,
        pady=(10,5)
    )

editor=tk.Text(
        query_window,
        wrap='none',
        undo=True
    )

editor.grid(
        row=1,
        column=0,
        sticky='nsew',
        padx=10,
        pady=5
    )

editor.insert(
        '1.0',
        sql_str.get()
    )

editor.focus_set()

button_frame=ttk.Frame(
        query_window,
        padding=10
    )

button_frame.grid(
        row=2,
        column=0,
        sticky='w'
    )

def run_sql(event=None):
        query=editor.get(
            '1.0',
            'end-1c'
        ).strip()

sql_str.set(query)
        execute_query(query)

return 'break'

def clear_editor():
        editor.delete(
            '1.0',
            tk.END
        )

editor.focus_set()

ttk.Button(
        button_frame,
        text='Run SQL',
        command=run_sql
    ).grid(
        row=0,
        column=0,
        padx=(0,5)
    )

ttk.Button(
        button_frame,
        text='Clear',
        command=clear_editor
    ).grid(
        row=0,
        column=1,
        padx=5
    )

ttk.Button(
        button_frame,
        text='Close',
        command=close_query_window
    ).grid(
        row=0,
        column=2,
        padx=5
    )

ttk.Label(
        button_frame,
        text='Ctrl+Enter: Run'
    ).grid(
        row=0,
        column=3,
        padx=(15,0)
    )

editor.bind(
        '<Control-Return>',
        run_sql
    )

query_window.protocol(
        'WM_DELETE_WINDOW',
        close_query_window
    )

def query_selected_table(event=None):
    selection=table_list.curselection()

if not selection:
        return

table_name=table_list.get(
        selection[0]
    )

safe_name=table_name.replace(
        '"',
        '""'
    )

sql_str.set(
        f'SELECT * FROM "{safe_name}" LIMIT 100'
    )

open_query_window()

def close_app():
    close_query_window()
    close_connection()
    root.destroy()

top_frame=ttk.Frame(
    root,
    padding=10
)

top_frame.grid(
    row=0,
    column=0,
    sticky='ew'
)

top_frame.columnconfigure(
    3,
    weight=1
)

ttk.Button(
    top_frame,
    text='Browse & Connect',
    command=lambda:connect_database('existing')
).grid(
    row=0,
    column=0,
    padx=(0,5)
)

ttk.Button(
    top_frame,
    text='New Database',
    command=lambda:connect_database('new')
).grid(
    row=0,
    column=1,
    padx=5
)

query_button=ttk.Button(
    top_frame,
    text='Query Window',
    command=open_query_window,
    state=tk.DISABLED
)

query_button.grid(
    row=0,
    column=2,
    padx=5
)

ttk.Label(
    top_frame,
    textvariable=path_var,
    wraplength=540
).grid(
    row=0,
    column=3,
    sticky='w',
    padx=(15,0)
)

main_frame=ttk.Panedwindow(
    root,
    orient='horizontal'
)

main_frame.grid(
    row=1,
    column=0,
    sticky='nsew',
    padx=10,
    pady=5
)

table_frame=ttk.LabelFrame(
    main_frame,
    text='SQLite Tables',
    padding=5
)

result_frame=ttk.LabelFrame(
    main_frame,
    text='Query Result',
    padding=5
)

main_frame.add(
    table_frame,
    weight=1
)

main_frame.add(
    result_frame,
    weight=4
)

table_frame.columnconfigure(
    0,
    weight=1
)

table_frame.rowconfigure(
    0,
    weight=1
)

table_list=tk.Listbox(
    table_frame,
    exportselection=False,
    width=24
)

table_list.grid(
    row=0,
    column=0,
    sticky='nsew'
)

table_scroll=ttk.Scrollbar(
    table_frame,
    orient='vertical',
    command=table_list.yview
)

table_scroll.grid(
    row=0,
    column=1,
    sticky='ns'
)

table_list.configure(
    yscrollcommand=table_scroll.set
)

table_list.bind(
    '<Double-1>',
    query_selected_table
)

ttk.Label(
    table_frame,
    text='Double-click a table to query it.',
    wraplength=180
).grid(
    row=1,
    column=0,
    columnspan=2,
    sticky='w',
    pady=(5,0)
)

result_frame.columnconfigure(
    0,
    weight=1
)

result_frame.rowconfigure(
    0,
    weight=1
)

tree=ttk.Treeview(
    result_frame,
    show='headings'
)

tree.grid(
    row=0,
    column=0,
    sticky='nsew'
)

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

y_scroll.grid(
    row=0,
    column=1,
    sticky='ns'
)

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

x_scroll.grid(
    row=1,
    column=0,
    sticky='ew'
)

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

ttk.Label(
    root,
    textvariable=status_var,
    wraplength=900
).grid(
    row=2,
    column=0,
    sticky='w',
    padx=10,
    pady=(5,10)
)

root.protocol(
    'WM_DELETE_WINDOW',
    close_app
)

root.mainloop()

Example SQL Statements Top ↑

Display Records Top ↑

SELECT * FROM student LIMIT 20

Filter Records Top ↑

SELECT name,class,mark
FROM student
WHERE mark >= 80
ORDER BY mark DESC

Update a Record Top ↑

UPDATE student
SET mark=90
WHERE id=1

The application asks for confirmation before executing the UPDATE and commits the transaction after successful execution.

Create a Table Top ↑

CREATE TABLE test_table(
    id INTEGER PRIMARY KEY,
    name TEXT
)

After successful execution, the table Listbox is refreshed automatically.

Why result.returns_rows Is Better Top ↑

The old program tried to determine the SQL result type by searching the query text:

Does query contain "select"?

The revised application asks SQLAlchemy about the executed result:

result.returns_rows

Execute SQL
     |
     v
SQLAlchemy Result
     |
     v
result.returns_rows
   /          \
 True         False
  |             |
show rows      commit
in Treeview    changes

Video: Tkinter Child Query Window for SQLite Top ↑

Tkinter child window using Toplevel to execute user entered SQL query on SQLite and display result

Continue the SQLite Connector Project Top ↑

Start with database selection and connection:

SQLite Connector Part 1

Continue with the next SQL connector section:

SQLite Connector Part 3

For a Pandas-based SQLite viewer with sorting and CSV export:

SQLite DataFrame Viewer

Frequently Asked Questions Top ↑

Q1: How do I open a child query window in Tkinter?

Use tk.Toplevel(). The child window can contain a Text widget for entering SQL and Buttons for running or clearing the statement.

Q2: Why use SQLAlchemy text() when executing SQL?

text() converts the SQL string into an executable SQLAlchemy text statement for the current execution API.

Q3: How do I know whether an SQL statement returned rows?

Check result.returns_rows. When it is true, result column names and records can be read and displayed.

Q4: Why does the application commit UPDATE and INSERT statements?

Database-changing statements must be committed so their changes remain in the SQLite database.

Q5: What happens when an SQL statement fails?

The application rolls back the active transaction and displays the database error in the status area.

Q6: Why are only 500 result rows displayed?

Limiting Treeview output prevents very large SQL results from creating thousands of GUI rows and making the interface difficult to use.

Q7: Does the query window remember the previous SQL?

Yes. The current SQL remains in the editor and is also stored in a StringVar so it can be edited and executed again.

Connector Part 1 Connector Part 3

Tkinter Projects

Python Tkinter Text Treeview SQLite Exceptions Python Tutorials

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:

Subscribe to our YouTube Channel here

Tkinter SQLite Query Window using Toplevel and SQLAlchemy