Python Forum
Problem with bindnig for query
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem with bindnig for query
#11
I appreciate who ever has taken the time to look at the code, regretfully there has been no help with it.
Cheers.
"Often stumped... But never defeated."
Reply
#12
This is my fault. I said I was going to get back to you and never did.
We have a large number of visitors (15,416 in the past 24 hours), and only a handful of moderators and admins available to handle all.
Sometimes, speaking for myself, I'll say I'm going to get back, and then forget (I often forget where I put my coffee (over 70 YO)).
So, I will do that now, and nothing else until I respond.
Reply
#13
I am having a difficult time trying to run the code properly.
I don't know the formatting rules. For example entering a recipe.
how is it supposed to be structured. If I click on Load Category, I don't know what is happening.
I'm sorry, but don't think I can help here.
Reply
#14
I apologize for the poor structure of the code... I am learning python and tkinter on my own through online documents, tutorials and attempting to code a projects as I go. I am working on learning the proper structure to code better.

I have made a number of changes to the code since I initially posted and I noticed that the button to enter a recipe is disabled in the original code posted and that is why you cannot get to the other frame.

Here is the updated code, all buttons and functions work, (except the print because I am just beginning to read on how to do that)
#-------------------------------------------------------------------------------
# Name:        module1
# Purpose:
#
# Author:      Home
#
# Created:     19/03/2019
# Copyright:   (c) Home 2019
# Licence:     <your licence>
#-------------------------------------------------------------------------------
import os
import tkinter
import sqlite3
import win32print
from tkinter import *
from tkinter import ttk
from tkinter import messagebox
#===================================== MAIN ====================================
def main():
#============================= CONNECT TO DATABASE =============================
    conn = sqlite3.connect('Recipe.db')
    c = conn.cursor()
#========================= CREATE DATABASE WITH TABLES =========================
    c.execute('''CREATE TABLE IF NOT EXISTS Recipes(
    Dish_Type CHAR(50),
    Recipe CHAR(50),
    Cook_Time CHAR(15),
    Serves CHAR(8),
    Ingredients CHAR(10000) ,
    Instructions CHAR(10000));''')
    conn.commit()

    c.execute('''CREATE TABLE IF NOT EXISTS Version(
    Welcome Decimal(10));''')
    conn.commit()

    c.execute('''CREATE TABLE IF NOT EXISTS Welcome(
    Welcome TEXT(2500));''')
    conn.commit()
#================================= MAIN WINDOW =================================
    root = tkinter.Tk()
    root.title('Recipes')
##    image1 =tkinter.PhotoImage(file='Menu.gif')
    w = 1024
    h = 612
    ws = root.winfo_screenwidth()
    hs = root.winfo_screenheight()
    x = (ws/2) - (w/2)
    y = (hs/2) - (h/2)
    root.geometry('%dx%d+%d+%d' % (w, h, x, y))
##    panel1 =tkinter.Label(root, image=image1)
##    panel1.pack(side='top', fill='both', expand='yes')
##    panel1.image = image1
##    root.wm_iconbitmap('recipe.ico')
    class OpenButton(Widget):
        '''Button widget.'''
        def __init__(self, master=None, cnf={}, **kw):
            start_button = tkinter.Button(root, text='Open',
            font='Times 12 bold italic', border = 2,  bd=5, bg = '#F9F8D6',
            fg = '#9A0615', width = 10, height = 2, overrelief='raised',
            command = window_2)
            start_button.pack()
            start_button.place(x=448, y=461)
#================================== WINDOW 2 ===================================
    def window_2():
        root.destroy()
        window_2 = tkinter.Tk()
##        image2 = 'window2.gif'
##        bg_image2 = tkinter.PhotoImage(file=image2)
        w = 1024
        h = 612
        ws = window_2.winfo_screenwidth()
        hs = window_2.winfo_screenheight()
        x = (ws/2) - (w/2)
        y = (hs/2) - (h/2)
        window_2.geometry('%dx%d+%d+%d' % (w, h, x, y))
