Displaying MySQL record details in tkinter window after using user entered ID of the row

Tkinter window to display row details from MySQL table based on the user input of record id

Connect to MySQL database

We connected to MySQL database by using our userid, password and database name. You can update your MySQL login details here.

import mysql.connector  # Import the MySQL connector library

my_connect = mysql.connector.connect(
  host="localhost", # Hostname of the MySQL server
  user="userid", # User ID for authentication
  passwd="password", # Password for the user
  database="database_name" # Name of the database to connect to
)

####### end of connection credentials ####

my_cursor = my_connect.cursor() # Initialize the cursor object for executing queries

We will use my_cursor in our further script as the connection object to get our records.
Connect to MySQL database

Adding components to tkinter

First add t1 the Text box to receive student id from the user. This id we will use to collect matching record from the student table.
We will add one Label l1 to display text Enter Student ID: before the text box t1.

l1 = tk.Label(my_w, text='Enter Student ID: ', font=['Arial', 10])  
l1.grid(row=1, column=1)  # Position the label at row 1, column 1

t1 = tk.Entry(my_w, width=4, bg='yellow', font=['Arial', 18])  
t1.grid(row=1, column=2)  # Position the entry box at row 1, column 2

We need one more Label ( l2) to display the returned details of the student. This Label initially will display the string Output.
We will be displaying data as we are getting from MySQL table, so we will declare one tkinter string variable ( StringVar() ) connected to our Label. We declared my_str = tk.StringVar() for this.

my_str = tk.StringVar() # Create a StringVar object

l2 = tk.Label(my_w, textvariable=my_str, width=30, fg='red')   
l2.grid(row=3, column=1, columnspan=2) # Position the label in the grid

my_str.set('Output') # Assign a default string value

We will add one Button b1 to pass user entered data to a function to get the record details.

click event of the button b1

Once the Button is clicked it will read the data entered in the text box t1 and pass it to the user defined function my_details().

b1 = tk.Button(my_w, text='Show Details',font=['Arial',14],width=10,bg='red',
    command=lambda: my_details(t1.get()))
b1.grid(row=1,column=3,padx=3)

my_details()

On click of the Button b1 , my_details() function will receive the string id as input parameter. This is the id of the student for which all the detail of the record will be taken from student table and displayed by using l2 label.

Here data ( id ) is entered by user through the text field ( t1 ). So before using this data in our database Query, we must check this input data.
Inside the function my_details() we will use try except code blocks to check if the user entered data id is Integer or not. If it is not integer then except: part of the code block will display message Check input.

 try:
	val = int(id) # check input is integer or not
	my_data = (val,) 
 except:
	my_str.set("Check input")

Inside the try block ( the input id is integer ) we will keep one more try and except. In this try block we will keep the database handling part and in except block we will display message Database error. The full code of the function my_details() is here.

def my_details(id): 
    try: 
        val = int(id) # check input is integer or not 
        my_data = (val,) 
        try: 
            query = 'SELECT * FROM student WHERE id=%s' 
            my_cursor.execute(query, my_data) 
            student = my_cursor.fetchone() 
            #print(student) 
            my_str.set(student) 
        except:  
             my_str.set('Database error') 
    except: 
        my_str.set('Check input')

We used SQL with WHERE to collect details of the record by using student id from the student table.

Note that MySQL database executes the query part only and return us one result set. This result set contains details of our row data of input ID.
We used the returned MySQLCursor i.e my_cursor
The full code is here , Change the userid,password and database name of your MySQL database.

import mysql.connector
import tkinter  as tk 
from tkinter import * 
my_connect = mysql.connector.connect(
    host="localhost",
    user="id", 
    passwd="password",
    database="db_name"
)
my_cursor = my_connect.cursor()
####### end of connection ####

my_w = tk.Tk()
my_w.geometry("400x200") 
# add one Label 
l1 = tk.Label(my_w,  text='Enter Student ID: ',font=['Arial',10] )  
l1.grid(row=1,column=1) 
t1 = tk.Entry(my_w, width=4,bg='yellow',font=['Arial',18]) 
t1.grid(row=1,column=2)
b1 = tk.Button(my_w, text='Show Details', font=['Arial',14], width=10,bg='red',
    command=lambda: my_details(t1.get()))
b1.grid(row=1,column=3,padx=3)
my_str = tk.StringVar()
my_str.set('Output here ')
# add one Label
l2 = tk.Label(my_w,  textvariable=my_str, width=30,fg='red', font=['Arial',18])   
l2.grid(row=3,column=1,columnspan=3,pady=10)
my_str.set("Output")
def my_details(id):
    try:
        val = int(id) # check input is integer or not
        my_data=(val,)
        try:
            query = "SELECT * FROM student WHERE id=%s"
            my_cursor.execute(query,my_data)
            student = my_cursor.fetchone()
            #print(student)
            my_str.set(student)
        except : 
             my_str.set("Database error")
    except:
        my_str.set("Check input")
