Why is my log file empty in my python script?

I am trying to output the errors, if there are any in the code, and also the results of the stored procedure in my log file. The log file is created but it is empty. I have other logging in my code that logs errors but nothing is showing up. Here is my code:

import os 
import json
import platform
import pymysql as pm
from datetime import date
import logging
from logging.handlers import TimedRotatingFileHandler


class LogOutput:
    env=None
    config=None

    def __init__(self, env_filename):
        self.env=env_filename
        self.config=self.get_config()

    def get_config(self):
        with open(self.env) as file_in:
            return json.load(file_in)
        
    def is_Windows():
        if "win" in (platform.system().lower()):
            return True
        else:
            return False       

    def setup_logger():
        logger = logging.getLogger(__name__)
        logger.setLevel(logging.DEBUG)
        
        # Rotating log setup
        log_filename = f"E:\\file\\path\\here\\log-{date.today()}.log"
        file_handler = TimedRotatingFileHandler(log_filename, when="midnight", backupCount=7)
        file_handler.setFormatter(logging.Formatter('%(asctime)s - %(levelname)s - %(message)s'))
        
        console_handler = logging.StreamHandler()
        console_handler.setFormatter(logging.Formatter('%(asctime)s - %(levelname)s - %(message)s'))
        
        logger.addHandler(file_handler)
        logger.addHandler(console_handler)
        return logger 
    setup_logger()

    def DB_connection(self):
        logger = logging.getLogger()
        logger.setLevel(logging.INFO)
        connection = None
        try:
            config=LogOutput.get_config(self)
            host=config["log-output"]["database-secrets"]["host"]
            port=config["log-output"]["database-secrets"]["port"]
            database=config["log-output"]["database-secrets"]["db"]
            username=config["log-output"]["database-secrets"]["username"]
            password=config["log-output"]["database-secrets"]["password"]

            # Load the configuration from DB_conn_info_local.json
            config_file_path = os.path.join(os.path.dirname(__file__), 'DB_conn_info_local.json')
            with open(config_file_path) as config_file:
                config = json.load(config_file)

            connection = pm.connect(user=username,password=password,host=host,port=port,database=database)
    
            logger.info("Successfully connected to database")

        except Exception as e:
            logger.error("Unable to connect to database: %s", str(e))
        
        return connection
    
    def logging_sp(self):
        logging_sp_query = """
        select *`  
        from table
        """
        cnxn = self.DB_connection()
        if cnxn is None:
            logging.error("Database connection is None. Cannot proceed.")
            return  # Exit here if there's no valid connection
            
        with cnxn.cursor() as cur:
            try:
                cur.execute(logging_sp_query)
            except Exception as e:
                logging.exception(e)
            else:
                cnxn.commit()
        cnxn.close()  # Close the connection 


def main():
    cwd=os.getcwd()
    if "win" in (platform.system().lower()):
        vfc=(cwd+"\\DB_conn_info_local"+".json")
    else:
        vfc=(cwd+"/DB_conn_info_local"+".json")
    ve=LogOutput(vfc)
    ve.logging_sp()
   
if __name__ == "__main__":
    main()

So setup_logger is the function that is writing the log file.

UPDATE:

The error is this which I am expecting because the user account doesn't have permissions to execute the code, but my issue is I want to write this error to my log file:

$ "C:/Program Files/Python312/python.exe" c:/Users/file/log_output_sp.py
2024-09-12 11:42:06,235 - INFO - Successfully connected to database
Traceback (most recent call last):
  File "c:\Users\file\log_output_sp.py", line 242, in <module>
    main()
  File "c:\Users\file\log_output_sp.py", line 239, in main
    ve.logging_sp()
  File "c:\Users\file\log_output_sp.py", line 228, in logging_sp
    cnxn.commit()
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\connections.py", line 482, in commit
    self._execute_command(COMMAND.COM_QUERY, "COMMIT")
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\connections.py", line 852, in _execute_command
    self.next_result()
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\connections.py", line 567, in next_result
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\connections.py", line 825, in _read_query_result
    result.read()
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\user\AppData\Roaming\Python\Python312\site-packages\pymysql\err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1142, "UPDATE command denied to user 'user'@'ip.address.here' for table `database`.`table`")

I updated my code to use:

    except pm.err.OperationalError as e:
        logging.exception(e)

but it didn't write it to the log so I am kind of stuck here.