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 & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: