MySQL or Google sheet as data source for Autocomplete

Using Google sheet as data source

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)

Using MySQL database table

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

#my_conn = my_conn = create_engine("mysql+mysqldb://id:password@localhost/my_tutorial")
my_conn = create_engine("sqlite:///G:\\My Drive\\testing\\my_db\\my_db.db")

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

my_list=[(1, 'John Deo', 'Four', 75, 'female'), 
(2, 'Max Ruin', 'Three', 85, 'male'), 
(3, 'Arnold', 'Three', 55, 'male'), 
(4, 'Krish Star', 'Four', 60, 'female')]

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

    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

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

← Subscribe to our YouTube Channel here