SQLite3 Update statement not updating Database-CodePudding

Mac OS latest update Python 3 latest update standard SQLite3 library

GUI development library PySimpleGUI

My problem is that my UPDATE Statement does not update the database and as a consequence my PySimpleGUI table element does not update in my GUI window.

I have around 2 months experience in coding using PySimpleGUI and have learned the value of research (as my code will suggest... I hope).

I have read the docs for (and extensively searched) both my GUI Library Docs and the SQLite Docs.

I am aware that this CRUD isn't fully ready (DELETE not working will fix once I understand what I am doing wrong with UPDATE) I really need to understand why this is not working so I can wrap my head around Query syntax so I can write my delete statement and write some custom queries across multiple tables.(This knowledge will come in time.)

Sorry for the volumous code here, best you see the whole CRUD Form.

import PySimpleGUI as sg
import sqlite3
from sqlite3 import Error

sg.Theme=('Purple')

def Customer_CRUD():
    
    def retrieve_customer():

        cust_details=[]
        conn=sqlite3.connect('Accounts.db')
        cursor=conn.execute('SELECT ID,Name,Bill,Site,Phone,Email FROM Customers')
        for row in cursor:
            cust_details.append(list(row))
        return cust_details

    table_data=retrieve_customer()

    headings=['ID','Name','Bill Address','Site Address','Phone','Email']

    cust_window_layout=[
        [sg.T('Customer CRUD For',font=('ariel',16))],
        [sg.T('Customer ID:',font=('ariel',14)),sg.InputText(font=('ariel',14),key='-ID-',disabled_readonly_background_color='Grey',disabled=True)],
        [sg.T('Customer Name:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(50,1),key='-Name-')],
        [sg.T('Billing Address:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(50,1),key='-Bill-')],
        [sg.T('Site Address:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(50,1),key='-Site-')],
        [sg.T('Phone:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(10,1),key='-Phone-'),sg.T('Email:',font=('ariel',14)),sg.InputText(font=('ariel',14),size=(25,1),key='-Email-')],
        [sg.Button('Submit',font=('ariel',16)),sg.Button('Update',font=('ariel',16)),sg.Button('Delete',font=('ariel',16))],
        [sg.Table(values=table_data,headings=headings,
                  font=('ariel',14),
                  max_col_width=50,
                  display_row_numbers=False,
                  justification='center',
                  vertical_scroll_only=False,
                  select_mode=sg.TABLE_SELECT_MODE_BROWSE,
                  num_rows=10,
                  enable_events=True,
                  key='-Table-')],
        [sg.Button('Main',font=('ariel',16)),sg.Button('Back',font=('riel',16))],
         ]
                  
    cust_window=sg.Window('Pool Resurfacing NQ Accounts',cust_window_layout)

    #define all sub processes here

    def clear_input():
        for key in values:
            cust_window[key]('')
            return None
        
    def add_customer(Name,Bill,Site,Phone,Email):

        conn=sqlite3.connect('Accounts.db')
        cursor=conn.execute('INSERT INTO Customers(Name,Bill,Site,Phone,Email) \
            VALUES(?,?,?,?,?)', (Name,Bill,Site,Phone,Email))
        conn.commit()
        conn.close()

        sg.Popup('Values successfully added',font=('ariel',16))

        cust_window['-Table-'].update(table_data)
        cust_window.refresh()

    def update_customer(Name,Bill,Site,Phone,Email,ID):

            conn=sqlite3.connect('Accounts.db')
            cursor=conn.execute('UPDATE Customers SET Name=?,Bill=?,Site=?,Phone=?,Email=? WHERE ID=?', (str(Name),str(Bill),str(Site),str(Phone),str(Email),str(ID)))
            conn.commit()
            conn.close()

            sg.Popup('Values successfully updated',font=('ariel',16))

            retrieve_customer()
            cust_window['-Table-'].update(table_data)
            cust_window.refresh()
            
    def delete_customer():

        conn=sqlite3.connect('Accounts.db')
        cursor=conn.execute('Delete FROM Customers \
            WHERE ID=cust_window["-ID-"]')
        conn.commit()
        conn.close()
        sg.Popup('Values successfully deleted',font=('ariel',16))
        cust_window['-Table-'].update(table_data)
        cust_window.refresh()

    while True:

        event,values=cust_window.read()

        if event==sg.WIN_CLOSED:
            break

        if event=='Submit':

            name=values['-Name-']
            if name=='':
                sg.popup('Missing Information:','Customer Name')

            bill=values['-Bill-']
            if bill=='':
                sg.popup('Missing Information:','Billing Address')

            site=values['-Site-']
            if site=='':
                sg.popup('Missing Information:','Site Address')

            phone=values['-Phone-']
            if phone=='':
                sg.popup('Missing Information:','Phone Number')

            email=values['-Email-']
            if email=='':
                sg.popup('Missing Information:','Email Address')

            else:
                add_customer(values['-Name-'],values['-Bill-'],values['-Site-'],values['-Phone-'],values['-Email-'])
                sg.popup('Your record has been saved successfully',font=('ariel',16))
                clear_input()

        if event=='-Table-':

            try:
                row=values['-Table-'][0]
                table_value=table_data[row]

                for i in table_value:
                    cust_window['-ID-'].update(table_value[0])
                    cust_window['-Name-'].update(table_value[1])
                    cust_window['-Bill-'].update(table_value[2])
                    cust_window['-Site-'].update(table_value[3])
                    cust_window['-Phone-'].update(table_value[4])
                    cust_window['-Email-'].update(table_value[5])
            except IndexError:
                continue

        if event=='Update':

            NAME=cust_window['-Name-']
            BILL=cust_window['-Bill-']
            SITE=cust_window['-Site-']
            PHONE=cust_window['-Phone-']
            EMAIL=cust_window['-Email-']
            IDENT=cust_window['-ID-']
            
            update_customer(NAME,BILL,SITE,PHONE,EMAIL,IDENT)

        if event=='Delete':
            delete_customer()


        if event=='Main':
            None#main_menu()

        if event=='Back':
            None#income()

    cust_window.close()
            
Customer_CRUD()

Thank you all in advance for your assistance!

CodePudding user response：

I might be very wrong but didn't you forget to commit the changes to the database.

In the case of your connection being called conn, conn.commit()

CodePudding user response：

If nothing wrong, IMO, your database updated, but not correctly update the Table element.

retrieve_customer() return cust_details from your database, but the result not kept
cust_window['-Table-'].update(table_data) update the Table element with old table_data which not updated.

Maybe it should be like this

    def update_customer(Name,Bill,Site,Phone,Email,ID):

            conn=sqlite3.connect('Accounts.db')
            cursor=conn.execute('UPDATE Customers SET Name=?,Bill=?,Site=?,Phone=?,Email=? WHERE ID=?', (str(Name),str(Bill),str(Site),str(Phone),str(Email),str(ID)))
            conn.commit()
            conn.close()

            sg.Popup('Values successfully updated',font=('ariel',16))

            table_data = retrieve_customer()    # Keep the returned list `cust_details`
            cust_window['-Table-'].update(table_data)
            cust_window.refresh()

Here, with a simple code to demo

import sqlite3
import PySimpleGUI as sg

def retrieve_customer():
    cust_details=[]
    cursor=conn.execute('SELECT ID,Name,Bill,Site,Phone,Email FROM Customers')
    for row in cursor:
        cust_details.append(list(row))
    return cust_details

def add_customer(data):
        cursor=conn.execute('INSERT INTO Customers (ID,Name,Bill,Site,Phone,Email) VALUES(?,?,?,?,?,?)', data)
        conn.commit()

conn = sqlite3.connect('Accounts.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS Customers (ID TEXT,Name TEXT, Bill TEXT,Site TEXT,Phone TEXT,Email TEXT)''')
conn.commit()

font1, font2 = ('Arial', 14), ('Arial', 16)
sg.theme('Purple')
sg.set_options(font=font1)

headings = ['ID','Name','Bill Address','Site Address','Phone','Email']
fields = {
    '-ID-'      : 'Customer ID:',
    '-Name-'    : 'Customer Name:',
    '-Bill-'    : 'Billing Address:',
    '-Site-'    : 'Site Address:',
    '-Phone-'   : 'Phone:',
    '-Email-'   : 'Email:'}

table_data = retrieve_customer()

layout = [
    [sg.Push(), sg.Text('Customer CRUD'), sg.Push()]]   [
    [sg.Text(text), sg.Push(), sg.Input(key=key)] for key, text in fields.items()]   [
    [sg.Push()]  
    [sg.Button(button) for button in ('Add', 'Delete')]  
    [sg.Push()],
    [sg.Table(values=table_data, headings=headings, max_col_width=50, num_rows=10,
          display_row_numbers=False, justification='center', enable_events=True,
          vertical_scroll_only=False, select_mode=sg.TABLE_SELECT_MODE_BROWSE,
          expand_x=True, key='-Table-')],
    [sg.Button('Main'), sg.Push(), sg.Button('Back')],
]

window = sg.Window('Pool Resurfacing NQ Accounts', layout)
sg.theme('DarkBlue4')

while True:

    event, values = window.read()

    if event == sg.WIN_CLOSED:
        break
    elif event == 'Add':
        data = tuple(map(lambda key:values[key], fields.keys()))
        if '' in data:
            index = data.index('')
            sg.popup('Missing Information:', list(fields.values())[index][:-1])
        else:
            add_customer(data)
            table_data = retrieve_customer()
            window['-Table-'].update(table_data)

conn.close()
window.close()