Tkinter Treeview mit sqlite3

Fragen zu Tkinter.
Antworten
Niggels
User
Beiträge: 1
Registriert: Dienstag 1. Juni 2021, 09:12

Moin,
ich komme partout nicht weiter.. Ich habe die zwei verschiedene Tabellen aus einer Datenbank in 2 Tkinter Trees (Verkaufsort und Ware) eingefügt und möchte nun aus diesen zwei Zeilen auswählen können (aus jedem Tree 1) und die jeweiligen Schlüssel mit den jeweiligen Fremdschlüsseln in einer dritten Tabelle (Angebot) vergleichen in dem ich auf den "Angebot" Button klicke. Wenn es ein passendes Angebot gibt möchte ich dieses in eine Listbox in einem Top Level Fenster einfügen. Das Problem ist soweit ich weiß, die in der tl_Angebot Funktion mit Fragezeichen markierte Zeile.

Code: Alles auswählen

from tkinter import *  # tkinter für GUI
import tkinter.ttk as ttk  # tkinter.ttk für Treeview
import sqlite3  # sqlite3 für Datenbankmanagement


# Funktion GUI Layout
def displayForm():
    # creating window
    display_screen = Tk()
    # Höhe und Breite des Fensters
    display_screen.geometry("1500x950")
    # Fenstertitel
    display_screen.title("Datenbankprojekt")
    # Variablendefinition
    global w_tree
    global vk_tree
    global SEARCH
    SEARCH = StringVar()

    # Frames
    TopViewForm = Frame(display_screen, width=400, bd=1, relief=SOLID)
    TopViewForm.pack(side=TOP, fill=X)
    LeftViewForm = Frame(display_screen, width=400)
    LeftViewForm.pack(side=LEFT, fill=Y)
    RightViewForm = Frame(display_screen, width=400)
    RightViewForm.pack(side=RIGHT, fill=Y)
    MidViewForm = Frame(display_screen, width=400, relief=SOLID)
    MidViewForm.pack(side=BOTTOM)

    # Header Label
    lbl_text = Label(TopViewForm, text="Datenbank Lebensmittel", font=('verdana', 18), width=600, bg="#1C2833",fg="white")
    lbl_text.pack(fill=X)

    # Labels
    lbl_serachbox = Label(TopViewForm, text = "Suchfeld", font = ('verdana',10))
    lbl_serachbox.pack(side=TOP)
    w_lbl_txtsearch = Label(TopViewForm, text="Warensuche", font=('verdana', 10))
    w_lbl_txtsearch.pack(side=LEFT, anchor=W)
    vk_lbl_txtsearch = Label(TopViewForm, text="Verkaufsortsuche", font=('verdana', 10))
    vk_lbl_txtsearch.pack(side=RIGHT, anchor=W)

    # Entryfeld
    w_search = Entry(TopViewForm, textvariable=SEARCH, font=('verdana', 12), width=10)
    w_search.pack(side=TOP)

    # Warenkorb Listbox
    wk_listbox = Listbox(MidViewForm, relief=RAISED)
    wk_listbox.pack(side=BOTTOM)

    # Buttons
    # Ware
    w_btn_search = Button(TopViewForm, text="Search", command=lambda: [w_searchRecord(), w_search.delete(0, END)])
    w_btn_search.pack(side=LEFT, padx=10, pady=10, fill=X)
    w_btn_search = Button(TopViewForm, text="View All", command=lambda: [w_displayData(), w_search.delete(0, END)])
    w_btn_search.pack(side=LEFT, padx=10, pady=10, fill=X)
    a_btn_search = Button(TopViewForm, text="Angebot", command=lambda: [tl_Angebot(), w_search.delete(0, END)])
    a_btn_search.pack(side=LEFT, padx=10, pady=10, fill=X)

    # Verkaufsort
    vk_btn_search = Button(TopViewForm, text="Search", command=lambda: [vk_searchRecord(), w_search.delete(0, END)])
    vk_btn_search.pack(side=RIGHT, padx=10, pady=10, fill=X)
    vk_btn_search = Button(TopViewForm, text="View All", command=lambda: [vk_displayData(), w_search.delete(0, END)])
    vk_btn_search.pack(side=RIGHT, padx=10, pady=10, fill=X)

    # w tree
    # wscrollbar
    w_scrollbarx = Scrollbar(LeftViewForm, orient=HORIZONTAL)
    w_scrollbary = Scrollbar(LeftViewForm, orient=VERTICAL)
    w_tree = ttk.Treeview(LeftViewForm, columns=("wID", "Name", "Verkaufsmenge"),
                          selectmode="extended", height=100, yscrollcommand=w_scrollbary.set, xscrollcommand=w_scrollbarx.set)
    w_scrollbary.config(command=w_tree.yview)
    w_scrollbary.pack(side=RIGHT, fill=Y)
    w_scrollbarx.config(command=w_tree.xview)
    w_scrollbarx.pack(side=BOTTOM, fill=X)

    # wHeadings
    w_tree.heading('wID', text="wID", anchor=W)
    w_tree.heading('Name', text="Name", anchor=W)
    w_tree.heading('Verkaufsmenge', text="Verkaufsmenge", anchor=W)

    # w Column height
    w_tree.column('#0', stretch=NO, minwidth=0, width=0)
    w_tree.column('#1', stretch=NO, minwidth=0, width=100)
    w_tree.column('#2', stretch=NO, minwidth=0, width=150)

    w_tree.pack(side=LEFT)
    w_displayData()

    # vk tree
    # vk scrollbar
    vk_scrollbarx = Scrollbar(RightViewForm, orient=HORIZONTAL)
    vk_scrollbary = Scrollbar(RightViewForm, orient=VERTICAL)
    vk_tree = ttk.Treeview(RightViewForm, columns=("vkID", "Name", "Entfernung in m","Adresse","Öffnungszeiten"),
                           selectmode="extended", height=100, yscrollcommand=vk_scrollbary.set, xscrollcommand=vk_scrollbarx.set)
    vk_scrollbary.config(command=vk_tree.yview)
    vk_scrollbary.pack(side=RIGHT, fill=Y)
    vk_scrollbarx.config(command=vk_tree.xview)
    vk_scrollbarx.pack(side=BOTTOM, fill=X)

    # vk headings
    vk_tree.heading('vkID', text="vkID", anchor=W)
    vk_tree.heading('Name', text="Name", anchor=W)
    vk_tree.heading('Entfernung in m', text="Entfernung in m", anchor=W)
    vk_tree.heading('Adresse', text="Adresse", anchor=W)
    vk_tree.heading('Öffnungszeiten', text="Öffnungszeiten", anchor=W)

    # vk column height
    vk_tree.column('#0', stretch=NO, minwidth=0, width=0)
    vk_tree.column('#1', stretch=NO, minwidth=0, width=100)
    vk_tree.column('#2', stretch=NO, minwidth=0, width=150)
    vk_tree.column('#3', stretch=NO, minwidth=0, width=80)
    vk_tree.column('#4', stretch=NO, minwidth=0, width=120)
    vk_tree.pack(side=RIGHT)
    vk_displayData()

