使用sqlalchemy实现外键关联的例子(程序，本例子来源于sqlalchemy官网)

import sqlalchemy as sqla

import sqlalchemy.orm as sqlorm
from sqlalchemy.ext.declarative import declarative_base as sqla_declarative_base


Base = sqla_declarative_base()
engine = sqla.create_engine('sqlite:///test.db', echo=True)


association_table = sqla.Table(
    'association', Base.metadata,
    sqla.Column('page_id', sqla.Integer, sqla.ForeignKey('page.id')),
    sqla.Column('tag_id', sqla.Integer, sqla.ForeignKey('tag.id'))
)


class Page(Base):
    __tablename__ = 'page'
    id = sqla.Column(sqla.Integer, primary_key=True)
    name = sqla.Column(sqla.String)
    tags = sqlorm.relationship('Tag', secondary=association_table)


class Tag(Base):
    __tablename__ = 'tag'
    id = sqla.Column(sqla.Integer, primary_key=True)
    name = sqla.Column(sqla.String)
    pages = sqlorm.relationship('Page', secondary=association_table)


Base.metadata.bind = engine
Base.metadata.create_all()


Session = sqlorm.scoped_session(sqlorm.sessionmaker(bind=engine))


def save_page():
    sess = Session()
    try:
        page = Page(name='Python API Page')
        sess.add(page)


        sess.flush()
        sess.commit()
    finally:
        sess.close()


def add_tag():
   sess = Session()
   try:
       python_tag = Tag(name='python')
       api_tag = Tag(name='api')
       sess.add(python_tag)
       sess.add(api_tag)
       page = sess.query(Page).first()
       page.tags.append(python_tag)
       page.tags.append(api_tag)


       sess.flush()
       sess.commit()
   finally:
       sess.close()


def remove_tag():
    sess = Session()
    try:
        page = sess.query(Page).first()
        api_tag = sess.query(Tag).filter(Tag.name=='api').first()
        page.tags.remove(api_tag)
        sess.flush()
        sess.commit()
    finally:
        sess.close()


if __name__ == '__main__':
    save_page()
    add_tag()
    remove_tag()