MySQL or Google sheet as data source for Autocomplete
Tkinter Autocomplete options using date sources from Google sheets or MySQL database or CSV file
Using Entry box and Listbox we can create one Autocomplete ( suggested matching string as user enters string ).
Part I : Design Layout, adding options and selection of options
part II : Navigation through options, selection of option by right arrow and enter key
Part III : Managing data source to use google sheets, MySQL , CSV file etc.
import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc = pygsheets.authorize(service_account_file=path)
sh = gc.open('my_gsheets1') # Open the google sheet
wk1 = sh[0] # work sheet 1
my_list=wk1.get_col(2,include_tailing_empty=False)
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:password@localhost/my_tutorial")
q="SELECT name FROM student "
my_cursor=my_conn.execute(q)
result=my_cursor.fetchall()
print(result)
my_list=[r for r, in result]
Using SQLite database
In above code only the variable my_conn needs to be changed to SQLite database.
import tkinter as tk
import re
from tkinter import END
my_w = tk.Tk()
my_w.geometry("410x400") # Size of the window
my_w.title("plus2net.com") # Adding a title
font1=('Times',24,'bold') # font size and style
#datasource
import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc = pygsheets.authorize(service_account_file=path)
sh = gc.open('my_gsheets1') # Open the google sheet
wk1 = sh[0] # work sheet 1
my_list=wk1.get_col(2,include_tailing_empty=False)
# MySQL database as source
'''
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
q="SELECT name FROM student "
my_cursor=my_conn.execute(q)
result=my_cursor.fetchall()
print(result)
my_list=[r for r, in result]
'''
print(my_list)
#my_list=['aecde','adba','acbd','abcd','abded','bdbd','baba','bcbc','bdbd']
l0=tk.Label(text='Autocomplete',font=font1) # adding label at top
l0.grid(row=0,column=1)
def my_upd(my_widget): # On selection of option
my_w = my_widget.widget
index = int(my_w.curselection()[0]) # position of selection
value = my_w.get(index) # selected value
e1_str.set(value) # set value for string variable of Entry
l1.delete(0,END) # Delete all elements of Listbox
def my_down(my_widget): # down arrow is clicked
l1.focus() # move focus to Listbox
l1.selection_set(0) # select the first option
e1_str=tk.StringVar() # string variable
e1=tk.Entry(my_w,textvariable=e1_str,font=font1) # entry
e1.grid(row=1,column=1,padx=10,pady=0)
# listbox
l1 = tk.Listbox(my_w,height=6,font=font1,relief='flat',
bg='SystemButtonFace',highlightcolor= 'SystemButtonFace')
l1.grid(row=2,column=1)
def get_data(*args): # populate the Listbox with matching options
search_str=e1.get() # user entered string
l1.delete(0,END) # Delete all elements of Listbox
for element in my_list:
if(re.match(search_str,element,re.IGNORECASE)):
l1.insert(tk.END,element) # add matching options to Listbox
#l1.bind('<<ListboxSelect>>', my_upd)
e1.bind('<Down>', my_down) # down arrow key is pressed
l1.bind('<Right>', my_upd) # right arrow key is pressed
l1.bind('<Return>', my_upd)# return key is pressed
e1_str.trace('w',get_data) #
#print(my_w['bg']) # reading background colour of window
my_w.mainloop() # Keep the window open
Other attributes of the selected option
On selection of the option we have to display the other parameters of the product ( or student ). If the student name is selected then the mark , class for the selected student is to be displayed.
From the above formatted list we will show the Names i.e my_list[i][1] as options for the autocomplete to select. For populating the choices we will use like this.
for element in my_list:
if(re.match(search_str,element[1],re.IGNORECASE)):
l1.insert(tk.END,element[1]) # add matching options to Listbox
Once the selection is done by the user , inside the function my_upd() the matching values are collected.
for row in my_list:
if row[1]==value:
print("Mark: ",row[3]," , Id : ",row[0])
l3.config(text=str(row[3])) # update the mark
l4.config(text=row[2]) # update the class
break
We have used config() method to update the text for l3 and l4 Labels to display the mark and class of the selected choice.
Tkinter autocomplete retrieving other matching attributes and displaying on selection of option