Insert a MySQL Record and Add It to Tkinter Treeview

This tutorial extends our Treeview insert() example by connecting the form to a MySQL database. The important difference is that the row is added to Treeview only after MySQL confirms that the database record was inserted successfully.

The workflow is:

Validate input
      |
      v
INSERT into MySQL
      |
      v
Get generated ID
      |
      v
Add confirmed row to Treeview

Project Steps 🔝

1. Create the Tkinter form.
2. Create the Treeview columns.
3. Connect Python to MySQL using SQLAlchemy.
4. Read and validate Name, Class, Mark and Gender.
5. Execute a parameterized INSERT statement.
6. Read the auto-generated MySQL primary key.
7. Use the same database ID when adding the row to Treeview.
8. Clear the form only after a successful insert.

Create the Treeview 🔝

This example displays five columns: ID, Name, Class, Mark and Gender.

import tkinter as tk
from tkinter import ttk

root=tk.Tk()
root.geometry('560x500')
root.title('MySQL Insert and Treeview - plus2net')

tree=ttk.Treeview(root, columns=('id','name','class','mark','gender'), show='headings', selectmode='browse', height=8)

Configure the columns:

tree.column('id', width=50, anchor='center')
tree.column('name', width=120, anchor='w')
tree.column('class', width=90, anchor='center')
tree.column('mark', width=70, anchor='center')
tree.column('gender', width=90, anchor='center')

Add the headings:

tree.heading('id', text='ID')
tree.heading('name', text='Name')
tree.heading('class', text='Class')
tree.heading('mark', text='Mark')
tree.heading('gender', text='Gender')

The Treeview starts empty. We do not add a sample row that has not been saved in MySQL.

Create the Input Form 🔝

Name and Mark are single-line values, so Entry widgets are sufficient.

name_var=tk.StringVar()
class_var=tk.StringVar()
mark_var=tk.StringVar()
gender_var=tk.StringVar()

Name

tk.Entry(root, textvariable=name_var, width=18)

Class using OptionMenu

class_menu=tk.OptionMenu(root, class_var, 'Three', 'Four', 'Five')

See Tkinter OptionMenu for more examples.

Gender using Radiobuttons

tk.Radiobutton(root, text='Male', variable=gender_var, value='Male')
tk.Radiobutton(root, text='Female', variable=gender_var, value='Female')

See Tkinter Radiobutton.

Create the SQLAlchemy Engine 🔝

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

engine=create_engine('mysql+mysqldb://id:pw@localhost/my_db')

Replace the database username, password, host and database name with the values used by your MySQL installation.

We create the Engine once. A database connection is acquired only when the INSERT is executed.

Validate User Input 🔝

Read the values and remove leading or trailing spaces.

my_name=name_var.get().strip()
my_class=class_var.get().strip()
my_mark=mark_var.get().strip()
my_gender=gender_var.get().strip()

Check the required text fields:

if len(my_name)<2:
    show_status('Enter a valid name.', 'red')
    return

if not my_class:
    show_status('Select a class.', 'red')
    return

if not my_gender:
    show_status('Select a gender.', 'red')
    return

Validate Mark as Integer

try:
    mark_value=int(my_mark)
except ValueError:
    show_status('Mark must be an integer.', 'red')
    return

This is clearer than using a generic validation flag and a bare except:.

Insert the Record into MySQL 🔝

Use named placeholders rather than building SQL from user-entered strings.

query=text('''INSERT INTO student
(name,class,mark,gender)
VALUES (:name,:class,:mark,:gender)''')

data={
    'name':my_name,
    'class':my_class,
    'mark':mark_value,
    'gender':my_gender
}

Execute the INSERT inside a transaction:

with engine.begin() as conn:
    result=conn.execute(query,data)
    new_id=result.lastrowid

Get the MySQL Auto-Increment ID 🔝

If the student table uses an auto-increment primary key, MySQL assigns the new ID during the INSERT.

new_id=result.lastrowid

For example:

42

This value represents the actual database record, so it is useful both as the displayed ID and as the Treeview item's iid.

Add the Confirmed Record to Treeview 🔝

Only after the database transaction succeeds do we add the row:

tree.insert('', tk.END, iid=str(new_id), values=(new_id,my_name,my_class,mark_value,my_gender))

The Treeview row and the MySQL row now share the same ID.

Why Use engine.begin()? 🔝

The INSERT is wrapped in:

with engine.begin() as conn:
    ...

If the INSERT succeeds, the transaction is committed when the block completes. If a database exception occurs, the transaction is rolled back.

This also avoids keeping one database connection open for the full life of the Tkinter application.

Complete MySQL Insert and Treeview Program 🔝

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

engine=create_engine('mysql+mysqldb://id:pw@localhost/my_db')

root=tk.Tk()
root.geometry('560x500')
root.title('MySQL Insert and Treeview - plus2net')

name_var=tk.StringVar()
class_var=tk.StringVar()
mark_var=tk.StringVar()
gender_var=tk.StringVar()
status_var=tk.StringVar()

def show_status(message,color):
    status_var.set(message)
    status_label.config(fg=color)
    root.after(3000,lambda:status_var.set(''))

def clear_form():
    name_var.set('')
    class_var.set('')
    mark_var.set('')
    gender_var.set('')
    name_entry.focus_set()

