Python_sqlalchemy实现数据库的外键关联

# Author : Xuefeng

# 外键关联

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


engine = create_engine("mysql+pymysql://Jim:123456@localhost/testdb",
                       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 Study_Record(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_sudy_record")

    def __repr__(self):
        return "< %s day: %s  status: %s >" % (self.student.name,self.day,self.status)

Base.metadata.create_all(bind=engine)

Session_class = sessionmaker(bind=engine)
Session = Session_class()

# , register_date= '2020-03-29'
# noinspection PyTypeChecker
s1 = Student(name="Jim", register_date='2020-03-29')
# noinspection PyTypeChecker
s2 = Student(name="Tom", register_date="2020-01-29")
# noinspection PyTypeChecker
s3 = Student(name="Mary", register_date="2020-10-29")

S_r1 = Study_Record(day=1, status="Yes", stu_id=1)
S_r2 = Study_Record(day=2, status="No", stu_id=1)
S_r3 = Study_Record(day=3, status="Yes", stu_id=1)
S_r4 = Study_Record(day=1, status="Yes", stu_id=2)
# Session.add_all([s1,s2,s3])
# Session.add_all([S_r1,S_r2,S_r3,S_r4])

Session.commit()
# stu_obj = Session.query(Student).filter(Student.name=="Jim").first()
stu_obj = Session.query(Study_Record).filter(Study_Record.stu_id==1).all()
print(stu_obj)