sqlalchemy-外键关联

# Author: Mr.Xue
# 2019.12.06

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

engine = create_engine("mysql+pymysql://root:[email protected]/testdb", encoding='utf-8')

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') #允许你在StudyRecord表里通过backref字段反向查出所有它在Student表中的关联项

    def __repr__(self):
        return '<%s day:%s status:%s>' % (self.id, self.day, self.status)

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

Session_class = sessionmaker(bind=engine) # 创建与数据库的会话session class
Session = Session_class() # 生成session实例 # cursor

''' s1 = Student(name='Xue', register_date='2014-05-21')
s2 = Student(name='Jack', register_date='2014-03-21')
s3 = Student(name='Rain', register_date='2014-02-21')
s4 = Student(name='Eric', register_date='2014-01-21')

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]) '''

stu_obj = Session.query(Student).filter(Student.name=='Xue').first()
print(stu_obj.my_study_record)

Session.commit()