pythonのsqlalchemy外键关联查询

#!/usr/bin/env python

import sqlalchemy
from sqlalchemy import create_engine
from  sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE,Enum,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

# 创建连接 echo=True 就会打印出所有过程信息
engine = create_engine("mysql+pymysql://root:root@localhost/testuser",encoding="utf-8",echo=True)

#
Base = declarative_base() # 生成orm基类

class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_Date = Column(DATE,nullable=False)

    def __repr__(self):
        return "<%s name:%s>"% (self.id,self.name)

class StudyRecord(Base):
    __tablename__ = "study_record"
    id = Column(Integer, primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("student.id"))

    # 关系
    student = relationship("Student",backref="my_study_record")
    def __repr__(self):
        return "< day:%s status:%s>" % (self.id, self.status)


Base.metadata.create_all(engine) # 创建表结构

Session_class = sessionmaker(bind=engine) # 创建与数据库的会话session class 注意，这里返回的是一个class，不是实例

session = Session_class()

# s1 = Student(name="Tom",register_Date="2018-08-08")
# s2 = Student(name="Jerry",register_Date="2018-06-08")
# s3 = Student(name="Lucy",register_Date="2018-08-28")
# s4 = Student(name="Lily",register_Date="2018-08-18")
#
# study_obj1 = StudyRecord(day=1,status="Yes",stu_id=1)
# study_obj2 = StudyRecord(day=2,status="No",stu_id=1)
# study_obj3 = StudyRecord(day=3,status="Yes",stu_id=1)
# study_obj4 = StudyRecord(day=1,status="Yes",stu_id=2)

# session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])
# session.commit()

stu_obj = session.query(Student).filter(Student.name=="Tom").first()

print(stu_obj.my_study_record)

解释：

　　（1）创建表：将43-54行代码解封，此时会自动为您创建相关联的表

重点理解：

　　32行：student = relationship("Student",backref="my_study_record")

　　这里需要引入  from sqlalchemy.orm import sessionmaker,relationship

　　32行代码用途，可以通过Student 在表StudyRecord中直接引用表Student  那么可以通过 my_study_record 直接引用StudyRecord中的数据。这是因为他们两者存在外键关联，所以当我们执行第58行的时候，他就会根据外键去查询study_record 中的数据。