Query window & display records in Treeview

";require "templates/body_start.php";?>
Libraries to useTreeview, Scrollbar and style uses ttk module

from tkinter import ttkimport tkinter as tk

Connection to Database

from sqlalchemy import create_enginefrom sqlalchemy.exc import SQLAlchemyError

For MySQL connection to database.

my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/db_name")

For SQLite database.

my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

Adding Text box and Button .

my_w = tk.Tk()my_w.geometry("620x500") # width and height of the window my_w.title("www.plus2net.com")font1=['Times',14,'normal']  # font style for Text entry box t1 = tk.Text(my_w,  height=2, width=60,bg='yellow',font=font1)t1.grid(row=0,column=0,padx=5,pady=10) b1=tk.Button(my_w,text='GO',font=18,        command=lambda:my_query(t1.get("1.0",'end')))b1.grid(row=0,column=1)

Assigning default style for Treeview

This is applied to all Treeview widgets.

font2=['Times',14,'normal']style = ttk.Style(my_w) style.theme_use("clam") # set theam to clamstyle.configure("Treeview", background="black",                 fieldbackground="black", foreground="white",font=font2)style.configure('Treeview.Heading', background="PowderBlue") # Header style

The last line in above code adds different background color to headings.
Treeview style →

Function to add records to Treeview

On click of the button the function my_query() will receive the Query ( SQL or Structured Query Language ) as entered by the user in text widget.
Try Except Else Block to handle error →

def my_query(query):    for w in my_w.grid_slaves(1):             w.grid_forget()  # remove all widgets     try:                #query="SELECT * from dt_table where `date` = CURDATE() "        r_set=my_conn.execute(query) # execute query and get record set         l1=[r for r in r_set.keys()] # list of columns from database table        r_set=list(r_set) # List of rows of recrods     except SQLAlchemyError as e: # database error message         #print(e)        error = str(e.__dict__['orig'])        l1=tk.Label(my_w,text=error, fg='red',font=16) #error message         l1.grid(row=1,column=0,columnspan=1,padx=20,pady=20)        print(error) # print error to consolse    else:  # No error is there so display the Treeview         trv=ttk.Treeview(my_w,selectmode='browse',                columns=l1,show='headings',height=15)        trv.grid(row=1,column=0,columnspan=2,padx=20,pady=20)        for i in l1: # List of columns collected from Database            trv.column(i,anchor='w',width=80)             trv.heading(i,text=i)        for row in r_set: # add rows or records             trv.insert('','end',iid=row[0],text=row[0],values=list(row))        # adding vertical Scrollbar to right column of Treeview        vs = ttk.Scrollbar(my_w,orient="vertical", command=trv.yview)        trv.configure(yscrollcommand=vs.set)         vs.grid(row=1,column=1,sticky='ns') # Place to right of Treeview

Full code is here
Copy to clipboard

from tkinter import ttkimport tkinter as tkfrom sqlalchemy import create_engine # connection to databasefrom sqlalchemy.exc import SQLAlchemyErrormy_conn = create_engine("mysql+mysqldb://userid:pw@localhost/db_name")  # Creating tkinter my_wmy_w = tk.Tk()my_w.geometry("720x600") # width and height of the window my_w.title("www.plus2net.com")font1=['Times',14,'normal']  # font style for Text entry box t1 = tk.Text(my_w,  height=5, width=70,bg='yellow',font=font1)t1.grid(row=0,column=0,padx=5,pady=10) b1=tk.Button(my_w,text='GO',font=18,        command=lambda:my_query(t1.get("1.0",'end')))b1.grid(row=0,column=1)## adding style to Treeview ###font2=['Times',14,'normal']style = ttk.Style(my_w) style.theme_use("clam") # set theam to clamstyle.configure("Treeview", background="black",                 fieldbackground="black", foreground="white",font=font2)style.configure('Treeview.Heading', background="PowderBlue") # Header style######Style end #####def my_query(query):    for w in my_w.grid_slaves(1):             w.grid_forget()  # remove all widgets     try:                #query="SELECT * from dt_table where `date` = CURDATE() "        r_set=my_conn.execute(query) # execute query and get record set         l1=[r for r in r_set.keys()] # list of columns from database table        r_set=list(r_set) # List of rows of recrods     except SQLAlchemyError as e: # database error message         #print(e)        error = str(e.__dict__['orig'])        l1=tk.Label(my_w,text=error, fg='red',font=16) #error message         l1.grid(row=1,column=0,columnspan=1,padx=20,pady=20)        print(error) # print error to consolse    else:  # No error is there so display the Treeview         trv=ttk.Treeview(my_w,selectmode='browse',                columns=l1,show='headings',height=15)        trv.grid(row=1,column=0,columnspan=2,padx=20,pady=20)        for i in l1: # List of columns collected from Database            trv.column(i,anchor='w',width=100)             trv.heading(i,text=i)        for row in r_set: # add rows or records             trv.insert('','end',iid=row[0],text=row[0],values=list(row))                # adding vertical Scrollbar to right column of Treeview        vs = ttk.Scrollbar(my_w,orient="vertical", command=trv.yview)        trv.configure(yscrollcommand=vs.set)         vs.grid(row=1,column=1,sticky='ns') # Place to right of Treeviewmy_w.mainloop()

Part II : Copy selected rows or all rows from the Treeview →
Part III : Query window to get reports by joining multiple tables in SQLite →

Sample Queries

Here are some SQL Exercise to try, Sample tables with data are available to download
SQL Date Query →
SQL Select update and delete →



Dynamic Creation of Header & Columns in Treeview » Display MySQL records in Treeview » Pagination of MySQL records in Treeview »
← Displaying MySQL records using Entry or Label ← Delete MySQL record
TreeviewTreeview insert Treeview parent child nodeSelect -Edit-update MySQL Product table using Treeview »