sqlalchemy_schema_to_dict.py

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
import yaml

Base = declarative_base()

# Association table for many-to-many relationship
association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")

class Left(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    rights = relationship("Right", secondary=association_table, back_populates="lefts")

class Right(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    lefts = relationship("Left", secondary=association_table, back_populates="rights")

# Create an engine and session (example purposes only, normally you'd configure this)
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

def sqlalchemy_schema_to_dict(base):
    schema_dict = {}
    for table in base.metadata.tables.values():
        table_dict = {
            'columns': {col.name: {
                'type': str(col.type),
                'nullable': col.nullable,
                'primary_key': col.primary_key
            } for col in table.columns},
            'relationships': {}
        }
        
        for mapper in base._decl_class_registry.values():
            if hasattr(mapper, '__tablename__') and mapper.__tablename__ == table.name:
                for rel in mapper.__mapper__.relationships:
                    table_dict['relationships'][rel.key] = {
                        'direction': str(rel.direction),
                        'target': rel.target.name,
                        'uselist': rel.uselist,
                        'secondary': str(rel.secondary) if rel.secondary else None
                    }
        
        schema_dict[table.name] = table_dict
    return schema_dict

schema_dict = sqlalchemy_schema_to_dict(Base)
yaml_schema = yaml.dump(schema_dict, default_flow_style=False)
print(yaml_schema)