##        panel2 =tkinter.Label(window_2, image=bg_image2)
##        panel2.pack(side='top', fill='both', expand='yes')
##        panel2.image = bg_image2
        window_2.title('Recipes')
        window_2.resizable(0,0)
##        window_2.wm_iconbitmap('recipe.ico')
#============================== GET RECIPE QUERY ===============================
        def getRecipe(event):
##            window_2.iconify()
            Dish_Type = StringVar()
            Recipe = StringVar()
            Cook_Time = StringVar()
            Serves = StringVar()
#================================== WINDOW 3 ===================================
            def close3():
##                output.delete(0,  END)
                window_2.deiconify()
                window_3.destroy()
            window_3 = tkinter.Toplevel()
            window_3.title('Recipes')
##            window_3.resizable(0,0)
            window_3.wm_iconbitmap('recipe.ico')
            w = 1024
            h = 612
            ws = window_3.winfo_screenwidth()
            hs = window_3.winfo_screenheight()
            x = (ws/2) - (w/2)
            y = (hs/2) - (h/2)
            window_3.geometry('%dx%d+%d+%d' % (w, h, x, y))
##            image3  = 'window3.gif'
##            bg_image3 = tkinter.PhotoImage(file=image3)
##            panel3 = Label(window_3, image=bg_image3)
##            panel3.pack(side='top', fill='both', expand='yes')
##            panel3.image = bg_image3
            window_3.title('Recipes')
##            window_3.wm_iconbitmap('recipe.ico')
            window_3.protocol('WM_DELETE_WINDOW', window_3)
#=================================== PRINTER ===================================
            def printer():
                printer_name = win32print.GetDefaultPrinter ()
                if sys.version_info >= (3,):
                  raw_data = bytes ("This is a test", "utf-8")
                else:
                  raw_data = "This is a test"
                hPrinter = win32print.OpenPrinter (printer_name)
                try:
                  hJob = win32print.StartDocPrinter (hPrinter, 1, ("test of raw data", None, "RAW"))
                  try:
                    win32print.StartPagePrinter (hPrinter)
                    win32print.WritePrinter (hPrinter, raw_data)
                    win32print.EndPagePrinter (hPrinter)
                  finally:
                    win32print.EndDocPrinter (hPrinter)
                finally:
                  win32print.ClosePrinter (hPrinter)


#================================= SAVE RECIPE =================================
            def Save():
                window_2.iconify()
                try:
                    saveInput = Recipe.get()
                    c.execute('SELECT Recipe FROM Recipes WHERE Recipe = :saveInput',
                    {'saveInput': saveInput})
                    conn.commit()
                    saveResult = c.fetchone()
                    if saveResult == None:
                        c.execute('''INSERT INTO Recipes VALUES (?,?,?,?,?,?)''',
                        (Dish_Type.get(), (Recipe.get()),
                        (Cook_Time.get()), (Serves.get()), (ingred.get(1.0, END)),
                        (instruct.get(1.0, END))))
                        conn.commit()
                        messagebox.showinfo('Recipes','Recipe saved successfully.')
                        dish_type_entry.delete(0, END)
                        recipe_entry.delete(0,END)
                        cook_time_entry.delete(0,END)
                        serves_entry.delete(0,END)
                        ingred.delete(1.0,END)
                        instruct.delete(1.0,END)
                    else:
                        messagebox.showinfo('Recipes',
                        'That recipe name already exists in the database.')
                        recipe_entry.delete(0,END)
                except:
                    messagebox.showerror('Recipes','Database Error.')
#================================ CLOSE PROGRAM ================================
            def exit():
                window_2.destroy()
