SQLAlchemy之Foreignkey

接上一篇：https://www.jianshu.com/p/c955764880c3

乔巴.png

先建俩表，指定外键关联，全文围绕这俩例子展开。

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy import Colum,INT,VARCHAR,Foreignkey

class Student(Base):
    __tablename__ = "student"
   id = Column(INT,primary_key=True)
    name = Column(VARCHAR(32))
   school_id = Column(INT,Foreignkey("school.id"))  # 建立外键关系，理解为该表外键school_id关联shool表的id字段
    
class School(Base):
    __tablename__ = "school"
    id = Column(INT,primary_key=True)
    name = Column(VARCHAR(32))

    # 创建引擎
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy?charset=utf8")
Base.metadate.create_all(engine)
#Base.metadate.drop_all(engine)  # 删除所有对象

1 Foreignkey_insert

1 常规玩法（low版）

常规玩法

from sqlalchemy.orm import sessionmaker
from create_table_Foreignkey import engine
Session = sessionmaker(engine)
db_session = Session()

# 1 增加数据
sch_obj = School(name="GGschool")
db_session.add(sch_obj)
db_session.commit()

sch_obj = db_session.query(school).filter(school.name=="GGschool").first()
stu_obj = Student(name="Gavin", school_id="")

db_session.add(stu_obj)
db_session.commit()
# 这是一个非常笨的方法，但是可以完成需求

2 relationship版--正向

从数据库层面上来看student表和school表的关系是1对多的关系，我们在student中设置了外键关联到了school的id上。但是在ORM层面上，我们的Student对象和School对象，还没有关系呢。所以我们通过relationship给他俩建立关系。

从ORM层面建立关系 relationship版本 -- 正向

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy import Colum,INT,VARCHAR,Foreignkey
from sqlalchemy import relationship  # 导入relationship用于建立ORM关系

class Student(Base):
  __tablename__ = "student"
   id = Column(INT,primary_key=True)
  name = Column(VARCHAR(32))
   school_id = Column(INT,Foreignkey("school.id"))  # 建立外键关系，理解为该表外键school_id关联shool表的id字段
    
   stu2sch = relationship("School.id",backref='sch2stu')  # 建立ORM关系

class School(Base):
    __tablename__ = "school"
    id = Column(INT,primary_key=True)
    name = Column(VARCHAR(32))

    # 创建引擎
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy?charset=utf8")
Base.metadate.create_all(engine)
#Base.metadate.drop_all(engine)  # 删除所有对象

添加数据

from sqlalchemy.orm import sessionmaker
from create_table_Foreignkey import engine
Session = sessionmaker(engine)
db_session = Session()

# 1 增加数据
stu_obj = Student(name="Gavin", stu2sch=School(name="GGschool"))  # ORM的relationship操作自动处理id主键
db_session.add(stu_obj)
db_session.commit()

3 relationship版--反向

sch_obj = School(name="CCschool")  # 新建一个学校对象
sch_obj.sch2stu = [Student(name="Gavin"),Student(name="Carrie")]  # 通过反向关系添加两名学生
db_session.add(sch_obj)  # 创建添加语句
db_session.commit()  # 执行

4 relationship--查询

查询

sch_obj = db_session.query(School).filter(School.name="GGschool").first()  # 查询GGschool
GG_stu_obj = db_session.query(Student).filter(Student.school_id == sch_obj.id).first()  # 查询GGsholl中的学生

relationship版--正向

stu_obj = db_session.query(Student).filter(Student.name=="Gavin").first() # Gavin学生对象
sch_obj = stu_obj.stu2sch.name

relationship版-反向

sch_obj_list = db_session.query(School).all()
for row in sch_obj_list:
    for stu in row.sch2stu:  # row是一个列表
      print(row.name, stu.name)

sch_obj.sch2stu.name

4 relationship--删 （略）

5 relationship--改 （略）

---

---

---

---

---

Foriegnkey到这里结束，但是你以为SQLAlchemy到这里结束了么，too naive，未完待续。。。

序：