my_w.mainloop()

Using SQLAlchemy full code is here

Replace userid (id), password (pw) and database name(my_db) here.

my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db") # mysql credentials

import tkinter  as tk 
from tkinter import * 
my_w = tk.Tk()
my_w.geometry("400x250") 
l1 = tk.Label(my_w,  text='Enter Student ID: ',font=['Arial',10] )  
l1.grid(row=1,column=1) 
t1 = tk.Entry(my_w, width=4,bg='yellow',font=['Arial',18]) 
t1.grid(row=1,column=2)
b1 = tk.Button(my_w, text='Show Details', font=['Arial',14], width=10,bg='red',
    command=lambda: my_details(t1.get()))
b1.grid(row=1,column=3,padx=5)
my_str = tk.StringVar()
my_str.set('Output here ')
# add one Label
l2 = tk.Label(my_w,  textvariable=my_str, width=30,fg='red', font=['Arial',18])   
l2.grid(row=3,column=1,columnspan=3,pady=10)

from sqlalchemy import create_engine,text
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db") # mysql credentials
my_conn = my_conn.connect()
def my_details(id):
    try:
        id = int(id) # check input is integer or not
        
        try:
            my_data={'my_id':id} # dicitionary with place holder and value
            my_row=my_conn.execute(text("SELECT * FROM student WHERE id=:my_id"),my_data)
            student = my_row.fetchone()
            #print(student)
            my_str.set(student)
        except SQLAlchemyError as e:
            error = str(e.__dict__.get('orig', e)) # get error message 
            print(error)
    except:
        my_str.set("Check input")
my_w.mainloop()

Using Treeview to display data

After collecting record from MySQL table, we can display the same using one Treeview. To display in Treeview we have to arrive at number of columns with header (column ) names. So based on the data returned by the database, we will dynamically create the column header names.
How to create rows and columns dynamically in Treeviews

Displaying record details from database table in Tkinter Treeview when user enters record id

import tkinter as tk
from tkinter import ttk  # required for Treeview
from tkinter import *
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError  # for database errors handling

my_w = tk.Tk()
my_w.geometry("400x250")

l1 = tk.Label(my_w, text='Enter Student ID: ', width=25)
l1.grid(row=1, column=1)

e1 = tk.Entry(my_w, width=4, bg='yellow', font=22)
e1.grid(row=1, column=2, padx=5)

b1 = tk.Button(my_w, text='Show Details', width=15, bg='red',
               command=lambda: my_details(e1.get()))
b1.grid(row=1, column=3, padx=5, pady=20)

my_str = tk.StringVar(value='Output here')

# add one Label
l2 = tk.Label(my_w, textvariable=my_str, width=30, fg='red', font=16)
l2.grid(row=2, column=1, columnspan=3)

my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()

# Global variable to track the Treeview widget
treeview_widget = None


def my_details(id):
    global treeview_widget
    try:
        val = int(id)  # Check input is an integer
        my_data = {'my_id': id}  # Dictionary with placeholder and value
        query = "SELECT * FROM student WHERE id=:my_id"  # Parameterized query
        my_row = my_conn.execute(text(query), my_data)

        row = my_row.fetchone()  # Fetch the first row

        # Clear the previously displayed Treeview, if any
        if treeview_widget:
            treeview_widget.destroy()
            treeview_widget = None

        if row:
            student = list(row)  # Convert tuple to list
            my_str.set(student)  # Display the row data in Label
            l1 = [r for r in my_row.keys()]  # List of column headers

            # Create and display the new Treeview
            treeview_widget = ttk.Treeview(my_w, selectmode='browse')
            treeview_widget.grid(row=4, column=1, columnspan=4, padx=5, pady=20)
            treeview_widget['height'] = 3  # Number of rows to display, default is 10
            treeview_widget['columns'] = l1  # List of columns to display
            treeview_widget['show'] = 'headings'
            for i in l1:
                treeview_widget.column(i, anchor='c', width=70)
                treeview_widget.heading(i, text=i)

            treeview_widget.insert("", 'end', iid=0, values=student)  # Add data
        else:
            my_str.set("No data found for the given ID.")  # Message if no data
    except ValueError:
        my_str.set("Invalid ID. Please enter a number.")  # Input error handling
    except SQLAlchemyError as e:
        my_str.set(f"Database error: {e}")  # Database error handling


my_w.mainloop()

Display MySQL Data in Tkinter GUI Based on ID Input | #Python #Tkinter #PythonGUI

We can display 10 records from student table without any user input.

Displaying records from student table Generating unique reference number or ticket number

View and Download tkinter-mysql-id ipynb file ( .html format )
display rows from MySQL.

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Displaying record from MySQL table based on user entered ID