Read entire database with sqlalchemy and dump as JSON

Question

Django has the convenience manage.py command dumpdata which can be configured to dump an entire database as JSON.

At present I am confined to using sqlalchemy and I'd like to do the same thing:

Take as input a connection string like 'mysql+pymysql://user:pwd@localhost:3306/' and get the contents of the database as JSON (I don't require all the meta info that Django provides, but I won't mind).

I found this question elaborating how to dump SQLAlchemy objects to JSON and this from the sqlalchemy documentation outlining how to get all tablenames from a database:

meta = MetaData()
input_db = f'sqlite:///tmpsqlite'
engine = create_engine(input_db)
meta.reflect(bind=engine)
print(meta.tables)

How do I retrieve all of the content of these tables and then convert them to JSON? Is there a built-in command in sqlalchemy similar to django's dumpdata functionality?

Answer 1

Leaving my solution here for posterity:

import json

def dump_sqlalchemy(output_connection_string,output_schema):
    """ Returns the entire content of a database as lists of dicts"""
    engine = create_engine(f'{output_connection_string}{output_schema}')
    meta = MetaData()
    meta.reflect(bind=engine)  # http://docs.sqlalchemy.org/en/rel_0_9/core/reflection.html
    result = {}
    for table in meta.sorted_tables:
        result[table.name] = [dict(row) for row in engine.execute(table.select())]
    return json.dumps(result)