sqlalchemy relationship

sqlalchemy_relation.py

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
#
# Use this file to build your own SSCCE
# SSCCE = Short, Self Contained, Correct (Compatible) Example
# see http://sscce.org/
#
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Standard imports

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import deferred, class_mapper
from sqlalchemy import Integer, String, Text, Binary, Column, ForeignKey, DateTime
from sqlalchemy import inspect
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship
from sqlalchemy.orm import joinedload, subqueryload


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# You probably don't need to overwrite this
Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    # domain

    id = Column(Integer, primary_key=True)

    cs = relationship("C",
        secondary= "join(A2B, C, A2B.b_id == C.b_id)"
    	,primaryjoin="and_(A.id == A2B.a_id)"
        ,secondaryjoin="C.b_id == foreign(A2B.b_id)",
        )

class A2B(Base):
    __tablename__ = 'a2b'
    # domain2fqdnset
    a_id = Column(ForeignKey('a.id'), primary_key=True)
    b_id = Column(ForeignKey('b.id'), primary_key=True)

class B(Base):
    __tablename__ = 'b'
    # fqdnset
    id = Column(Integer, primary_key=True)

class C(Base):
    __tablename__ = 'c'
    # cert

    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey('b.id'), primary_key=True)


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Define some models that inherit from Base


engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# you shouldn't change these 2 line

sessionFactory = sessionmaker(bind=engine)
s = sessionFactory()


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Start your example here:

s.query(A).join(A.cs).all()