python 之sqlalchemy many to many

 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 = p
47 # a.child = c
48 # 插入数据方式二
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_engine
10 
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_engine
10 
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_engine
10 
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与第三种其实是完全一样的效果