python 之sqlalchemy many to many

2021-10-03 04:04:05

 # -*- coding: utf-8 -*-

 """

 @author: zengchunyun

 """

 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table

 from sqlalchemy.orm import sessionmaker, relationship, backref

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy import create_engine

 Base = declarative_base()

 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)

 class Association(Base):

     __tablename__ = 'association'

     left_id = Column(Integer, ForeignKey("left.id"), primary_key=True)

     right_id = Column(Integer, ForeignKey("right.id"), primary_key=True)

     extra_data = Column(String(50))

     child = relationship("Child", back_populates="parents")

     parent = relationship("Parent", back_populates="children")

 class Parent(Base):

     __tablename__ = 'left'

     id = Column(Integer, primary_key=True)

     children = relationship("Association", back_populates='parent')

 class Child(Base):

     __tablename__ = 'right'

     id = Column(Integer, primary_key=True)

     parents = relationship("Association", back_populates="child")

 Base.metadata.create_all(engine)

 DBSession = sessionmaker()

 DBSession.configure(bind=engine)

 session = DBSession()  # 打开数据连接

 # 插入数据方式一

 # p = Parent()

 # c = Child()

 # a = Association(extra_data="ss")

 # a.parent = p

 # a.child = c

 # 插入数据方式二

 c = Child()

 a = Association(extra_data='dd')

 a.parent = Parent()

 c.parents.append(a)

 # 插入数据方式三

 # p = Parent()

 # a = Association(extra_data="some data")

 # a.child = Child()

 # p.children.append(a)

 #

 # for assoc in p.children:

 #     print(assoc.extra_data)

 #     print(assoc.child)

 session.add(a)

 session.commit()

第二种方式

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

 class Association(Base):

     __tablename__ = 'association'

     left_id = Column(Integer, ForeignKey("left.id"), primary_key=True)

     right_id = Column(Integer, ForeignKey("right.id"), primary_key=True)

     extra_data = Column(String(50))

     child = relationship("Child", backref="parents")

     parent = relationship("Parent", backref="children")

 class Parent(Base):

     __tablename__ = 'left'

     id = Column(Integer, primary_key=True)

 class Child(Base):

     __tablename__ = 'right'

     id = Column(Integer, primary_key=True)

第三种方式，完整版

 #!/usr/bin/env python3

 # -*- coding: utf-8 -*-

 """

 @author: zengchunyun

 """

 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table

 from sqlalchemy.orm import sessionmaker, relationship, backref

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy import create_engine

 Base = declarative_base()

 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)

 class Association(Base):

     __tablename__ = 'association'

     left_id = Column(Integer, ForeignKey("left.id"), primary_key=True)

     right_id = Column(Integer, ForeignKey("right.id"), primary_key=True)

     extra_data = Column(String(50))

     child = relationship("Child")

 class Parent(Base):

     __tablename__ = 'left'

     id = Column(Integer, primary_key=True)

     children = relationship("Association")

 class Child(Base):

     __tablename__ = 'right'

     id = Column(Integer, primary_key=True)

 Base.metadata.create_all(engine)

 DBSession = sessionmaker()

 DBSession.configure(bind=engine)

 session = DBSession()  # 打开数据连接

 p = Parent()

 a = Association(extra_data='dasa')

 a.child = Child()

 p.children.append(a)

 session.add(p)  #注意，这里必须先添加p，否则关系映射不成功

 session.add(a)  #再添加a,记录就能添加成功了

 session.commit()

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

many to many table形式

 #!/usr/bin/env python3

 # -*- coding: utf-8 -*-

 """

 @author: zengchunyun

 """

 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table

 from sqlalchemy.orm import sessionmaker, relationship, backref

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy import create_engine

 Base = declarative_base()

 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)

 PC = Table("p_c", Base.metadata,

            Column("left_id", Integer, ForeignKey("left.id")),

            Column("right_id",Integer, ForeignKey("right.id"))

            )

 class Parent(Base):

     __tablename__ = 'left'

     id = Column(Integer, primary_key=True)

     name = Column(String(22))

     child = relationship("Child", secondary=PC)

 class Child(Base):

     __tablename__ = 'right'

     id = Column(Integer, primary_key=True)

     name = Column(String(22))

 Base.metadata.create_all(engine)

 DBSession = sessionmaker()

 DBSession.configure(bind=engine)

 session = DBSession()  # 打开数据连接

 p1 = Parent(name='zeng')

 c1 = Child(name="haha")

 p1.child.append(c1)  # 只有存在relationship关系的对象才能通过append形式添加记录

 # 或者p1.child = [c1]

 session.add(p1)

 session.commit()

Table形式二

 #!/usr/bin/env python3

 # -*- coding: utf-8 -*-

 """

 @author: zengchunyun

 """

 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Table

 from sqlalchemy.orm import sessionmaker, relationship, backref

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy import create_engine

 Base = declarative_base()

 engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/day11',echo=True)

 PC = Table("p_c", Base.metadata,

            Column("left_id", Integer, ForeignKey("left.id")),

            Column("right_id",Integer, ForeignKey("right.id"))

            )

 class Parent(Base):

     __tablename__ = 'left'

     id = Column(Integer, primary_key=True)

     name = Column(String(22))

     child = relationship("Child", secondary=PC,

                          back_populates="parents")

 class Child(Base):

     __tablename__ = 'right'

     id = Column(Integer, primary_key=True)

     name = Column(String(22))

     parents = relationship("Parent", secondary=PC,

                            back_populates="child")

 Base.metadata.create_all(engine)

 DBSession = sessionmaker()

 DBSession.configure(bind=engine)

 session = DBSession()  # 打开数据连接

 # # 第一种数据插入方式

 # p1 = Parent(name='zeng')

 # c1 = Child(name="haha")

 # p1.child.append(c1)  # 只有存在relationship关系的对象才能通过append形式添加记录

 # # 或者p1.child = [c1]

 # session.add(p1)

 # 第二种

 # p1 = Parent(name='zeng')

 # c1 = Child(name='haha')

 # c1.parents.append(p1)

 # session.add(c1)

 # 第三种

 # p1 = Parent(name='zeng')

 # p1.child = [Child(name="hah")]

 # session.add(p1)

 # 第四种

 p1 = Parent(name="zcy", child=[Child(name='sasa')])

 session.add(p1)

 session.commit()

 # 以上四种插入效果都是一样的

Table最后一种写法

 PC = Table("p_c", Base.metadata,

            Column("left_id", Integer, ForeignKey("left.id")),

            Column("right_id",Integer, ForeignKey("right.id"))

            )

 class Parent(Base):

     __tablename__ = 'left'

     id = Column(Integer, primary_key=True)

     name = Column(String(22))

     child = relationship("Child", secondary=PC,

                          backref="parents")

 class Child(Base):

     __tablename__ = 'right'

     id = Column(Integer, primary_key=True)

     name = Column(String(22))

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

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

码农公寓

相关文章