def tl_Angebot():
    global a_tree
    top = Toplevel()
    top.geometry('300x300')

    topframe = Frame(top)
    topframe.pack()

    top_header = Label(top, text="Angebot")
    top_header.pack()

    # a Tree
    # Scrollbar
    a_scrollbarx = Scrollbar(topframe, orient=HORIZONTAL)
    a_scrollbary = Scrollbar(topframe, orient=VERTICAL)
    a_tree = ttk.Treeview(topframe, columns=("aID", "fk_wID", "Preis", "fk_vkID"),
                          selectmode="extended", height=100, yscrollcommand=a_scrollbary.set,
                          xscrollcommand=a_scrollbarx.set)
    a_scrollbary.config(command=a_tree.yview)
    a_scrollbary.pack(side=RIGHT, fill=Y)
    a_scrollbarx.config(command=a_tree.xview)
    a_scrollbarx.pack(side=BOTTOM, fill=X)

    # a headings
    a_tree.heading('aID', text="wID", anchor=W)
    a_tree.heading('fk_wID', text="fk_wID", anchor=W)
    a_tree.heading('Preis', text="Preis", anchor=W)
    a_tree.heading('fk_vkID', text="fk_vkID", anchor=W)

    # a column height
    a_tree.column('#0', stretch=NO, minwidth=0, width=0)
    a_tree.column('#1', stretch=NO, minwidth=0, width=100)
    a_tree.column('#2', stretch=NO, minwidth=0, width=150)

    a_tree.pack(side=LEFT)

    # clear
    a_tree.delete(*a_tree.get_children())
    # open Database
    conn = sqlite3.connect('Preisliste Datablanka.db')
    # select query
