sqlalchemy 外键

sqlalchemy.orm import relationship from sqlalchemy import Column from sqlalchemy import Integer,String,ForeignKey class Depart(Base): __tablename__ = ‘depart‘ id = Column(Integer, primary_key=True) title = Column(String(32), index=True, nullable=False) class Users(Base): __tablename__ = ‘users‘ id = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=False) depart_id = Column(Integer,ForeignKey("depart.id")) # dp 创建联系，不创建字段 dp = relationship("Depart", backref=‘pers‘)

二、数据操作

# 1. 查询所有用户+所属部门名称
ret = session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id == Depart.id).all()
for row in ret:
    print(row.id,row.name,row.title)

# left join, sql语句
query = session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id == Depart.id,isouter=True)
print(query)

# 2. relation字段:查询所有用户+所属部门名称 ====> 正向查询 推荐
ret = session.query(Users).all()
for row in ret:
    print(row.id,row.name,row.depart_id,row.dp.title)

# 3. relation字段:查询销售部所有的人员  =======> 反向查询 推荐
obj = session.query(Depart).filter(Depart.title == ‘销售‘).first()
for row in obj.pers:
    print(row.id,row.name,obj.title)

# 4. 创建一个名称叫：IT部门，再在该部门中添加一个员工：a
# 方式一：
d1 = Depart(title=‘IT‘)
session.add(d1)
session.commit()
#
u1 = Users(name=‘a‘,depart_id=d1.id)
session.add(u1)
session.commit()

# 方式二：使用relation
u1 = Users(name=‘a‘,dp=Depart(title=‘IT‘))
session.add(u1)
session.commit()

# 5. 创建一个名称叫：保洁的部门，再在该部门中添加多个员工：a/b/c
d1 = Depart(title=‘保洁‘)
d1.pers = [Users(name=‘a‘),Users(name=‘b‘),Users(name=‘c‘),]
session.add(d1)
session.commit()

 

sqlalchemy 外键

标签：user   销售   title   ble   int   primary   fir   部门   art