#================================ DELETE RECIPE ================================
            def delete():
                    try:
                        checkInput = Recipe.get()
                        c.execute('SELECT Recipe FROM Recipes WHERE Recipe = :checkInput',
                        {'checkInput': checkInput})
                        conn.commit()
                        theResult = c.fetchone()
                        if theResult == None:
                            messagebox.showinfo('Recipes',
                            'That recipe name does not exist in the database.')
                            recipe_entry.delete(0,END)
                        else:
                            MsgBox =  messagebox.askyesno('Recipes',
                            'Are you sure you want to delete this recipe?',
                            icon = 'warning')
                            if MsgBox == True:
                                messagebox.showinfo('Recipes',
                                'The recipe will now be delete.')
                                c.execute('DELETE FROM Recipes WHERE Recipe =:checkInput',
                                {'checkInput': checkInput})
                                conn.commit()
                                messagebox.showinfo('Recipes',
                                'The recipe has been deleted successfully.')
                                recipe_entry.delete(0,END)
                            else:
                                messagebox.showinfo('Recipes',
                                'Delete has been cancelled.')
                                recipe_entry.delete(0,END)
                    except:
                        messagebox.showerror('Recipes','Database Error.')
            def focus_next_window(event):
                event.widget.tk_focusNext().focus()
                return("break")
            window_2.iconify()
            dish_type = tkinter.Label(window_3, font=('Times 9 bold'),
            text='Category:', bg = '#FFD599', fg = '#9A0615')
            dish_type.pack()
            dish_type.place(x=210, y=125)
            dish_type_entry = tkinter.Entry(window_3, textvariable=Dish_Type,
            width = 29, bg = '#FFD599', fg = '#9A0615', justify=CENTER)
            dish_type_entry.place(x=275, y=126)
            dish_type_entry.focus()
            recipe = tkinter.Label(window_3, font=('Times 9 bold'),
            text='Recipe:', bg = '#FFD599', fg = '#9A0615')
            recipe.pack()
            recipe.place(x=210, y=145)
            recipe_entry = tkinter.Entry(window_3, textvariable=Recipe,
            width = 29, bg = '#FFD599', fg = '#9A0615', justify=CENTER)
            recipe_entry.place(x=275, y=146)
            serves = tkinter.Label(window_3, font=('Times 9 bold'),
            text='Serves:', bg = '#FFD599', fg = '#9A0615')
            serves.pack()
            serves.place(x=547, y=125)
            serves_entry = tkinter.Entry(window_3, textvariable=Serves,
            width = 8, bg = '#FFD599', fg = '#9A0615', justify=CENTER)
            serves_entry.place(x=623, y=126)
            cook_time = tkinter.Label(window_3, font=('Times 9 bold'),
            text='Cook Time:', bg = '#FFD599', fg = '#9A0615')
            cook_time.pack()
            cook_time.place(x=547, y=145)
            cook_time_entry = tkinter.Entry(window_3, textvariable=Cook_Time,
            width = 12, bg = '#FFD599', fg = '#9A0615', justify=CENTER)
            cook_time_entry.place(x=623, y=146)
            ingred = tkinter.Text(window_3, font=('Times 10'), height = 20,
            width=40, bd=1, bg = '#FFD599', fg = '#9A0615',padx=5, pady=5)
            ingred.pack()
            ingred.place(x=210, y=195)
            ingred.bind("<Tab>", focus_next_window)
            yscroll3 = tkinter.Scrollbar(window_3, command=ingred.yview,
            orient=tkinter.VERTICAL)
            yscroll3.place(x=461, y=195)
            ingred.configure(yscrollcommand=yscroll3.set)
            instruct = tkinter.Text(window_3, font=('Times 10'), height = 20,
            width=40, bd=1, bg = '#FFD599', fg = '#9A0615',padx=5, pady=5)
            instruct.pack()
            instruct.place(x=547, y=195)
            yscroll4 = tkinter.Scrollbar(window_3, command=instruct.yview,
            orient=tkinter.VERTICAL)
            yscroll4.place(x=798, y=195)
            instruct.configure(yscrollcommand=yscroll4.set)
            saveButton = tkinter.Button(window_3, text='Save a Recipe',
            font='Times 9 bold italic', border = 1, height = 1, width = 11,
            bd=4, bg = '#F9F8D6', fg = '#9A0615', command = Save)
            saveButton.pack
            saveButton.place(x=215, y=514)
            deleteButton = tkinter.Button(window_3, text='Delete a Recipe',
            font='Times 9 bold italic', border = 1, height = 1, width = 11,
            bd=4, bg = '#F9F8D6', fg = '#9A0615', command = delete)
            deleteButton.pack
            deleteButton.place(x=337, y=514)

            printButton = tkinter.Button(window_3, text='Print Recipe',
            font='Times 9 bold italic', border = 1, height = 1, width = 11,
            bd=4, bg = '#F9F8D6', fg = '#9A0615', command = printer)
            printButton.pack
            printButton.place(x=459, y=514)

            backButton = tkinter.Button(window_3, text='Back a Page',
            font='Times 9 bold italic', border = 1, height = 1, width = 11,
            bd=4, bg = '#F9F8D6', fg = '#9A0615', command = close3)
            backButton.pack
            backButton.place(x=581, y=514)
            exitButton = tkinter.Button(window_3, text='Exit Program',
            font='Times 9 bold italic', border = 1, height = 1, width = 11,
            bd=4, bg = '#F9F8D6', fg = '#9A0615', command = exit)
            exitButton.pack
            exitButton.place(x=703, y=514)
            try:
                dish_type_entry.delete(0,END)
                index = output.curselection()[0]
                selected = output.get(index)
                c.execute('SELECT Dish_Type FROM Recipes WHERE Recipe = :selected',
                {'selected': selected[0]})
                dish_type_result = c.fetchone()
                dish_type_entry.insert(0,dish_type_result)

                recipe_entry.delete(0,END)
                index = output.curselection()[0]
                selected = output.get(index)
                c.execute('SELECT Recipe FROM Recipes WHERE Recipe = :selected',
                {'selected': selected[0]})
                recipe_result = c.fetchone()
                recipe_entry.insert(0,recipe_result[0])

                serves_entry.delete(0,END)
                index = output.curselection()[0]
                selected = output.get(index)
                c.execute('SELECT Serves FROM Recipes WHERE Recipe = :selected',
                {'selected': selected[0]})
                serves_result = c.fetchone()
                serves_entry.insert(0,serves_result[0])

                cook_time_entry.delete(0,END)
                index = output.curselection()[0]
                selected = output.get(index)
                c.execute('SELECT Cook_Time FROM Recipes WHERE Recipe = :selected',
                {'selected': selected[0]})
                cook_time_result = c.fetchone()
                cook_time_entry.insert(0,cook_time_result[0])

                index = output.curselection()[0]
                selected = output.get(index)
                c.execute('SELECT Ingredients FROM Recipes WHERE Recipe = :selected',
                {'selected': selected[0]})
                ingredients_result = c.fetchone()
                ingred.insert(1.0,ingredients_result[0])
                ingred.config(wrap=WORD)

                index = output.curselection()[0]
                selected = output.get(index)
                c.execute('SELECT Instructions FROM Recipes WHERE Recipe = :selected',
                {'selected': selected[0]})
                instructions_result = c.fetchone()
                instruct.insert(1.0, instructions_result[0])
                instruct.config(wrap=WORD)
            except:
                pass
