[FIXED] Why the Json module cannot serialize date object in python?

Issue

I have a error coming as below

{
        "statusCode": 500,
        "body": "\"An error occurred: Object of type date is not JSON serializable\""
    },

and below is the code implementation to handle the error.

def format_item(item):
    if item is None:
        return 0.0 if isinstance(item, (Decimal, float)) else ""
    elif isinstance(item, (Decimal, float)):
        return float(item)
    elif isinstance(item, (datetime, date)):
        if isinstance(item, date):
            return item.isoformat()  # Handle yyyy-mm-dd format
        elif isinstance(item, datetime):
            if item.hour == 0 and item.minute == 0 and item.second == 0:
                return item.strftime('%Y-%m-%d')  # Handle yyyy-mm-dd format
            else:
                return item.strftime('%Y-%m-%d %H:%M:%S')  # Handle yyyy-mm-dd hh:mm:ss format
    else:
        return "" if item is None else item


def stored_procedure_call(sp_name, id, entity):
    logging.info(f"Fetching DB connection details.")
    try:
        # Load env file
        load_dotenv()
        # Create the connection object
        conn = mysql.connector.connect(
            user=os.getenv('USER_NAME'),
            password=get_db_password(os.getenv('RDS_HOST')),
            host=os.getenv('RDS_HOST'),
            database=os.getenv('DB_NAME'),
            port=os.getenv('PORT'))

        # Create a cursor
        cursor = conn.cursor()
    except Exception as error:
        logging.error("An unexpected error occurred: {}".format(error))

    try:

        # Call the stored procedure with the provided ID
        cursor.callproc(stored_procedure_name, [id, entity])
        conn.commit()

        result_list = []
        for result in cursor.stored_results():
            rows = result.fetchall()
            for row in rows:
                result_list.append(list(row))
                logging.info(row)
        print("[RESULT LIST] :", result_list)

        if not result_list:
            return {
                'statusCode': 200,
                'body': json.dumps([])
            }

        else:
            result_list_serializable = [list(format_item(item) for item in tup) for tup in result_list]
            print('[RESULT SERIALIZER] :', result_list_serializable)

        return {
            'statusCode': 200,
            'headers': {
                'Content-Type': 'application/json'
            },
            'body': json.dumps(result_list)
        }

    except Exception as error:
        logging.error("An unexpected error occurred: {}".format(error))
        return {
            'statusCode': 500,  # Internal Server Error
            'body': json.dumps('An error occurred: {}'.format(str(error)))
        }

    finally:
        close_connection(cursor, conn)

What could be the possible issue.

The error seems to be quite common, and I have tried to implement the solution from other blogs or article. But it doesn't seem to be working. Please suggest

sample data:

[['4565b', 'Agi Ltd', 'Insight Dire', '2100', datetime.date(2023, 9, 21), datetime.date(2023, 10, 21), 'ABC', 'Dell E5 ', '4565b64f53e', datetime.datetime(2023, 11, 30, 16, 17, 14)]]

Solution

According to the json documentation, in json the type of a value can only be: (str), integer (int), float (float), object (dict), array (list/tuple), true, false or null.

If you want to put dates in a json object, you'll need to convert it to one of these types. This can be done in several ways:

item.timestamp() # Convert a datetime to an UNIX timestamp (works for datetime objects but not for date objects)
item.isoformat() # Convert a datetime to an ISO 8601 timestamp
item.strftime('%Y-%m-%d %H:%M:%S')  # Handle yyyy-mm-dd hh:mm:ss format

Note that you can also convert all non-serializable objects to str (like str(item)) by setting default=str.

json.dumps(result_list, default=str)

Answered By - crazycat256