一, 创建表关系

 1 from sqlalchemy.ext.declarative import declarative_base
 2 from sqlalchemy import Column, Integer, String, ForeignKey
 3 from sqlalchemy.orm import relationships
 4 from sqlalchemy import create_engine
 5 
 6 Base = declarative_base()
 7 
 8 
 9 class Hotel(Base):
10     __tablename__ = "hotel"
11     id = Column(Integer, primary_key=True)
12     girl_id = Column(Integer, ForeignKey("girl.id"))
13     boy_id = Column(Integer, ForeignKey("boy.id"))
14 
15 
16 class Girl(Base):
17     __tablename__ = "girl"
18     id = Column(Integer, primary_key=True)
19     name = Column(String(32), index=True)
20 
21     # 创建关系
22     boys = relationships("Boy",secondary="hotel",backref="girl2boy")
23 
24 
25 class Boy(Base):
26     __tablename__ = "boy"
27     id = Column(Integer, primary_key=True)
28     name = Column(String(32), index=True)
29 
30 
31 engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy?charset=utf8")
32 
33 Base.metadata.create_all(engine)
多对多创建表关系

二, 基于relationship增加数据

 1 from M2M.create_table import Girl, Boy, Hotel, engine
 2 # 创建连接
 3 from sqlalchemy.orm import sessionmaker
 4 # 创建数据表操作对象sessionmaker
 5 DB_session = sessionmaker(engine)
 6 db_session = DB_session()
 7 
 8 # 通过boy添加girl和hotel数据
 9 boy = Boy(name="小黑兔")
10 boy.girl2boy = [Girl(name="小红兔"),Girl(name="小花兔")]
11 db_session.add(boy)
12 db_session.commit()
13 
14 
15 # 通过girl添加boy和hotel数据
16 girl = Girl(name="小白兔")
17 girl.boys = [Boy(name="小蓝兔")]
18 db_session.add(girl)
19 db_session.commit()
增加数据

三, 基于relationship查询数据

 1 from M2M.create_table import Girl, Boy, Hotel, engine
 2 # 创建连接
 3 from sqlalchemy.orm import sessionmaker
 4 # 创建数据表操作对象 sessionmaker
 5 DB_session = sessionmaker(engine)
 6 db_session = DB_session()
 7 
 8 # 通过boy查询对应的girl
 9 boy_list = db_session.query(Boy).all()
10 for boy in boy_list:
11     for girl in boy.girl2boy:
12         print(boy.name, girl.name)
13 
14 # 通过girl查询对应的boy
15 girl_list = db_session.query(Girl).all()
16 for girl in girl_list:
17     for boy in girl.boys:
18         print(girl.name, boy.name)
查询数据