##                messagebox.showerror('DataBase Error', 'Failed to load category')
#=========================== MODULE 2 CLEAR INPUT ==============================
        def clear1():
            output.delete(0,END)
#================================ SELECT QUERY =================================
        def select():
            try:
                output.delete(0, END)
                selection = var.get()
                c.execute('SELECT Recipe FROM Recipes WHERE Dish_Type = :selection ORDER BY Recipe DESC',
                {'selection': selection})
                results = c.fetchall()
                for recipe in results:
                    output.insert(0,recipe)
            except:
                messagebox.showerror('Recipes', 'Failed to load category')
#================================ OPTION MENU ==================================
        var = tkinter.StringVar(window_2)
        var.set('Category')
        choices = [
            'Appetizers',
            'Beef',
            'Beverages',
            'Bakery',
            'Desserts',
            'Eggs',
            'Lamb',
            'Pasta',
            'Pork',
            'Potato',
            'Poultry',
            'Rice',
            'Salads',
            'Sandwiches',
            'Sauces/Dips',
            'Sea Food',
            'Slow Cooker',
            'Soups/Chili',
            'Stews',
            'Sushi',
            'Vegetables',
            'Vegetarian',
            'Wild Game']
        option = tkinter.OptionMenu(window_2, var, *choices)
        option.config(font=('Times 9 bold'), bg = '#F9F8D6', fg = '#9A0615')
        option.place(x=210, y=136)
        option['menu'].config(bg = '#F9F8D6', fg = '#9A0615')
        backer = tkinter.Label(window_2, font=('Times 16 bold'),
        text='                 ')
        backer.pack()
        backer.place(x=360, y=137)
        button = tkinter.Button(window_2, text='Load Category',
        font=('Times 9 bold'), bg = '#F9F8D6', fg = '#9A0615', command=select)
        button.pack
        button.place(x=362, y=139)
