mysql——sqlAlchemy&ORM：多对多

多对多：

一、创建：

　　注意1：在关系表中设置外键，并且将关系表放在最前面，否则查询不到，

　　注意2：设置relationship时，需要使用关系表.__table__（）,

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")

Base = declarative_base()

# 关系表放在最上边，否则查找不到
class Man2woman(Base):
    __tablename__ = 'man2woman'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    man_id = Column(Integer,ForeignKey('man.id'))
    woman_id = Column(Integer,ForeignKey('woman.id'))

class Man(Base):
    __tablename__ = 'man'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),unique=True)
    age = Column(Integer,nullable=True)

class Woman(Base):
    __tablename__ = 'woman'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),unique=True)
    age = Column(Integer,nullable=True)

    bf = relationship('Man',secondary=Man2woman.__table__,backref='gf')

Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

二、添加：

　　方式1：直接在关系列表中赋值，

　　方式2：通过relationship指定关联，注意在原有表中添加新关系时，需要append，

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

from many2many_create import Man2woman,Man,Woman
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()

# # 添加对象
# m1=Man(name='dong',age=18)
# m2=Man(name='song',age=18)
# w1=Woman(name='lala',age=16)
# w2=Woman(name='haha',age=16)
# session.add_all([m1,m2,w1,w2,])
# session.commit()

# # 添加多对多关系
# # 方式一：在关系表中直接赋值
# r1=Man2woman(man_id=1,woman_id=2)
# session.add(r1)
# session.commit()

# 方式二：使用relationship指定
m=session.query(Man).filter(Man.name=='song').first()
w1=session.query(Woman).all()
m.gf=w1
session.add(m)
session.commit()

三、查找：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

from many2many_create import Man2woman,Man,Woman
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()

m=session.query(Man).filter(Man.name=='song').first()
print(m.gf)

w=session.query(Woman).filter(Woman.name=='lala').first()
print(w.bf)

注意：