check_mysql_foreign_keys.py

import pandas as pd

import dbconn

engine = dbconn.get_engine_by_name("foo")

result = engine.execute("""
    select table_name, column_name, referenced_table_name, referenced_column_name
    from information_schema.key_column_usage
    where constraint_schema = 'smartpr_api'
        and referenced_table_name is not null
    """)

for r in result:
    params = dict(r.items())
    sql = """
        select '%(table_name)s.%(column_name)s', count(*)
        from %(table_name)s t
            left join %(referenced_table_name)s rt
                on t.%(column_name)s = rt.%(referenced_column_name)s
        where t.%(column_name)s is not null
            and rt.%(referenced_column_name)s is null;""" % params

    for t, count in engine.execute(sql):
        print(count, t)