MySQL or Google sheet as data source for Autocomplete


Youtube Live session on Tkinter


Autocomplete using Entry & Listbox

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.

Using Google sheet as data source

About google authorization API and google sheet
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

MySQL connection and SQLAlchemy
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.
#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")
Change the path in above line to point to your SQLite database location.
Download the SQLite database with sample student table
Full code is here
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

Autocomplete attributes of selected option
On selection of the option we whave to display the other parameters of the product ( or student ). If the student name is selected then the mark , class fo the selected student is to be displayed.

Here we have to change the source format to use.
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')]
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


Tkinter Autocomplete using Entry & Listbox. Autocomplete using colour names as source.
Python Tkinter Projects
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer