1 # -*- coding: utf-8 -*- 2 """ 3 @author: zengchunyun 4 """ 5 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table 6 from sqlalchemy.orm import sessionmaker, relationship, backref 7 from sqlalchemy.ext.declarative import declarative_base 8 from sqlalchemy import create_engine 9 10 Base = declarative_base()11 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)12 13 14 15 class Association(Base):16     __tablename__ = 'association'17     left_id = Column(Integer, ForeignKey("left.id"), primary_key=True)18     right_id = Column(Integer, ForeignKey("right.id"), primary_key=True)19     extra_data = Column(String(50))20     child = relationship("Child", back_populates="parents")21     parent = relationship("Parent", back_populates="children")22 23 24 class Parent(Base):25     __tablename__ = 'left'26     id = Column(Integer, primary_key=True)27     children = relationship("Association", back_populates='parent')28 29 class Child(Base):30     __tablename__ = 'right'31     id = Column(Integer, primary_key=True)32     parents = relationship("Association", back_populates="child")33 34 35 Base.metadata.create_all(engine)36 37 DBSession = sessionmaker()38 DBSession.configure(bind=engine)39 session = DBSession()  # 打开数据连接40 41 42 # 插入数据方式一43 # p = Parent()44 # c = Child()45 # a = Association(extra_data="ss")46 # a.parent = p47 # a.child = c48 # 插入数据方式二49 c = Child()50 a = Association(extra_data='dd')51 a.parent = Parent()52 c.parents.append(a)53 54 # 插入数据方式三55 # p = Parent()56 # a = Association(extra_data="some data")57 # a.child = Child()58 # p.children.append(a)59 #60 # for assoc in p.children:61 #     print(assoc.extra_data)62 #     print(assoc.child)63 64 65 session.add(a)66 session.commit()

 

 

第二种方式

上面的其它代码不变，只修改relationship关系，效果是一样的

1 class Association(Base): 2     __tablename__ = 'association' 3     left_id = Column(Integer, ForeignKey("left.id"), primary_key=True) 4     right_id = Column(Integer, ForeignKey("right.id"), primary_key=True) 5     extra_data = Column(String(50)) 6     child = relationship("Child", backref="parents") 7     parent = relationship("Parent", backref="children") 8  9 10 class Parent(Base):11     __tablename__ = 'left'12     id = Column(Integer, primary_key=True)13 14 class Child(Base):15     __tablename__ = 'right'16     id = Column(Integer, primary_key=True)

 第三种方式，完整版

1 #!/usr/bin/env python3 2 # -*- coding: utf-8 -*- 3 """ 4 @author: zengchunyun 5 """ 6 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table 7 from sqlalchemy.orm import sessionmaker, relationship, backref 8 from sqlalchemy.ext.declarative import declarative_base 9 from sqlalchemy import create_engine10 11 Base = declarative_base()12 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)13 14 class Association(Base):15     __tablename__ = 'association'16     left_id = Column(Integer, ForeignKey("left.id"), primary_key=True)17     right_id = Column(Integer, ForeignKey("right.id"), primary_key=True)18     extra_data = Column(String(50))19     child = relationship("Child")20 21 22 class Parent(Base):23     __tablename__ = 'left'24     id = Column(Integer, primary_key=True)25     children = relationship("Association")26 27 class Child(Base):28     __tablename__ = 'right'29     id = Column(Integer, primary_key=True)30 31 32 Base.metadata.create_all(engine)33 34 DBSession = sessionmaker()35 DBSession.configure(bind=engine)36 session = DBSession()  # 打开数据连接37 38 p = Parent()39 a = Association(extra_data='dasa')40 a.child = Child()41 p.children.append(a)42 session.add(p)  #注意，这里必须先添加p，否则关系映射不成功43 session.add(a)  #再添加a,记录就能添加成功了44 session.commit()

 

 

以上三种方式最终效果是一样的，针对第三张表的写法还有另一种实现方式，通过Table创建，有时间再补上

 