#=================================== WINDGETS ==================================
        output = tkinter.Listbox(window_2, font=('Times 10'), height = 20,
        width=42, bd=0, bg = '#FFD599', fg = '#9A0615', selectmode=SINGLE)
        output.pack()
        output.place(x=210, y=195)
        yscroll1 = tkinter.Scrollbar(command=output.yview,
        orient=tkinter.VERTICAL)
        yscroll1.place(x=463, y=196)
        output.configure(yscrollcommand=yscroll1.set)
        output.bind('<ButtonRelease-1>', getRecipe)
        instructions = tkinter.Text(window_2, font=('Times 10'), height = 21,
        width=42, bd=1, bg = '#FFD599', fg = '#9A0615',padx=5, pady=5)
        instructions.pack()
        instructions.place(x=547, y=195)
        yscroll2 = tkinter.Scrollbar(command=instructions.yview,
        orient=tkinter.VERTICAL)
        yscroll2.place(x=810, y=196)
        instructions.configure(yscrollcommand=yscroll2.set)
#=================================== BUTTONS ===================================
        enterButton = tkinter.Button(window_2, text='Enter a Recipe',
        font='Times 9 bold italic', border = 1, height = 1, width = 12, bd=3,
        bg = '#F9F8D6', fg = '#9A0615')##, command = getRecipe)
        enterButton.pack
        enterButton.place(x=293, y=525)
        enterButton.bind('<Button-1>', getRecipe)
        clear1Button = tkinter.Button(window_2, text='Clear Page',
        font='Times 9 bold italic', border = 1, height = 1, width = 12, bd=3,
        bg = '#F9F8D6', fg = '#9A0615', command = clear1)
        clear1Button.pack
        clear1Button.place(x=630, y=525)
#================================ WELCOME PANE =================================
        c.execute('SELECT * FROM Welcome WHERE welcome = Welcome')
        welcome_result2 = c.fetchall()
        for welcome_result in welcome_result2:
            instructions.insert(1.0,welcome_result[0])
            instructions.config(wrap=WORD, state=DISABLED)

    OpenButton()
    root.mainloop()
if __name__ == '__main__':
    main()
The results of that query are inserted properly but are encased in curly brackets.

If I remove the brackets by using a comma between "each_result" and "in" located at line 335 (showing the code here):
for each_result in results: ##If I add a comma between each_result,in will removes the curly brackets

The recipe names inserted in the Listbox are no longer clickable so it can query the database for the recipe selected to be fetched and inserted into the widget in frame 3 (window_3).

That is the issue that I am trying to resolve.
"Often stumped... But never defeated."
Reply
#15
Larz60+, Just wanted to offer a sincere thank you for taking the time to peek at the code. I have finally figured it out the problem after many trials and errors. It seems the indexing during the second query (getRecipe) was the problem.

