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()Student通过 Student