many to many table形式

1 #!/usr/bin/env python3 2 # -*- coding: utf-8 -*- 3 """ 4 @author: zengchunyun 5 """ 6 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table 7 from sqlalchemy.orm import sessionmaker, relationship, backref 8 from sqlalchemy.ext.declarative import declarative_base 9 from sqlalchemy import create_engine10 11 Base = declarative_base()12 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)13 14 15 PC = Table("p_c", Base.metadata,16            Column("left_id", Integer, ForeignKey("left.id")),17            Column("right_id",Integer, ForeignKey("right.id"))18            )19 20 class Parent(Base):21     __tablename__ = 'left'22     id = Column(Integer, primary_key=True)23     name = Column(String(22))24     child = relationship("Child", secondary=PC)25 26 27 class Child(Base):28     __tablename__ = 'right'29     id = Column(Integer, primary_key=True)30     name = Column(String(22))31 32 33 Base.metadata.create_all(engine)34 35 DBSession = sessionmaker()36 DBSession.configure(bind=engine)37 session = DBSession()  # 打开数据连接38 39 p1 = Parent(name='zeng')40 c1 = Child(name="haha")41 p1.child.append(c1)  # 只有存在relationship关系的对象才能通过append形式添加记录42 # 或者p1.child = [c1]43 session.add(p1)44 session.commit()

 Table形式二

1 #!/usr/bin/env python3 2 # -*- coding: utf-8 -*- 3 """ 4 @author: zengchunyun 5 """ 6 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table 7 from sqlalchemy.orm import sessionmaker, relationship, backref 8 from sqlalchemy.ext.declarative import declarative_base 9 from sqlalchemy import create_engine10 11 Base = declarative_base()12 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)13 14 15 PC = Table("p_c", Base.metadata,16            Column("left_id", Integer, ForeignKey("left.id")),17            Column("right_id",Integer, ForeignKey("right.id"))18            )19 20 class Parent(Base):21     __tablename__ = 'left'22     id = Column(Integer, primary_key=True)23     name = Column(String(22))24     child = relationship("Child", secondary=PC,25                          back_populates="parents")26 27 28 class Child(Base):29     __tablename__ = 'right'30     id = Column(Integer, primary_key=True)31     name = Column(String(22))32     parents = relationship("Parent", secondary=PC,33                            back_populates="child")34 35 36 Base.metadata.create_all(engine)37 38 DBSession = sessionmaker()39 DBSession.configure(bind=engine)40 session = DBSession()  # 打开数据连接41 42 43 # # 第一种数据插入方式44 # p1 = Parent(name='zeng')45 # c1 = Child(name="haha")46 # p1.child.append(c1)  # 只有存在relationship关系的对象才能通过append形式添加记录47 # # 或者p1.child = [c1]48 # session.add(p1)49 # 第二种50 # p1 = Parent(name='zeng')51 # c1 = Child(name='haha')52 # c1.parents.append(p1)53 # session.add(c1)54 # 第三种55 # p1 = Parent(name='zeng')56 # p1.child = [Child(name="hah")]57 # session.add(p1)58 # 第四种59 p1 = Parent(name="zcy", child=[Child(name='sasa')])60 session.add(p1)61 session.commit()62 63 # 以上四种插入效果都是一样的

 

Table最后一种写法

1 PC = Table("p_c", Base.metadata, 2            Column("left_id", Integer, ForeignKey("left.id")), 3            Column("right_id",Integer, ForeignKey("right.id")) 4            ) 5  6 class Parent(Base): 7     __tablename__ = 'left' 8     id = Column(Integer, primary_key=True) 9     name = Column(String(22))10     child = relationship("Child", secondary=PC,11                          backref="parents")12 13 14 class Child(Base):15     __tablename__ = 'right'16     id = Column(Integer, primary_key=True)17     name = Column(String(22))

以上几种Table形式多对多写法效果是一样的，只是在查询上有一定区别，

第二种table与第三种其实是完全一样的效果