上一回扭仁，我們介紹了炊琉，常用的單查詢SQL展蒂，這里，我們介紹下苔咪，怎樣進行關(guān)聯(lián)查詢锰悼，就是join的使用
數(shù)據(jù)庫使用表信息：
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張表的關(guān)系
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
下面团赏，我們再寫一個統(tǒng)計班級人數(shù)的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] + ' 人數(shù)：' + str(rs[1]))
image.png
好了，同樣的功能舔清，我們換成子查詢練習下
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
今天暫時先到這里丝里，后面的話曲初，官網(wǎng)上例子挺全的，大家可以自行練習下杯聚，后續(xù)也會再整理
