• 周日. 9月 25th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

python之 sqlalchemy

admin

11月 28, 2021

one To many

 1 #!/usr/bin/env python3
 2 # -*- coding: utf-8 -*-
 3 """
 4 @author: zengchunyun
 5 """
 6 from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey
 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:[email protected]:3306/day11',echo=True)
13 
14 
15 class Child(Base):
16     __tablename__ = 'child'  # 表名
17     id = Column(Integer, primary_key=True)  # 表字段
18     parent_id = Column(Integer, ForeignKey('parent.id'))  # 外键最好不要直接用类名.字段形式,避免类还没有实例化,最好以表名.字段形式
19     # 外键关联到另一张表的id字段,通过查找parent_id字段可以找到父亲是谁
20     parent = relationship('Parent')  # 通过查到儿子后,通过儿子.parent.name 可以找到父亲是谁,实例相当于儿子.parent等于Parent类.类直接访问自己的name属性
21 
22 
23 class Parent(Base):
24     __tablename__ = 'parent'
25     id = Column(Integer, primary_key=True)
26     name = Column(String(64))
27     # children = relationship(Child)  # 当设置外键时,第一张表如果是先创建,可以使用类名形式关系映射,否则会造成未声明先引用,最好以表名.字段进行关系映射
28     # 这个是单向映射关联,即一个父亲对应多个儿子,或者多个儿子对应一个父亲
29 
30 
31 
32 Base.metadata.create_all(engine)
33 
34 DBSession = sessionmaker()
35 DBSession.configure(bind=engine)
36 session = DBSession()  # 打开数据连接
37 
38 ret = session.query(Child).filter(Child.id==1).one()
39 print(ret.parent.name)
40 ret = session.query(Parent).filter(Parent.id == 1).one()
41 print(ret.name)

one to many

 1 class Parent(Base):
 2     __tablename__ = 'parent'  # 表名
 3     id = Column(Integer, primary_key=True)
 4     name = Column(String(64))
 5     children = relationship('Child', back_populates='parent')  # 第一个参数为类名,当被关联的表先创建时,可以直接写类名,否则只能写字符串形式名字
 6     # 第二个参数为双向one-to-many关系,即反向的many-to-one
 7 
 8     def __repr__(self):
 9         return "id: {}, name: {}".format(self.id, self.name)
10 
11 
12 class Child(Base):
13     __tablename__ = 'child'
14     id = Column(Integer, primary_key=True)
15     name = Column(String(43))
16     parent_id = Column(Integer, ForeignKey('parent.id'))
17     parent = relationship('Parent', back_populates='children')
18 
19     def __repr__(self):
20         return "id: {}, parent_id: {}, ".format(self.id, self.parent_id)
21 
22 
23 ret = session.query(Parent).filter(Parent.id ==1).all()
24 print(ret)
25 print(ret[0].children)  # 通过父亲找儿子
26 
27 ret = session.query(Child).filter(Child.parent_id == Parent.id).all()
28 print(ret)
29 print(ret[0].parent.name)  # 通过儿子找父亲

 one to many,这个效果与上面这个实例一样,这样可以少写一个

 # children = relationship('Child', back_populates='parent') 映射关系
 1 class Parent(Base):
 2     __tablename__ = 'parent'  # 表名
 3     id = Column(Integer, primary_key=True)
 4     name = Column(String(64))
 5     # children = relationship('Child', back_populates='parent')  # 第一个参数为类名,当被关联的表先创建时,可以直接写类名,否则只能写字符串形式名字
 6     # 第二个参数为双向one-to-many关系,即反向的many-to-one
 7 
 8     def __repr__(self):
 9         return "id: {}, name: {}".format(self.id, self.name)
10 
11 
12 class Child(Base):
13     __tablename__ = 'child'
14     id = Column(Integer, primary_key=True)
15     name = Column(String(43))
16     parent_id = Column(Integer, ForeignKey('parent.id'))
17     parent = relationship('Parent', backref='children')
18 
19     def __repr__(self):
20         return "id: {}, parent_id: {}, ".format(self.id, self.parent_id)

 one to many, 通过父亲查多个儿子

 1 class Child(Base):
 2     __tablename__ = 'child'  # 表名
 3     id = Column(Integer, primary_key=True)  # 表字段
 4     name = Column(String(43))
 5     parent_id = Column(Integer, ForeignKey('parent.id'))  # 设置child表的字段parent_id为外键关联到parent表的id
 6 
 7 
 8 class Parent(Base):
 9     __tablename__ = 'parent'
10     id = Column(Integer, primary_key=True)
11     name = Column(String(64))
12     children = relationship('Child')  # 通过关系映射,将children映射到child表,这样可以查到父亲下有几个儿子了,
13 
14 ret = session.query(Parent).filter(Parent.id ==1).one()
15 print(ret)
16 print(ret.children)
17 print(ret.children[0].name)

发表回复

您的电子邮箱地址不会被公开。