Thank you for the attempt.
"Often stumped... But never defeated."
Reply
#16
You're welcome. Perhaps you could share the solution, so anyone else with the same issue can benefit.
Reply
#17
Removing the curly bracjet when it inserted the data was resolved by a small change to 2 lins in each query segment of the code.
Removing the [0] in line 3 and 6 was required. The same was true for each query there after in that segment of code.
try:
    dish_type_entry.delete(0,END)
    index = output.curselection()[0]
    selected = output.get(index)
    c.execute('SELECT Dish_Type FROM Recipes WHERE Recipe = :selected',
    {'selected': selected[0]})
    dish_type_result = c.fetchone()
    dish_type_entry.insert(0,dish_type_result[0])

    recipe_entry.delete(0,END)
    index = output.curselection()[0]
    selected = output.get(index)
    c.execute('SELECT Recipe FROM Recipes WHERE Recipe = :selected',
    {'selected': selected[0]})
    recipe_result = c.fetchone()
    recipe_entry.insert(0,recipe_result[0])

    serves_entry.delete(0,END)
    index = output.curselection()[0]
    selected = output.get(index)
    c.execute('SELECT Serves FROM Recipes WHERE Recipe = :selected',
    {'selected': selected[0]})
    serves_result = c.fetchone()
    serves_entry.insert(0,serves_result[0])

    cook_time_entry.delete(0,END)
    index = output.curselection()[0]
    selected = output.get(index)
    c.execute('SELECT Cook_Time FROM Recipes WHERE Recipe = :selected',
    {'selected': selected[0]})
    cook_time_result = c.fetchone()
    cook_time_entry.insert(0,cook_time_result[0])

    index = output.curselection()[0]
    selected = output.get(index)
    c.execute('SELECT Ingredients FROM Recipes WHERE Recipe = :selected',
    {'selected': selected[0]})
    ingredients_result = c.fetchone()
    ingred.insert(1.0,ingredients_result[0])
    ingred.config(wrap=WORD)

    index = output.curselection()[0]
    selected = output.get(index)
    c.execute('SELECT Instructions FROM Recipes WHERE Recipe = :selected',
    {'selected': selected[0]})
    instructions_result = c.fetchone()
    instruct.insert(1.0, instructions_result[0])
    instruct.config(wrap=WORD)
except:
    messagebox.showerror('DataBase Error', 'Failed to load category')
The resolved code is:
            try:
                dish_type_entry.delete(0,END)
                index = output.curselection()
                selected = output.get(index)
                c.execute('SELECT Dish_Type FROM Recipes WHERE Recipe = :selected',
                {'selected': selected})
                dish_type_result = c.fetchone()
                dish_type_entry.insert(0,dish_type_result[0])

                recipe_entry.delete(0,END)
                index = output.curselection()
                selected = output.get(index)
                c.execute('SELECT Recipe FROM Recipes WHERE Recipe = :selected',
                {'selected': selected})
                recipe_result = c.fetchone()
                recipe_entry.insert(0,recipe_result[0])

                serves_entry.delete(0,END)
                index = output.curselection()
                selected = output.get(index)
                c.execute('SELECT Serves FROM Recipes WHERE Recipe = :selected',
                {'selected': selected})
                serves_result = c.fetchone()
                serves_entry.insert(0,serves_result[0])

                cook_time_entry.delete(0,END)
                index = output.curselection()
                selected = output.get(index)
                c.execute('SELECT Cook_Time FROM Recipes WHERE Recipe = :selected',
                {'selected': selected})
                cook_time_result = c.fetchone()
                cook_time_entry.insert(0,cook_time_result[0])

                index = output.curselection()
                selected = output.get(index)
                c.execute('SELECT Ingredients FROM Recipes WHERE Recipe = :selected',
                {'selected': selected})
                ingredients_result = c.fetchone()
                ingred.insert(1.0,ingredients_result[0])
                ingred.config(wrap=WORD)

                index = output.curselection()
                selected = output.get(index)
                c.execute('SELECT Instructions FROM Recipes WHERE Recipe = :selected',
                {'selected': selected})
                instructions_result = c.fetchone()
                instruct.insert(1.0, instructions_result[0])
                instruct.config(wrap=WORD)
            except:
                messagebox.showerror('DataBase Error', 'Failed to load category')
"Often stumped... But never defeated."
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Problem Using SQL Placeholder In MySQL Query AdeS 11 5,929 Jul-31-2021, 12:19 AM
Last Post: Pedroski55
  MySQLdb, problem with query with user-defined variables buran 6 6,315 Feb-03-2017, 06:16 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020