SQLAlchemy手册（四）- 关联查询

上一回，我们介绍了，常用的单查询SQL，这里，我们介绍下，怎样进行关联查询，就是join的使用

数据库使用表信息：

t_class

t_student

t_student表存有t_class表的id，获取学生所属的班级信息

# -*- coding: utf-8 -*-



from sqlalchemy import MetaData, create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, Date, or_, and_

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

from sqlalchemy import text, func

from sqlalchemy.orm import sessionmaker

import sys



reload(sys)

sys.setdefaultencoding('utf-8')



Base = declarative_base()

engine = create_engine('postgresql://postgres:shishi@localhost:5432/postgres')

Session = sessionmaker(bind=engine)



session = Session()



class StuClass(Base):

	__tablename__ = 't_class'





	id = Column(Integer,primary_key=True)

	class_name = Column(String(10))



	def __repr__(self):

		return "<StuClass(id='%s', name='%s')>" % (self.id, self.class_name)



class Student(Base):

	__tablename__ = 't_student'



	id = Column(Integer , primary_key=True)

	stu_name = Column(String(10))

	class_id = Column(Integer , ForeignKey('t_class.id'))



	stuClass = relationship("StuClass")



	def __repr__(self):

		return "<Student(id='%s', stu_name='%s', class_id='%s', class_name='%s')>" % (self.id, self.stu_name,self.class_id,self.stuClass.class_name)





#使用filter定义2张表的关系

for stu, stuc in session.query(Student, StuClass).filter(Student.class_id == StuClass.id).all():

	print stu,stuc



#使用join需要ForeignKey

for x in session.query(Student).join(StuClass).filter(StuClass.id == 803).all():

	print x

下面，我们再写一个统计班级人数的SQL

用SQLAlchemy来写的话，是这样的

for rs in session.query(StuClass.class_name,func.count('*').label('stu_count')).join(Student).group_by(StuClass.class_name).order_by(StuClass.class_name).all():

	print('班级:' + rs[0] + ' 人数：' + str(rs[1]))

好了，同样的功能，我们换成子查询练习下

  select a.class_name,b.num from t_class a

left join (

	select class_id , count(1) num from t_student group by class_id

)b on b.class_id = a.id

order by a.class_name

那SQLAlchemy是这样滴：

 sub = session.query(Student.class_id,func.count('*').label('num')).group_by(Student.class_id).subquery()



for sc,num in session.query(StuClass,sub.c.num).outerjoin(sub, StuClass.id == sub.c.class_id).order_by(StuClass.class_name).all():

	print sc,num

今天暂时先到这里，后面的话，官网上例子挺全的，大家可以自行练习下，后续也会再整理