I don't know what is wrong (Python and SQL connection)

[shereen]

every time i get that error : Exception in Tkinter callback

Error:
Traceback (most recent call last):
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python312\Lib\tkinter\__init__.py", line 1948, in __call__
    return self.func(*args)
           ^^^^^^^^^^^^^^^^
  File "C:\Users\Lenovo\Desktop\P1.py", line 136, in iPrescription
    self.cursor.execute('''
sqlite3.OperationalError: table patients has no column named use_medication

this is the code :

from tkinter import *
from tkinter import ttk  # Import ttk for themed widgets
from tkcalendar import DateEntry
from datetime import datetime
from tkinter import StringVar
import sqlite3
import random
import time;
import tkinter.messagebox


class Hospital:
    def __init__(self, root):
        self.root = root
        self.root.title("Hospital Management System")
        self.root.geometry("1350x750")
        self.root.configure(background='powder blue')
        self.conn = sqlite3.connect('Myhospital.db')
        self.cursor = self.conn.cursor()


        cmbNameTablets = StringVar()
        Ref = StringVar()
        Dose = StringVar()
        NumberTables = StringVar()
        AppointmentDate = StringVar()
        IssuedDate = StringVar()
        HowtoUseMedication = StringVar()  # Added HowtoUseMedication variable
        PatientID = StringVar()
        PatientNHSNo = StringVar()
        PatientName = StringVar()
        DateOfBirth = StringVar()
        PatientAddress = StringVar()
        Prescription = StringVar()
        DoctorName = StringVar()

        # Create the patients table if it doesn't exist
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS patients (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                reference_no TEXT,
                issued_date TEXT,
                appointment_date TEXT,
                patient_id TEXT,
                patient_name TEXT,
                date_of_birth TEXT,
                patient_address TEXT,
                nhs_number TEXT,
                name_of_tablets TEXT,
                no_of_tablets TEXT,
                dose TEXT,
                use_medication TEXT,
                doctor_name TEXT
            )
        ''')

        self.cursor.execute('''
            CREATE INDEX IF NOT EXISTS idx_appointment_date
            ON patients (appointment_date)
        ''')
        
        self.conn.commit()


        def openMedicationWindow():
            # Create a new window for medication details
            medication_window = Toplevel()
            medication_window.title("Medication Details")
            medication_window.geometry("400x300")
            
            # Medication labels and entry widgets in the new window
            lblNameTablet = Label(medication_window, font=('arial', 12, 'bold'), text="Name of Tablets:", padx=2, pady=2)
            lblNameTablet.grid(row=0, column=0, sticky=W)
            cboNameTablet = ttk.Combobox(medication_window, textvariable=cmbNameTablets, state='readonly', font=('arial', 12, 'bold'), width=23)
            cboNameTablet['values'] = ('', 'Ibuprofen', 'Co-codamol', 'Paracetamol', 'Amlodipine','Acetaminophen','Adderall','Amitriptyline','Amlodipine','Amoxicillin','Ativan')
            cboNameTablet.current(0)
            cboNameTablet.grid(row=0, column=1, sticky=W)

            lblNoOfTablets = Label(medication_window, font=('arial', 12, 'bold'), text="No. of Tablets:", padx=2 ,pady=2)
            lblNoOfTablets.grid(row=1, column=0, sticky=W)
            txtNoOfTablets = Entry(medication_window, font=('arial', 12, 'bold'), textvariable=NumberTables, width=25)
            txtNoOfTablets.grid(row=1, column=1)
            
            lblDose = Label(medication_window, font=('arial', 12, 'bold'), text="Dose:", padx=2 , pady=4)
            lblDose.grid(row=2, column=0, sticky=W)
            txtDose = Entry(medication_window, font=('arial', 12, 'bold'), textvariable=Dose, width=25)
            txtDose.grid(row=2, column=1)
            

            btnSaveMedication = Button(medication_window, text='Save Medication', font=('arial', 12, 'bold'), width=24, bd=4, command=saveMedication)
            btnSaveMedication.grid(row=4, column=1)

        def saveMedication():
            name_tablets = cmbNameTablets.get()
            no_of_tablets = NumberTables.get()
            dose = Dose.get()

            self.cursor.execute('''
                INSERT INTO patients (name_of_tablets, no_of_tablets, dose)
                VALUES (?, ?, ?)
            ''', (name_tablets, no_of_tablets, dose))


            self.conn.commit()
            tkinter.messagebox.showinfo("Success", "Medication details saved successfully!")

        #======================================function mdeclaration=============================================

        def iExit():
            iExit=tkinter.messagebox.askyesno("Hospital Managment System","Confirm if you want to exit")
            if iExit>0:
                root.destroy()
                return


        def iPrescription():

            # Get data from entry widgets

            reference_no = Ref.get()
            issued_date = IssuedDate.get()
            appointment_date = AppointmentDate.get()
            patient_id = PatientID.get()
            patient_name = PatientName.get()
            date_of_birth = DateOfBirth.get()
            patient_address = PatientAddress.get()
            nhs_number = PatientNHSNo.get()
            name_of_tablets = cmbNameTablets.get()
            no_of_tablets = NumberTables.get()
            dose = Dose.get()
            use_medication = self.txtUseMedication.get("1.0", "end-1c")  # Get content from txtUseMedication Text widget
            doctor_name = DoctorName.get()


          # Insert data into the database
            self.cursor.execute('''
                INSERT INTO patients (
                    reference_no, issued_date, appointment_date, patient_id, patient_name, date_of_birth,
                    patient_address, nhs_number, name_of_tablets, no_of_tablets, dose, use_medication, doctor_name
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                reference_no, issued_date, appointment_date, patient_id, patient_name, date_of_birth,
                patient_address, nhs_number, name_of_tablets, no_of_tablets, dose, use_medication,
                doctor_name
            ))
            self.conn.commit()

            # Display prescription data in textPrescription
            prescription_data = (
                f"Reference No: {reference_no}\n"
                f"Issued Date: {issued_date}\n"
                f"Appointment Date: {appointment_date}\n"
                f"Patient ID: {patient_id}\n"
                f"Patient Name: {patient_name}\n"
                f"Date of Birth: {date_of_birth}\n"
                f"Patient Address: {patient_address}\n"
                f"NHS Number: {nhs_number}\n"
                f"Name of Tablets: {name_of_tablets}\n"
                f"No. of Tablets: {no_of_tablets}\n"
                f"Dose: {dose}\n"
                f"Use Medication: {use_medication}\n"
                f"Doctor Name: {doctor_name}\n\n"
            )
            self.textPrescription.insert(END, prescription_data)

            # Display use_medication in FrameDetail
            self.textFrameDetail.insert(END, f"Use Medication: {use_medication}\n")
  
            
        def iDelete():

            Ref.set("")
            HowtoUseMedication.set("")
            DoctorName.set("")
            AppointmentDate.set("")
            IssuedDate.set("")
            PatientID.set("")
            PatientNHSNo.set("")
            PatientName.set("")
            DateOfBirth.set("")
            PatientAddress.set("")
            Prescription.set("")
            self.textPrescription.delete("1.0",END)
            self.textFrameDetail.delete("1.0",END)

            return


        def iReset():

            Ref.set("")
            HowtoUseMedication.set("")
            DoctorName.set("")
            AppointmentDate.set("")
            IssuedDate.set("")
            PatientID.set("")
            PatientNHSNo.set("")
            PatientName.set("")
            DateOfBirth.set("")
            PatientAddress.set("")
            Prescription.set("")
            self.textPrescription.delete("1.0",END)
            self.textFrameDetail.delete("1.0",END)

            return


        def sort_records():
            # Fetch sorted records from the database
            sorted_records = fetch_sorted_records()

            # Clear the textFrameDetail before displaying sorted records
            self.textFrameDetail.delete("1.0", END)

            # Display the sorted records in the textFrameDetail
            for record in sorted_records:
                self.textFrameDetail.insert(END, f"{record[0]}\t{record[1]}\t{record[2]}\t{record[3]}\t"
                                                  f"{record[4]}\t{record[5]}\t{record[6]}\t{record[7]}\t"
                                                  f"{record[8]}\t{record[9]}\t{record[10]}\t{record[11]}\n")

        def fetch_sorted_records():
            # Fetch records from the database and sort them by appointment date
            self.cursor.execute('''
                SELECT reference_no, issued_date, appointment_date, patient_id, patient_name, date_of_birth,
                patient_address, nhs_number, name_of_tablets, no_of_tablets, dose, use_medication
                FROM patients
                ORDER BY appointment_date ASC''')
            records = self.cursor.fetchall()
            return records
        #===========================================frame=========================================================
        MainFrame = Frame(self.root)
        MainFrame.grid()

        TitleFrame = Frame(MainFrame, bd=20, width=1350, padx=20, relief=RIDGE)
        TitleFrame.pack(side=TOP)

        self.lblTitle = Label(TitleFrame, font=('arial', 40, 'bold'), text="Hospital Management System", padx=2)
        self.lblTitle.grid()

        FrameDetail = Frame(MainFrame, bd=20, width=1350, height=100, padx=20, relief=RIDGE)
        FrameDetail.pack(side=BOTTOM)

        ButtonFrame = Frame(MainFrame, bd=20, width=1350, height=50, padx=20, relief=RIDGE)
        ButtonFrame.pack(side=BOTTOM)

        DataFrame = Frame(MainFrame, bd=20, width=1350, height=400, padx=20, relief=RIDGE)
        DataFrame.pack(side=BOTTOM)

        DataFrameLEFT = LabelFrame(DataFrame, bd=10, width=800, height=300, padx=20, relief=RIDGE
                              , font=('arial', 12, 'bold'), text="Patient Information:",)
        DataFrameLEFT.pack(side=LEFT)

        DataFrameRIGHT = LabelFrame(DataFrame, bd=10, width=450, height=300, padx=20, relief=RIDGE
                                    , font=('arial', 12, 'bold'), text="Prescription:",)
        DataFrameRIGHT.pack(side=RIGHT)

        #=============================================DataFrameLEFT==============================================


        self.lblRef = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="Reference No:", padx=2 , pady=2)
        self.lblRef.grid(row=0, column=0)
        self.txtRef = Entry(DataFrameLEFT, font=('arial', 12, 'bold'),textvariable=Ref, width=25)
        self.txtRef.grid(row=0, column=1)


        self.lblIssuedDate = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="Issued Date:", padx=2, pady=2)
        self.lblIssuedDate.grid(row=0, column=2)
        self.txtIssuedDate = DateEntry(DataFrameLEFT, font=('arial', 12, 'bold'), textvariable=IssuedDate, width=23)
        self.txtIssuedDate.grid(row=0, column=3)


        self.lblAppointmentDate = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="AppointmentDate", padx=2, pady=2)
        self.lblAppointmentDate.grid(row=1, column=0)
        self.txtAppointmentDate = DateEntry(DataFrameLEFT, font=('arial', 12, 'bold'), textvariable=AppointmentDate, width=23)
        self.txtAppointmentDate.grid(row=1, column=1)


        self.lblPatientID = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="Patient ID:", padx=2, pady=2)
        self.lblPatientID.grid(row=1, column=2)
        self.txtPatientID = Entry(DataFrameLEFT, font=('arial', 12, 'bold'),textvariable=PatientID , width=25)
        self.txtPatientID.grid(row=1, column=3)

        
        self.lblPatientName = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="Patient Name:", padx=2, pady=2)
        self.lblPatientName.grid(row=2, column=0)
        self.txtPatientName = Entry(DataFrameLEFT, font=('arial', 12, 'bold'),textvariable=PatientName , width=25)
        self.txtPatientName.grid(row=2, column=1)


        self.lblDateOfBirth = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="Date Of Birth:", padx=2, pady=2)
        self.lblDateOfBirth.grid(row=2, column=2)
        self.txtDateOfBirth = DateEntry(DataFrameLEFT, font=('arial', 12, 'bold'), textvariable=DateOfBirth, width=23)
        self.txtDateOfBirth.grid(row=2, column=3)


        self.lblPatientAddress = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="Patient Address:", padx=2, pady=2)
        self.lblPatientAddress.grid(row=3, column=0)
        self.txtPatientAddress= Entry(DataFrameLEFT, font=('arial', 12, 'bold'),textvariable=PatientAddress , width=25)
        self.txtPatientAddress.grid(row=3, column=1)

        
        self.lblNHSNumber = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="NHS Number:", padx=2, pady=2)
        self.lblNHSNumber.grid(row=3, column=2)
        self.txtNHSNumber = Entry(DataFrameLEFT, font=('arial', 12, 'bold'),textvariable=PatientNHSNo , width=25)
        self.txtNHSNumber.grid(row=3, column=3)
        

        self.lblUseMedication = Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="Use Medication:", padx=2, pady=2)
        self.lblUseMedication.grid(row=4, column=0)
        self.txtUseMedication = Text(DataFrameLEFT, font=('arial', 12), height=2, width=25)
        self.txtUseMedication.grid(row=4, column=1)



        self.lblDoctorName= Label(DataFrameLEFT, font=('arial', 12, 'bold'), text="  Doctor Name:", padx=2 ,pady=2)
        self.lblDoctorName.grid(row=4, column=2)
        self.txtDoctorName = Entry(DataFrameLEFT, font=('arial', 12, 'bold'),textvariable=DoctorName , width=25)
        self.txtDoctorName.grid(row=4, column=3)



        
        #====================================DataFrameRIGHT=====================================================================
        
        self.textPrescription=Text(DataFrameRIGHT, font=('arial', 12, 'bold'),width=43, height=14, padx=2, pady=2)
        self.textPrescription.grid(row=0, column=0)



        #=====================================ButtonFrame==================================================================

        self.btnPrescription=Button(ButtonFrame,text='Prescription', font=('arial', 12, 'bold'),width=24 ,bd=4
                                    ,command=iPrescription)
        self.btnPrescription.grid(row=0, column=0)

        self.btnMedicationDetails=Button(ButtonFrame,text='Medication Details', font=('arial', 12, 'bold'),width=24 ,bd=4,
                                    command=openMedicationWindow)
        self.btnMedicationDetails.grid(row=0, column=1)


        self.btnDelete=Button(ButtonFrame,text='Delete', font=('arial', 12, 'bold'),width=24 ,bd=4
                              ,command=iDelete)
        self.btnDelete.grid(row=0, column=2)

        self.btnReset=Button(ButtonFrame,text='Reset', font=('arial', 12, 'bold'),width=24 ,bd=4
                             ,command=iReset)
        self.btnReset.grid(row=0, column=3)
        
        self.btnExit=Button(ButtonFrame,text='Exit', font=('arial', 12, 'bold'),width=24 ,bd=4
                            ,command=iExit)
        self.btnExit.grid(row=0, column=4)


        
        #=====================================FrameDetail======================================================================

        self.lblLabel = Label(FrameDetail, font=('arial', 10, 'bold'), pady=8,
                    text="Type of medical condition/Does the patient use medications?")
        self.lblLabel.grid(row=0, column=0)

        
        self.textFrameDetail=Text(FrameDetail,font=('arial', 12, 'bold'),width=141, height=4, padx=2, pady=4)
        self.textFrameDetail.grid(row=1, column=0)




        




if __name__ == '__main__':
    root = Tk()
    application = Hospital(root)
    root.mainloop()

[deanhystad]

Cut out the database query code into a new file and focus on that. The error is very clear. Your table does not have a column named "use_medication". You need to find out why you think it does. Once you get the database all cleared up you can move the correct code back into your GUI.

I suggest a similar approach for the GUI. Work on a small section and get that to work with the database, then add features.

At a glance there are some problems, like this:

        cmbNameTablets = StringVar()
        Ref = StringVar()
        Dose = StringVar()
        NumberTables = StringVar()
        AppointmentDate = StringVar()
        IssuedDate = StringVar()
        HowtoUseMedication = StringVar()  # Added HowtoUseMedication variable
        PatientID = StringVar()
        PatientNHSNo = StringVar()
        PatientName = StringVar()
        DateOfBirth = StringVar()
        PatientAddress = StringVar()
        Prescription = StringVar()
        DoctorName = StringVar()

All these variables disappear as soon as the Hospital.__init__() method completes. If Hospital needs these variables, they should be instance variables (self.doctor_name = StringVar()).

I know you know about instance variables because you use them elsewhere in your code, like here:

        self.lblLabel = Label(FrameDetail, font=('arial', 10, 'bold'), pady=8,
                    text="Type of medical condition/Does the patient use medications?")
        self.lblLabel.grid(row=0, column=0)

But I have to wonder why you keep self.lblLabel in an instance variable. These two lines are the only place it is used. It is never referenced again. Create instance variables for things you need to reference later. Use local variables for things you only refer in the init. Or use no variable at all. Why not do this:

tk.Label(
    FrameDetail, font=('arial', 10, 'bold'), pady=8, text="Type of medical condition/Does the patient use medications?"
).grid(row=0, column=0)

Your GUI should have maybe 3 fonts max. I prefer one. Your code makes 38 fonts. Most of these fonts are the same, but it is very easy to make a typo when you repeat something 38 times. Instead of defining the same font over an over, make an object that you can use over and over.

small_font = ('arial', 10, 'bold')  # define once
 self.lblLabel =tk.Label(
    FrameDetail, font=small_font, pady=8, text="Type of medical condition/Does the patient use medications?"
).grid(row=0, column=0)

This drives me nuts. I see it all the time and it makes no sense.

class Hospital:
    def __init__(self, root):
        self.root = root
        self.root.title("Hospital Management System")

if __name__ == '__main__':
    root = Tk()
    application = Hospital(root)
    root.mainloop()

Why is Hospital passed a root? Shouldn't Hospital be a root?

class Hospital(tk.Tk):
    """The main window for my hospital management system."""
    def __init__(self):
        super().__init__()
        self.title("Hospital Management System")

if __name__ == '__main__':
     Hospital().mainloop()

[menator01]

Hope you don't mind but, I spruced up your app a little.
I didn't do any of the database functions as I don't really know what you are wanting them to do.
On a side note you have a delete and reset button. If you delete a record from the database, it can't be reset.

Here is the code

import tkinter as tk
import tkcalendar 

class Database:
    def __init__(self):
        pass 


class Window:
    def __init__(self, parent):
        # Set some parent window variables
        self.parent = parent
        self.parent.columnconfigure(0, weight=1)
        self.parent.rowconfigure(0, weight=1)
        # self.parent.geometry('1280x720+300+300')
        self.parent.title('Hospital Management System Version 0.01')

        # Create a main container frame widget. This will hold all other containers and widgets
        container = tk.Frame(self.parent)
        container.grid(column=0, row=0, sticky='news')
        container.grid_columnconfigure(0, weight=3)

        # Create a header for the app
        header = tk.Label(container, text='Hospital Management System', pady=8)
        header['font'] = None, 34, 'bold'
        header['bg'] = 'firebrick'
        header['fg'] = 'white'
        header.grid(column=0, row=0, sticky='news', padx=8, pady=4)

        # Create a container for widgets in row 1
        row1 = tk.Frame(container)
        row1.grid(column=0, row=1, sticky='news', padx=4, pady=4)

        # Divied the column space evenly
        row1.grid_columnconfigure(0, weight=3, uniform='row1')
        row1.grid_columnconfigure(1, weight=3, uniform='row1')

        # Container to hold all the entry widgets for patient
        infoframe = tk.LabelFrame(row1, text='Patient Information', font=(None, 11, 'bold'), padx=4, pady=4)
        infoframe.grid(column=0, row=0, sticky='news', padx=4, pady=4)
        for i in range(4):
            infoframe.grid_columnconfigure(i, weight=3)
        
        # Container to hold prescription information/listbox
        prescriptionframe = tk.LabelFrame(row1, text='Prescription', font=(None, 11, 'bold'))
        prescriptionframe.grid(column=1, row=0, sticky='news', padx=4, pady=4)
        prescriptionframe.grid_columnconfigure(0, weight=3)

        # List of patient fields
        data = ['Reference No', 'Issue Date', 'Appointment Date', 'Patient ID',
        'Patient Name', 'Date Of Birth', 'Patient Address', 'NHS Number', 'Use Medication', 'Doctor Name']
        thelist = ['Issue Date','Appointment Date', 'Date Of Birth']
        # Empty list to hold entry fields
        self.fields = []
        col, row = 0, 0

        for index, prefix in enumerate(data):
            label = tk.Label(infoframe, text=f'{prefix}:', anchor='w')

            # So entry prefix is in every other column
            if col % 2 == 0:
                col += 1
            label.grid(column=col, row=row, sticky='new', padx=2, pady=4)
            if prefix in thelist:
                self.fields.append(tkcalendar.DateEntry(infoframe))
            else:
                self.fields.append(tk.Entry(infoframe))
            self.fields[index].grid(column=col+1, row=row, sticky='new', padx=8, pady=4)

            if col >= 3:
                row += 1
                col = 0
            else:
                col += 1

        self.prescription = tk.Text(prescriptionframe, height=9)
        self.prescription.grid(column=0, row=0, sticky='news', padx=4, pady=4)

        # List for the buttons
        buttons = ['Prescription', 'Medication Details', 'Delete', 'Reset', 'Exit']
        buttonlist = []

        buttonframe = tk.LabelFrame(container, text='Actions', font=(None, 11, 'bold'))
        buttonframe.grid(column=0, row=2, sticky='news', padx=4, pady=4)
        for i in range(len(buttons)):
            buttonframe.grid_columnconfigure(i, weight=3, uniform='button')

        for index, button in enumerate(buttons):
            if button == 'Exit':
                color = 'orangered'
                activecolor = 'orange'
            elif button == 'Delete':
                color = 'tomato'
                activecolor = 'red'
            else:
                color = 'powderblue'
                activecolor = 'skyblue'
            buttonlist.append(tk.Button(buttonframe, text=button, bg=color, cursor='hand2'))
            buttonlist[index]['activebackground'] = activecolor
            buttonlist[index].grid(column=index, row=0, sticky='news', padx=4, pady=4)


        extra = tk.LabelFrame(container)
        extra['text'] = 'Type of medical condition / Does the patient use medications?'
        extra.grid(column=0, row=3, sticky='news', padx=4, pady=4)
        extra.grid_columnconfigure(0, weight=3)

        self.extra = tk.Text(extra)
        self.extra.grid(column=0, row=0, sticky='news' ,padx=4, pady=4)




class Controller:
    def __init__(self, database, window):
        self.database = database 
        self.window = window 


if __name__ == '__main__':
    root = tk.Tk()
    controller = Controller(Database(), Window(root))
    root.mainloop()

[Pedroski55]

Maybe the CREATE command has a problem?

Instead of AUTOINCREMENT try AUTO_INCREMENT? Otherwise AUTOINCREMENT may be interpreted as a column name.

CREATE TABLE IF NOT EXISTS patients (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
reference_no TEXT,
issued_date TEXT,
appointment_date TEXT,
patient_id TEXT,
patient_name TEXT,
date_of_birth TEXT,
patient_address TEXT,
nhs_number TEXT,
name_of_tablets TEXT,
no_of_tablets TEXT,
dose TEXT,
use_medication TEXT,
doctor_name TEXT
)

This works in my MySQL database:

INSERT INTO patients (
reference_no, issued_date, appointment_date, patient_id, patient_name, date_of_birth,
patient_address, nhs_number, name_of_tablets, no_of_tablets, dose, use_medication, doctor_name
) VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M')

SELECT id, use_medication, doctor_name FROM patients WHERE 1