【Flask】ORM一对多关联关系

SQLAlchemy提供了一个relationship，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。另外，可以通过backref来指定反向访问的属性名称。

配置数据库、创建数据库引擎、创建基类

#配置数据库
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = 'root'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

#创建数据库引擎
engine=create_engine(DB_URI)

#创建基类，所有的模型类继承基类
Base=declarative_base(engine)

#创建session
session=sessionmaker(engine)()

方法一：

创建模型类

#创建模型类
class Dept(Base):
    __tablename__='t_dept2'
    dept_no=Column(name='dept_no',type_=Integer,primary_key=True,autoincrement=True)
    dept_name=Column(name='dept_name',type_=String(20))
    city=Column(name='city',type_=String(20))
    
    #代表当前部门下的所有员工列表，这种写法不是最好的，最优的写法只要在一个对象中关联就可以了
    emp=relationship('Emp') #参数必须是另一个关联模型类的类名


    def __str__(self):
        return f'部门编号:{self.dept_no}部门:{self.dept_name}城市:{self.city}'

特别注意1：第9行代码：代表当前部门下的所有员工列表；这种写法不是最好的，最优的写法只要在一个对象中关联就可以了

class Emp(Base):
    __tablename__='t_emp2'
    emp_no=Column(name='emp_no',type_=Integer,primary_key=True,autoincrement=True)
    emp_name=Column(name='emp_name',type_=String(20))
    hire_date=Column(name='hire_date',type_=DATE)
    sal=Column(name='sal',type_=DECIMAL(10,2))

    #todo 设置外键关联，在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和主表的主键字段类型保持一致。
    dept_no=Column(ForeignKey('t_dept2.dept_no',ondelete='CASCADE'),name='dept_no',type_=Integer)
    
    #代表员工所属的部门信息
    dept=relationship('Dept')        #参数必须是另一个关联模型类的类名

    def __str__(self):
        return f'员工编号:{self.emp_no}员工姓名:{self.emp_name}员工入职时间:{self.hire_date}员工薪资:{self.sal}'

特别注意2：设置外键关联，在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和主表的主键字段类型保持一致。

dept_no=Column(ForeignKey('t_dept2.dept_no',ondelete='CASCADE'),name='dept_no',type_=Integer)

方法二：将方法一中第九行代码注释，只要在一个模型类中关联就可以了

#创建模型类
class Dept(Base):
    __tablename__='t_dept2'
    dept_no=Column(name='dept_no',type_=Integer,primary_key=True,autoincrement=True)
    dept_name=Column(name='dept_name',type_=String(20))
    city=Column(name='city',type_=String(20))

    #代表当前部门下的所有员工列表，这种写法不是最好的，最优的写法只要在一个对象中关联就可以了
    #emp=relationship('Emp') #参数必须是另一个关联模型类的类名


    def __str__(self):
        return f'部门编号:{self.dept_no}部门:{self.dept_name}城市:{self.city}'

class Emp(Base):
    __tablename__='t_emp2'
    emp_no=Column(name='emp_no',type_=Integer,primary_key=True,autoincrement=True)
    emp_name=Column(name='emp_name',type_=String(20))
    hire_date=Column(name='hire_date',type_=DATE)
    sal=Column(name='sal',type_=DECIMAL(10,2))

    #todo 设置外键关联，在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和主表的主键字段类型保持一致。
    dept_no=Column(ForeignKey('t_dept2.dept_no',ondelete='CASCADE'),name='dept_no',type_=Integer)

    #代表员工所属的部门信息，backref:反向关联的属性名
    dept=relationship('Dept',backref='emp')        #参数必须是另一个关联模型类的类名

    def __str__(self):
        return f'员工编号:{self.emp_no}员工姓名:{self.emp_name}员工入职时间:{self.hire_date}员工薪资:{self.sal}

特别注意3：第12行代码：代表员工所属的部门信息，backref:反向关联的属性名，dept=relationship('Dept',backref='emp') 中的Dept必须是另外一个相关联的类名

特别注意4：relationship,backref需要导入
from sqlalchemy.orm import sessionmaker,relationship,backref

插入数据

def save():
    d1=Dept(dept_name='信息部',city='北京')
    session.add(d1)
    #session.commit()

    e1=Emp(emp_name='python',hire_date='2020-1-1',sal='8888.88',dept_no=1)
    session.add(e1)
    session.commit()

查询数据：

查询1：查询财务部下的所有的员工

d1=session.query(Dept).filter(Dept.dept_name=='财务部').first()
print(d1)
for i in d1.emp:
    print(i)

部门编号:2部门:财务部城市:上海
员工编号:5员工姓名:flask员工入职时间:2022-02-06员工薪资:7777.77
员工编号:6员工姓名:django员工入职时间:2021-12-12员工薪资:10000.00

查询2：查询信息部下员工入职时间大于2022年，并且工资超过8000的员工信息

d2=session.query(Emp).filter(Dept.dept_name=='信息部').filter(Emp.hire_date>'2022-01-01',Emp.sal>8000).first()
print(d2)

员工编号:4员工姓名:jmeter员工入职时间:2022-06-04员工薪资:10000.00

查询3：查询python员工所在的部门

e1=session.query(Emp).filter(Emp.emp_name=='python').first()
print(e1)
print(e1.dept)

查询4：查询员工入职时间大于2022年，并且工资超过7000的员工都在那些部门

e2=session.query(Dept).filter(Emp.hire_date>'2022-1-1',Emp.sal>7000).all()
print(e2)
for i in e2:
    print(i)

[<__main__.Dept object at 0x000001BA5EF8BCA0>, <__main__.Dept object at 0x000001BA5EF8BF40>]
部门编号:1部门:信息部城市:北京
部门编号:2部门:财务部城市:上海

查询5：统计工资最高的员工所在的部门

res13=session.query(Emp,func.max(Emp.sal)).first()[0]   #员工编号:2~员工姓名:james~来自:北京~薪资:50000.00
print(res13)
print(type(res13))                                      #Emp对象，可以调用属性
print(res13.dept)

更新数据

将员工表中员工编号为18的员工信息所在的部门人数改为500

def update():
    res1=session.query(Emp).filter(Emp.emp_id==18).first()
    print(res1)
    res2=res1.dept
    print(type(res2))   	#<class '__main__.Dept'>
    res2.emp_count=500
    session.commit()

疑问：需要博友指教一下：

问题：统计每个部门下工资最高的员工信息，下面代码怎么修改？？？

res15=session.query(Emp,func.max(Emp.sal)).group_by(Emp.dept_id).all()
print(res15)