python SQLAchemy外键关联

join

1、利用filter

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
# 连接方式 mysql+pymysql 用户名 root 密码 123456 主机 localhost 库 testdb encoding 字符集
engine = create_engine("mysql+pymysql://root:123456@localhost/testdb",encoding='utf-8', echo=True)

Base = declarative_base()  # 生成orm基类

class User(Base):
    __tablename__ = 'user'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

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

class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_data = Column(DATE,nullable=False)
    gender=Column(String(32),nullable=False)

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

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


Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  # 生成session实例 cursor
print(Session.query(User,Student).filter(User.id==Student.id).all())

 2 、join

这个要求A表和B表必须有FOREIGN KEY的关系才可以。

Session.query(User).join(Student).all()

Session.query(User).join(Student,isouter=True).all()

 ORM实现外键约束

用户表、课程表、用户与课程中间表

1、首先把用户和用户中间表管理起来

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey,DATE
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
# 连接方式 mysql+pymysql 用户名 root 密码 123456 主机 localhost 库 testdb encoding 字符集
engine = create_engine("mysql+pymysql://root:@localhost/test",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'))

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

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

 以上先创建两张表

2、添加学员

Session_class = sessionmaker(bind=engine)
Session = Session_class()  # 生成session实例 cursor

s1 = Student(name='Alex',register_date='2017-10-24')
s2 = Student(name='jack',register_date='2017-10-24')
s3 = Student(name='rain',register_date='2017-10-24')
s4 = Student(name='Lily',register_date='2017-10-24')
s5 = Student(name='Lucy',register_date='2017-10-24')

study_obj1 = StudyRecord(day=1,status='yes',stu_id=1)
study_obj2 = StudyRecord(day=2,status='yes',stu_id=1)
study_obj3 = StudyRecord(day=3,status='yes',stu_id=1)
study_obj4 = StudyRecord(day=1,status='yes',stu_id=2)
study_obj5 = StudyRecord(day=2,status='yes',stu_id=2)

Session.add_all([s1,s2,s3,s4,s5,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5])


Session.commit()  # 现此才统一提交，创建数据

3、 查询

先看一个比较牛的东西：反查。通过设置的一个字段反向查询关联的表。关联表也可以根据这个字段查询当前表。双向反查。

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_classes') #指定关联的表 Student， backref
    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)

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

Session_class = sessionmaker(bind=engine)
Session = Session_class()  # 生成session实例 cursor

Stu_obj = Session.query(Student).filter(Student.name=='alex').first()
print(Stu_obj.my_classes)

 

首先 stu_id为ForeignKey，relationship()函数将告知ORM通过StudyRecord.student 关联到类Student。relationship()会使用两个表的外键约束来判定这种链接的性质。backref提供一种反向的查询。Student通过 Student.my_classes反向查询到StudyRecord。