##????????????????????????????????????????????????????????????????????????????????##
    cursor = conn.execute("SELECT Preis FROM Angebot WHERE fk_vkID = " + x)
##????????????????????????????????????????????????????????????????????????????????##

    # fetch all (alle Daten aus der Datenbank)
    fetch = cursor.fetchall()
    # Schleife um alle resultate anzuzeigen
    for data in fetch:
        a_tree.insert('', 'end', values=(data))
    cursor.close()
    conn.close()

# wsearch function

def w_searchRecord():
    # Test ob die Suchleiste leer ist
    if SEARCH.get() != "":
        # clear
        w_tree.delete(*w_tree.get_children())
        # Datenbank öffnen
        conn = sqlite3.connect('Preisliste Datablanka.db')
        # select query
        cursor = conn.execute("SELECT * FROM Ware WHERE Name LIKE ?", ('%' + str(SEARCH.get()) + '%',))
        # fetch all passenden resultate
        fetch = cursor.fetchall()
        # Schleife um alle resultate anzuzeigen
        for data in fetch:
            w_tree.insert('', 'end', values=(data))
        cursor.close()
        conn.close()


# w show data
def w_displayData():
    if SEARCH.get!="":
        # clear
        w_tree.delete(*w_tree.get_children())
        # Database öffnen
        conn = sqlite3.connect('Preisliste Datablanka.db')
        # select query
        cursor = conn.execute("SELECT * FROM Ware")
        # fetch all (alle Daten aus der Datenbank)
        fetch = cursor.fetchall()
        # Schleife um alle resultate anzuzeigen
        for data in fetch:
            w_tree.insert('', 'end', values=data)
        cursor.close()
        conn.close()


# vk search function
def vk_searchRecord():
    # Test ob die Suchleiste leer ist
    if SEARCH.get() != "":
        # clear
        vk_tree.delete(*vk_tree.get_children())
        # Datenbank öffnen
        conn = sqlite3.connect('Preisliste Datablanka.db')
        # select query
        cursor = conn.execute("SELECT * FROM Verkaufsort WHERE Name LIKE ?", ('%' + str(SEARCH.get()) + '%',))
        # fetch all passenden resultate
        fetch = cursor.fetchall()
        # Schleife um alle resultate anzuzeigen
        for data in fetch:
            vk_tree.insert('', 'end', values=(data))
        cursor.close()
        conn.close()


# vk show data
def vk_displayData():
    if SEARCH.get!="":
        # clear
        vk_tree.delete(*vk_tree.get_children())
        # Database öffnen
        conn = sqlite3.connect('Preisliste Datablanka.db')
        # select query
        cursor = conn.execute("SELECT * FROM Verkaufsort")
        # fetch all (alle Daten aus der Datenbank)
        fetch = cursor.fetchall()
        # Schleife um alle resultate anzuzeigen
        for data in fetch:
            vk_tree.insert('', 'end', values=data)
        cursor.close()
        conn.close()

# calling function
displayForm()
if __name__ == '__main__':
    # Running Application
    mainloop()
https://drive.google.com/drive/folders/ ... sp=sharing
Antworten