Tkinter Query window to run sample SELECT UPDATE DROP SQL commands on SQLite database and display result

Tkinter Query window to execute SQL commands SELECT UPDATE DROP with WHERE to learn and show results

Opening Query window

We will use the Query button to execute the function my_child() to open the child window. Inside this function we will display the Text to collect the user entered SQL.

Executing the query : my_data()

Our function my_data() will receive the query and first it will delete the previous data if any in our main display Text area . Then set the background to white by using config()

We will find out the type of query user has entered, if SELECT word is present then show all the records.

Using one try except code block run the query and set the result.

Finally we will display the result.

The connection to SQLite ( the connection.py file ) is available in Part -1 of SQlite connector.

connector-sqlite-v2

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
import tkinter as tk
from tkinter import END
from connection import my_connect
global my_conn
### connect to database ####
def my_connect_p(status):
    global my_conn
    my_conn,my_output,file=my_connect(status) # connect and collect details. 
    t1.delete('1.0',END)
    t1.update()
    #print(my_output)
    if(my_conn and my_output =='ok'):
        my_output=''
        r_set=my_conn.execute("select name from sqlite_master where type = 'table'")
        #if(len(r_set.fetchall())>0):
        my_list_of_tables=r_set.fetchall() # cursors support sequential access, no go back
        
        if(len(my_list_of_tables)>0):
            for i in my_list_of_tables: #default table list 
                my_output=my_output + str(i[0]) + '\n'
        else:
            my_output="No table found"
        t1.config(bg='white') # show with with background colour as there is no error 
        b2.config(state='normal') # Button for query window
        l1_path.config(text=file) # write the file path 
    else:
        t1.config(bg='red')
        b2.config(state='disabled') # Button for query window
    t1.insert(tk.END,my_output)    
### my_data() # get data from database 
def my_data(query):
    t1.delete('1.0',END)
    t1.update()
    my_output=''
    t1.config(bg='white')
    
    try:
        r_set=my_conn.execute(query)
        print(query.lower().find('select'))
        if(query.lower().find('select')!=-1):
            for i in r_set:
                my_output=my_output + str(i) + '\n'
        else:
            my_output="rows affected: "+str(r_set.rowcount)  
    except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        my_output=error
        t1.config(bg='red')
        #b2.config(state='disabled') # Button for query window
    t1.insert(tk.END,my_output) 
    
### layout design of the widgets in window ##
my_w = tk.Tk()
my_w.geometry("800x1000") # width & height of window
sql_str=tk.StringVar()
font1=('Times',26,'bold')
font2=('Time',18,'normal')
l0=tk.Label(my_w,text='plus2net.com SQLite Connector',font=font1)
l0.grid(row=0,column=1,columnspan=10)
b1=tk.Button(my_w,text='Browse & Connect',width=20,command=lambda:my_connect_p('existing'))
b1.grid(row=1,column=1)
l1_path=tk.Label(my_w,text='')
l1_path.grid(row=1,column=2)
b2=tk.Button(my_w,text='Query window ',state='disabled',command=lambda:my_child())
b2.grid(row=2,column=9,sticky='nw')
b3=tk.Button(my_w,text='New Database',command=lambda:my_connect_p('new'))
b3.grid(row=2,column=10,sticky='nw')

t1=tk.Text(my_w,height=50,width=60)
t1.grid(row=2,column=1,columnspan=8,rowspan=2,padx=10)
###############
def my_child():
    my_w_child=tk.Toplevel(my_w)
    my_w_child.geometry("800x500")
    my_w_child.title('www.plus2net.com')
    
    l1_q=tk.Label(my_w_child,text='Query')
    l1_q.grid(row=1,column=1)
    t1_child=tk.Text(my_w_child, bg='yellow',height=15,width=60,font=font2)
    t1_child.grid(row=2,column=1,padx=10,columnspan=6)
    t1_child.insert(tk.END, sql_str.get())
    b3=tk.Button(my_w_child,text='Submit',bg='lightgreen',
    command=lambda:child_close())
    b3.grid(row=3,column=1)
    b4=tk.Button(my_w_child,text='Show Tables',
    command=lambda:show_query("SELECT name FROM sqlite_master WHERE type='table'"))
    b4.grid(row=3,column=2)
    b5=tk.Button(my_w_child,text='SELECT..',
    command=lambda:show_query("SELECT * FROM table_name WHERE 1"))
    b5.grid(row=3,column=3)
    b6=tk.Button(my_w_child,text='UPDATE..',
    command=lambda:show_query("UPDATE table_name SET column1='' WHERE 1"))
    b6.grid(row=3,column=4)
    b7=tk.Button(my_w_child,text='DROP..',
    command=lambda:show_query("DROP TABLE  table_name"))
    b7.grid(row=3,column=5)
    def child_close():
        sql_str.set(t1_child.get("1.0",'end-1c'))
        my_data(t1_child.get("1.0",'end-1c'))
        #my_w_child.destroy()
    def show_query(q):
        sql_str.set(q)
        t1_child.delete('1.0',END)
        t1_child.update()
        t1_child.insert(tk.END,q)
        #my_data(q)
#sql_str.trace('w',my_data())
my_w.mainloop()

SQLite Connector Part -1 ( Browse and connect OR create database ) SQLite Connector Part -2 ( Query window )

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

EXecuting SQL & SQLite database

Opening Query window

Executing the query : my_data()

Subscribe