def add_data():
    my_name=name_var.get().strip()
    my_class=class_var.get().strip()
    my_mark=mark_var.get().strip()
    my_gender=gender_var.get().strip()

    if len(my_name)<2:
        show_status('Enter a valid name.','red')
        return

    if not my_class:
        show_status('Select a class.','red')
        return

    if not my_gender:
        show_status('Select a gender.','red')
        return

    try:
        mark_value=int(my_mark)
    except ValueError:
        show_status('Mark must be an integer.','red')
        return

    query=text('''INSERT INTO student
    (name,class,mark,gender)
    VALUES (:name,:class,:mark,:gender)''')

    data={'name':my_name,'class':my_class,'mark':mark_value,'gender':my_gender}

    try:
        with engine.begin() as conn:
            result=conn.execute(query,data)
            new_id=result.lastrowid
    except SQLAlchemyError as e:
        show_status(f'Database error: {e}','red')
        return

    if new_id is None:
        show_status('Record was not confirmed by the database.','red')
        return

    tree.insert('',tk.END,iid=str(new_id),values=(new_id,my_name,my_class,mark_value,my_gender))
    clear_form()
    show_status(f'Record added. ID: {new_id}','green')

tree=ttk.Treeview(root,columns=('id','name','class','mark','gender'),show='headings',selectmode='browse',height=8)
tree.grid(row=0,column=0,columnspan=4,padx=20,pady=20)

tree.column('id',width=50,anchor='center')
tree.column('name',width=120,anchor='w')
tree.column('class',width=90,anchor='center')
tree.column('mark',width=70,anchor='center')
tree.column('gender',width=90,anchor='center')

tree.heading('id',text='ID')
tree.heading('name',text='Name')
tree.heading('class',text='Class')
tree.heading('mark',text='Mark')
tree.heading('gender',text='Gender')

tk.Label(root,text='Add Student',font=('Helvetica',16)).grid(row=1,column=0,columnspan=4,pady=8)

tk.Label(root,text='Name:').grid(row=2,column=0,sticky='e',padx=5,pady=5)
name_entry=tk.Entry(root,textvariable=name_var,width=18)
name_entry.grid(row=2,column=1,sticky='w')

tk.Label(root,text='Class:').grid(row=2,column=2,sticky='e',padx=5)
class_menu=tk.OptionMenu(root,class_var,'Three','Four','Five')
class_menu.grid(row=2,column=3,sticky='w')

tk.Label(root,text='Mark:').grid(row=3,column=0,sticky='e',padx=5,pady=5)
tk.Entry(root,textvariable=mark_var,width=8).grid(row=3,column=1,sticky='w')

gender_frame=tk.Frame(root)
gender_frame.grid(row=3,column=2,columnspan=2,sticky='w')
tk.Radiobutton(gender_frame,text='Male',variable=gender_var,value='Male').pack(side=tk.LEFT)
tk.Radiobutton(gender_frame,text='Female',variable=gender_var,value='Female').pack(side=tk.LEFT)

tk.Button(root,text='Add Record',command=add_data).grid(row=4,column=1,pady=12)

status_label=tk.Label(root,textvariable=status_var)
status_label.grid(row=5,column=0,columnspan=4,pady=5)

name_entry.focus_set()
root.mainloop()

Common MySQL Insert and Treeview Errors 🔝

1. Adding the Treeview Row before MySQL Succeeds

A database failure would leave the GUI showing a record that was never saved.

Correct order:

INSERT into MySQL
get new_id
tree.insert(...)

2. Forgetting to Commit the INSERT

Using:

with engine.begin() as conn:

provides a transaction that commits when the block completes successfully.

3. Leaving a Connection Open for the Entire GUI Session

Create the Engine once and acquire a connection when database work is required.

4. Keeping Whitespace from a Text Widget

The older code used:

t1.get('1.0',END)

which can include a trailing newline. Single-line Entry widgets and .strip() avoid this issue.

5. Using a Bare except

Use the expected exception:

except ValueError:

when checking integer conversion.

6. Clearing the Form before the Database Operation Succeeds

Only clear the form after MySQL returns successfully. If an error occurs, the user's values remain available for correction or retry.

7. Showing a Hard-Coded Treeview Row

If the purpose of the page is to demonstrate rows confirmed by MySQL, don't display records that were not retrieved from or inserted into the database.

8. Using String Formatting to Build SQL

Use bound parameters:

VALUES (:name,:class,:mark,:gender)

and pass the values separately.

Frequently Asked Questions 🔝

MySQL Insert and Treeview Summary 🔝

• Validate Tkinter form values before accessing MySQL.
• Strip whitespace from user-entered strings.
• Convert Mark explicitly to an integer.
• Use parameterized SQL through text().
• Create the SQLAlchemy Engine once.
• Use engine.begin() for the INSERT transaction.
• Read the generated MySQL primary key with lastrowid.
• Add the row to Treeview only after MySQL confirms the insert.
• The database primary key can also be used as the Treeview iid.
• Do not clear the form when the database insert fails.
• Do not show hard-coded rows that are not synchronized with the database.
• The shared database/Treeview ID makes later Delete and Update operations easier.

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: