Incorrect handling of DATETIME data type in mysql 5.7

[brandinchiu]

I'm attempting to pull binlog data from a mysql 5.7 database, but I'm getting an error when trying to serialize certain data from the logs.

I'll be honest in saying that I'm not very familiar with Python, so if this is not the appropriate forum for this discussion, feel free to close this issue and I'll move my question somewhere else.

This is the error I receive:

datetime.datetime(2020, 8, 25, 19, 19, 4) is not JSON serializable

When running this:

...
stream = BinLogStreamReader(
    connection_settings= {
      "host": "<HOST>",
      "port": <PORT>,
      "user": "<USER>",
      "passwd": "<PASSWORD>"},
    server_id=100,
    blocking=True,
    resume_stream=True,
    only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])

  for binlogevent in stream:
    for row in binlogevent.rows:
      event = {"schema": binlogevent.schema,
      "table": binlogevent.table,
      "type": type(binlogevent).__name__,
      "row": row
      }

      print json.dumps(event)
...

My understanding is that the datetime object is being generated by this package, but I'm not sure who should be responsible for handling it's serializability to JSON.

[the4thdoctor]

I had the same problem with pgchameleon.
You can grab the solution here and here

[brandinchiu]

@the4thdoctor : so this is just a problem with the package then, and I'll need to solve it externally using the proposed solution above?

ie. this isn't something I've done explicitly wrong in my implementation of python-mysql-replication?

[the4thdoctor]

Yep. As far as I remember this library always returned the data types from the replica converted to the corresponding python object. I suppose is implicitly done by pymysql anyway.

[dongwook-chan]

Actually, pymysql does nothing about determining the type. This library explicitly chooses Python types for each internal types in MySQL.
Anyways, try out the code below to serialize any Dict to JSON.

import json

class CustomEncoder(json.JSONEncoder):
    def default(self, o):
        try:
            # Let the base class's default method try to serialize the object.
            return super(CustomEncoder, self).default(o)
        except TypeError:
            # If it raises a TypeError, cast the object to a string.
            return str(o)

data = {
    'integer': 42,
    'string': 'Hello, World!',
    'set': {1, 2, 3},  # sets are not natively serializable by json
}

json_string = json.dumps(data, cls=CustomEncoder)
print(json_string)

[the4thdoctor]

Thanks, I'll have a look