Class that connects to MySQL server and provides ability to list databases, list tables, dump tables as json, serialize JSON data, and write table rows as individual JSON objects to a file (requires PyMySQL)

mysql_explorer.py

#!/usr/bin/env python

import pymysql.cursors
import datetime
import decimal
import json
import os

class mysql_explorer():
    def __init__(self, user, password, host='127.0.0.1'):
        self.conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            cursorclass=pymysql.cursors.SSDictCursor
        )

    def serialize_json(self, item):
        # Temp dict object
        temp = {}
        # Iterate over items, handle non-json serializable data types, and convert to json
        for key, value in item.items():
            # Handle bytearrays and bytes
            if isinstance(value, bytearray) or isinstance(value, bytes):
                try:
                    temp[key] = value.decode()
                except:
                    # Handle ip addresses
                    temp[key] = '.'.join(f'{c}' for c in value)
            # Handle datetime objects
            elif isinstance(value, datetime.datetime):
                temp[key] = value.isoformat()
            # Handle Decimal objects
            elif isinstance(value, decimal.Decimal):
                temp[key] = float(value)
            # Otherwise, save raw value
            else:
                temp[key] = value
        # Ensure good json and return, or return None (maybe empty dict instead?)
        try:
            json.dumps(temp)
            return temp
        except TypeError:
            return None

    def list_dbs(self):
        with self.conn.cursor() as cursor:
            cursor.execute('SHOW DATABASES')
            return [list(db.values())[0] for db in cursor.fetchall()]

    def list_tables(self, db):
        with self.conn.cursor() as cursor:
            cursor.execute(f'USE {db}')
            cursor.execute('SHOW TABLES')
            return [list(table.values())[0] for table in cursor.fetchall()]

    def table_to_json(self, db, table):
        # Get cursor
        cursor = self.conn.cursor()
        # Select everything from each table
        cursor.execute(f'SELECT * FROM {db}.{table}')
        # Retrieve table body
        body = cursor.fetchall()
        jtable = []
        # Iterate over body rows and save as json
        for row in body:
            jtable.append(dict(zip(row.keys(), row.values())))
        # Close cursor
        cursor.close()
        # Return
        return jtable

    def table_to_file(self, db, table, outdir='data'):
        # If outdir (default "./data") does not exist, create it
        if not os.path.exists(outdir):
            os.mkdir(outdir)
        # Create and append to outfile
        with open(os.path.join(outdir, f'{table}.json'), 'a+') as outfile:
            # Get cursor
            cursor = self.conn.cursor()
            # Select everything from each table
            cursor.execute(f'SELECT * FROM {db}.{table}')
            # Retrieve first row
            row = cursor.fetchone()
            # Iterate over rows
            while row:
                # Make json-serializable
                jrow = self.serialize_json(row)
                # Save json to file
                if jrow:
                    outfile.write(f'{json.dumps(jrow)}\n')
                # Retrieve another row
                row = cursor.fetchone()
        # Close cursor
        cursor.close()