Trying to save to a sqlite3 database with df.to_sql(), database file stays empty

Question

I am working on a Smile Detection Application which uses a CNN model and CV2 to capture the image of people smiling using laptop's camera. Currently the application is under development and we are saving the details in a csv file - timestamp, happiness_index and location of smile captured. Now we need to use sqlite3 database to save the same details to be later used for PowerBi reporting.

Below is a snapshot of my code. I have omitted a few other lines of code (non-related ones), however I can see that smile_details.db is created but its empty, I am not getting any other options to save the details to the database. I tried MySQL but no success. Kindly help me with any helpful solutions or other options to acheive the same.

P.S. the details are getting updated in the happiness_details.csv though.

I want to see the details saved in sqlite3 database.

# Load the pre-trained model for smile detection
model = load_model(r'...\..\model.h5')

print("Model loaded!")

def detect_smile(frame, df,location):
    global happiness_index
    gray = cv2.cvtColor(frame, cv2.COLOR_BGR2GRAY)
    faces = face_cascade.detectMultiScale(gray, 1.3, 5)
    # Define the happiness index
    happiness_index = 0
    for (x, y, w, h) in faces:
        cv2.rectangle(frame, pt1=(x, y), pt2=(x + w, y + h), color=(255, 0, 0), thickness=2)
        roi = gray[y:y + h, x:x + w]
        roi = cv2.resize(roi, (32, 32))
        roi = roi / 255.0
        roi = roi[np.newaxis, ...]
        smile_prob = model.predict(roi)[0]
        if smile_prob > 0.5:
            # Calculate the happiness index based on the smile probability
            happiness_index = smile_prob * 100
            # Generate a unique face ID based on the face coordinates with timestamp
            face_id = f"{x}-{y}-{w}-{h}-{int(time.time())}"
            # Append the face ID and happiness index to the DataFrame
            new_row = {'Timestamp': datetime.now().strftime("%d/%m/%Y %H:%M:%S"), 'Face ID': face_id, 'Happiness Index': happiness_index, 'Location': location}
            df = pd.concat([df, pd.DataFrame([new_row])], ignore_index=True)
            # Save the data to CSV file without truncating the previous data
            df.to_csv(r'..\..\happiness_details.csv', mode='a', index=False, header=False)

            label = "Thanks for Smiling!"
            cv2.putText(frame, label, (x, y - 10), cv2.FONT_HERSHEY_SIMPLEX, 0.75, (0, 255, 0), 2)

        else:
            label = "No Smile Detected!"
            cv2.putText(frame, label, (x, y - 10), cv2.FONT_HERSHEY_SIMPLEX, 0.75, (0, 0, 255), 2)

        # Display the frame with the smile detection and happiness index
        cv2.putText(frame, f"Happiness Index: {float(happiness_index):.2f}", (10, 30), cv2.FONT_HERSHEY_SIMPLEX, 0.7,
                    (0, 255, 0), 2, cv2.LINE_AA)
        cv2.imshow("Smile Detector", frame)

    return frame, df
print(" Detect Smile completed!")

print("Db Creation Node")
def save_details_to_database(df):
    # Create a connection to the SQLite database
    conn = sqlite3.connect('smile_details.db')

    # Save the DataFrame to the database
    df.to_sql('happiness_details', conn, if_exists='append', index=False)

    # Close the connection
    conn.close()

def main():
    st.set_page_config(page_title="Smile Detector", page_icon=":smiley:")

    # Create a title for the app
    st.title("Happiness Index Detection")
    activities = ["Selfie Portal", "About Us"]
    choice = st.sidebar.selectbox("Select Activity", activities)
    
    # Add an input field for location
    location = st.text_input("Enter your location:")

    if choice == "Selfie Portal":
        st.header("Smile Please")
        st.write("Click on Start to use the camera and detect your happiness index")

        if st.button("Start"):
            global last_captured_frame, run_detection, happiness_index
            # Create a DataFrame to store the happiness index
            df = pd.DataFrame(columns=['Timestamp', 'Face ID', 'Happiness Index', 'Location'])

            # Open the default camera and start the smile detection
            cap = cv2.VideoCapture(0)
            run_detection = True

            while run_detection:
                ret, frame = cap.read()

                # Call the detect_smile function to detect smile and calculate happiness index
                frame, df = detect_smile(frame, df, location)

                # Store the last captured frame
                last_captured_frame = frame.copy()

                # Display the webcam feed and happiness index
                cv2.imshow('Smile Detector', frame)

                # Check if the user wants to stop the detection
                if cv2.waitKey(1) & 0xFF == ord('q'):
                    run_detection = False

            # Release the camera and close the window
            cap.release()
            cv2.destroyAllWindows()
            print("Initiating the db")
            #saving to sqlite3 database
            save_details_to_database(df)
            df.to_csv("happiness_details.csv",index=False)
            # Display the thank you message
            st.success("Thanks for smiling, have a good day!")