TypeError datetime.date is not JSON serializable (using SQLAlchemy)


Hello everyone!

I am developing a service which returns a guest. I am using SQLAlchemy to define a model class and to query the database. The service is using SimpleIO. Here you are an excerpt of the model class:

class Guest(Base):
    __tablename__ = 'guest'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    surname = Column(String(50))
    email = Column(String(255), index=True, unique=True)
    birthdate = Column(Date)
    deleted = Column(DateTime, default=None, index=True)

And this is an excerpt of the service:

class Get(Service):
    """Service class to get a guest by id through channel /genesisng/guests/get/{id}."""

    class SimpleIO(object):
        input_required = ('id')
        output_optional = ('id', 'name', 'surname', 'gender', 'email', 'birthdate', 'deleted')

    def handle(self):
        conn = self.kvdb.conn.get('genesisng:database:connection')
        id_ = self.request.input.id

        with closing(self.outgoing.sql.get(conn).session()) as session:
            result = session.query(Guest).filter(and_(Guest.id == id_, Guest.deleted == None)).one_or_none()

            if result:
                self.response.status_code = OK
                self.response.payload = result
                self.response.status_code = NOT_FOUND
                self.response.payload = ''

When calling the service:

curl -v -g ""; echo ""

I am getting this error:

TypeError: datetime.date(1976, 1, 1) is not JSON serializable

If the field is NULL on the database, then no error is returned. It happens both with date (birthdate field) and datetime (deleted field):

TypeError: datetime.datetime(2018, 9, 9, 9, 16, 38, 655582) is not JSON serializable

I’ve been searching for information on the Zato docs, the Zato forums, Stack Overflow and elsewhere regarding serialization of dates but I could not find anything useful.

Not sure if it may be related to this post, but I don’t think I am the first person attempting to return a Date or a DateTime from SQLAlchemy through a service, hence I’m confused.

This is the full trace:

2018-09-09 09:11:17,411 DEBG 'zato-server2' stdout output:
2018-09-09 09:11:17,410 - WARNING - 109:DummyThread-40 - zato.server.service:514 - Exception in service `guest.get`, e:`Traceback (most recent call last):
  File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 503, in update_handle
    response = set_response_func(service, data_format=data_format, transport=transport, **kwargs)
  File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 366, in _set_response_data
    self.set_payload(service.response, data_format, transport, service)
  File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 557, in set_payload
    response.payload = response.payload.getvalue() if response.payload else ''
  File "/opt/zato/3.0/code/zato-server/src/zato/server/service/reqresp/__init__.py", line 447, in getvalue
    return dumps(top)
  File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/anyjson/__init__.py", line 141, in dumps
    return implementation.dumps(value, *args, **kwargs)
  File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/anyjson/__init__.py", line 87, in dumps
    return self._encode(data, *args, **kwargs)
  File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/simplejson/__init__.py", line 354, in dumps
    return _default_encoder.encode(obj)
  File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/simplejson/encoder.py", line 262, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/simplejson/encoder.py", line 340, in iterencode
    return _iterencode(o, 0)
  File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/simplejson/encoder.py", line 239, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: datetime.date(1976, 2, 18) is not JSON serializable

2018-09-09 09:11:17,411 DEBG 'zato-server2' stdout output:
2018-09-09 09:11:17,411 - ERROR - 109:DummyThread-40 - zato.server.connection.http_soap.channel:324 - Caught an exception, cid:`c191b669a784db822eaeb428`, status_code:`500`, _format_exc:`Traceback (most recent call last):
  File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 268, in dispatch
    payload, worker_store, self.simple_io_config, post_data, path_info, soap_action)
  File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 495, in handle
  File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 521, in update_handle
    raise resp_e
TypeError: datetime.date(1976, 2, 18) is not JSON serializable

Thanks in advance.