from sqlalchemy.ext.declarative import declarative_base

"""
This gist demonstrates ways to retrieve foreign keys in an SQLAlchemy database schema.
"""

Base = declarative_base()
metadata = Base.metadata

# then you must populate schema
# one method: sqlacodegen postgresql://user:pass@localhost/dbname --outfile schema.py

# once you have a schema,
# you can do the following to retrieve all foreign keys for a single table by column:
def identify_foreign_keys(tablename):
    """
    :param tablename: string
    """
    retlist = []
    for c in metadata.tables[tablename].columns:
        # if no foreign keys, c.foreign_keys will be an empty set
        for fk in c.foreign_keys:
            retlist.append({'column': str(c), 'foreign_linked_column': str(fk.column)})
    return retlist

# Table objects also have a foreign_keys member, for example:
for namestr, table in metadata.tables.items():
    if len(table.foreign_keys) > 0:
        print("{} - {}".format(namestr, table.foreign_keys))