dump-mysql-db-to-json.py

#!/usr/bin/env python
from contextlib import closing
from datetime import datetime
import json
import MySQLdb

DB_NAME = 'x'
DB_USER = 'y'
DB_PASS = 'z'

def get_tables(cursor):
    cursor.execute('SHOW tables')
    result = cursor.fetchall()
    table_names = [r[0] for r in result]
    return table_names


def get_rows_as_dicts(cursor, table):
    contents = []
    cursor.execute('select * from {}'.format(table))
    columns = [d[0] for d in cursor.description]
    for row in cursor.fetchall():
        contents.append(dict(zip(columns, row)))
    return contents


def dump_date(thing):
    if isinstance(thing, datetime):
        return thing.isoformat()
    else:
        return str(thing)


with closing(MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_NAME)) as conn, closing(conn.cursor()) as cursor:
    dump = {}
    for table in get_tables(cursor):
        dump[table] = get_rows_as_dicts(cursor, table)
    print(json.dumps(dump, default=dump_date))