Getting an error when inserting a record into db from entrybox on tkinter using python

Question

from tkinter import *
import psycopg2
# Db connection
try:
    connection = psycopg2.connect(user="postgres",
                                  password="admin",
                                  host="192.168.1.9",
                                  port="5432",
                                  database="BigBearSystemsDB")
    cursor = connection.cursor()
    # Main window
    window = Tk()
    window.title("Big Bear Systems")
    window.configure(background="light blue")
    window.geometry("1500x950")

    # Customer id
    Label(window, text="Customer ID", bg="light blue", fg="black", font=("Ariel", 10)).grid(row=0, column=1, pady=5, padx=5, sticky=W)
    # Customer name
    Label(window, text="Customer name", bg="light blue", fg="black", font=("Ariel", 10)).grid(row=1, column=1, pady=5, padx=5, sticky=W)
    # Active checkbox
    Checkbutton(window, text="Active customer", bg="light blue", fg="black", font=("Ariel", 10)).grid(row=2, column=1, pady=5, padx=5, sticky=W)
    # Customer id entry box
    customerid = Entry(window, width=25, bg="white")
    customerid.grid(row=0, column=2, sticky=W)
    # Customer name entry box
    customername = Entry(window, width=25, bg="white")
    customername.grid(row=1, column=2, sticky=W)
    # Save the customer to db function
    def save_it():
        Customerid = customerid.get()
        Customername = customername.get()
        cursor.execute('INSERT INTO customer(Customer ID, Customer name)VALUES (%s, %s)', (Customerid, Customername))
    # Customer save button
    Button(window, text="Save", width=14, command=save_it, font=("Ariel", 10)).grid(row=3, column=11, pady=250,padx=1,
                                                                                                    sticky=E)
    # Cancel function
    def cancel():
        window.destroy()
        exit()
    # Cancel button
    Button(window, text="Cancel", width=14, command=cancel,font=("Ariel", 10)).grid(row=3, column=10, pady=250, padx=1, sticky=E)
    connection.commit()
    count = cursor.rowcount
    print(count, "Record inserted successfully into table")
except (Exception, psycopg2.Error) as error:
    if (connection):
        print("Record failed to insert", error)
finally:
    if (connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")
    window.mainloop()

When I execute the code below to save the customer id and customer name into the database, it skips the save function and goes to the exception then exits out.

Here is the error message I am getting.

-1 Record inserted successfully into table PostgreSQL connection is closed Exception in Tkinter callback Traceback (most recent call last): File "C:\Users\13104\AppData\Local\Programs\Python\Python38-32\lib\tkinter__init__.py", line 1883, in call return self.func(*args) File "C:/Users/13104/PycharmProjects/BigBearSystems/BigBearSystemsUI.py", line 45, in save_it cursor.execute('INSERT INTO customer(Customer ID, Customer name)VALUES (%s, %s)', (Customerid, Customername)) psycopg2.InterfaceError: cursor already closed

Process finished with exit code 0

Answer 1

You have wrapped all of your code inside the try-block. Everything inside the try-block is going to run once, only to skip to the finally-block once it's finished provided that nothing threw an exception along the way. I suggest only wrapping the parts pertaining to your save function within the try-block, which is what I think was your original intent.

Perhaps this will guide you closer to what you're looking for:

from tkinter import *
import psycopg2
# Db connection

connection = psycopg2.connect(user="postgres",
                              password="admin",
                              host="192.168.1.9",
                              port="5432",
                              database="BigBearSystemsDB")
cursor = connection.cursor()
    # Main window
window = Tk()
window.title("Big Bear Systems")
window.configure(background="light blue")
window.geometry("1500x950")

    # Customer id
Label(window, text="Customer ID", bg="light blue", fg="black", font=("Ariel", 10)).grid(row=0, column=1, pady=5, padx=5, sticky=W)
    # Customer name
Label(window, text="Customer name", bg="light blue", fg="black", font=("Ariel", 10)).grid(row=1, column=1, pady=5, padx=5, sticky=W)
    # Active checkbox
Checkbutton(window, text="Active customer", bg="light blue", fg="black", font=("Ariel", 10)).grid(row=2, column=1, pady=5, padx=5, sticky=W)
    # Customer id entry box
customerid = Entry(window, width=25, bg="white")
customerid.grid(row=0, column=2, sticky=W)
    # Customer name entry box
customername = Entry(window, width=25, bg="white")
customername.grid(row=1, column=2, sticky=W)
    # Save the customer to db function
def save_it():
    Customerid = customerid.get()
    Customername = customername.get()
    try:
        cursor.execute('INSERT INTO customer(Customer ID, Customer name)VALUES (%s, %s)', (Customerid, Customername))
        connection.commit()
        print(count, "Record inserted successfully into table")

    except (Exception, psycopg2.Error) as error:
        if (connection):
        print("Record failed to insert", error)

    finally:
        if (connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

    # Customer save button
Button(window, text="Save", width=14, command=save_it, font=("Ariel", 10)).grid(row=3, column=11, pady=250,padx=1,
                                                                                                    sticky=E)
    # Cancel function
def cancel():
    window.destroy()
    exit()
    # Cancel button
Button(window, text="Cancel", width=14, command=cancel,font=("Ariel", 10)).grid(row=3, column=10, pady=250, padx=1, sticky=E)

count = cursor.rowcount



window.mainloop()