Python中使用SQLAlchemy连接Mysql数据库2（多表连接操作）

http://blog.csdn.net/u011573853/article/details/51355113 在上面我们已经说了单表的一些操作，现在我们说一下多表的连接 from sqlalchemy import distinct from sqlalchemy.orm import aliased Astu = aliased(Stu, 'Astu' ) Acla = aliased(Cla, 'Acla'

http://blog.csdn.net/u011573853/article/details/51355113
在上面我们已经说了单表的一些操作，现在我们说一下多表的连接

>>> from sqlalchemy import distinct
>>> from sqlalchemy.orm import aliased
>>> Astu = aliased(Stu,'Astu')
>>> Acla = aliased(Cla,'Acla')
>>> Agrade = aliased(Grade,'Agrade')

在这里先用函数aliased（）给表起一个别名以备使用

#查询所有有成绩的学号
>>> query = session.query(Stu).join(Grade,Stu.id==Grade.uid).all()
SELECT stu.id AS stu_id, stu.name AS stu_name 
FROM stu INNER JOIN grade ON stu.id = grade.uid
2016-05-10 15:02:36,476 INFO sqlalchemy.engine.base.Engine ()
>>> for re in query:
...     print re.id
... 
1
2
3
4
5
>>> 

#查找有成绩的同学的学号好成绩
>>> print session.query(Grade.uid,Grade.gre).join(Stu,Grade.uid==Stu.id).all()
SELECT grade.uid AS grade_uid, grade.gre AS grade_gre 
FROM grade INNER JOIN stu ON grade.uid = stu.id
2016-05-10 15:15:22,208 INFO sqlalchemy.engine.base.Engine ()
[(1L, 76L), (1L, 76L), (2L, 66L), (2L, 76L), (2L, 50L), (3L, 96L), (3L, 76L), (3L, 60L), (4L, 76L), (4L, 76L), (4L, 76L), (5L, 66L), (5L, 96L), (5L, 96L)]
>>> 

#查找有成绩同学的学好成绩和课程名
>>>print session.query(Grade.uid,Grade.gre,Cla.cname).join(Stu,Grade.uid==Stuid).join(Cla,Grade.cid==Cla.id).all()
 SELECT grade.uid AS grade_uid, grade.gre AS grade_gre, cla.cname AS cla_cname 
FROM grade INNER JOIN stu ON grade.uid = stu.id INNER JOIN cla ON grade.cid = cla.id
2016-05-10 15:21:29,402 INFO sqlalchemy.engine.base.Engine ()
[(2L, 66L, 'yuwen'), (5L, 66L, 'yuwen'), (3L, 96L, 'shuxue'), (2L, 50L, 'yingyu'), (3L, 60L, 'yingyu'), (4L, 76L, 'yingyu'), (5L, 96L, 'yingyu'), (1L, 76L, 'wuli'), (3L, 76L, 'wuli'), (4L, 76L, 'wuli'), (1L, 76L, 'huaxue'), (2L, 76L, 'huaxue'), (4L, 76L, 'huaxue'), (5L, 96L, 'huaxue')]
>>> 

#查找有成绩同学的学好成绩和课程名，并进行排序
>>> print session.query(Grade.uid,Grade.gre,Cla.cname).join(Stu,Grade.uid==Stud).join(Cla,Grade.cid==Cla.id).order_by(Grade.uid,Grade.gre.desc()).all()
SELECT grade.uid AS grade_uid, grade.gre AS grade_gre, cla.cname AS cla_cname 
FROM grade INNER JOIN stu ON grade.uid = stu.id INNER JOIN cla ON grade.cid = cla.id 
ORDER BY grade.uid, grade.gre DESC
2016-05-10 15:34:17,902 INFO sqlalchemy.engine.base.Engine ()
[(1L, 76L, 'wuli'), (1L, 76L, 'huaxue'), (2L, 76L, 'huaxue'), (2L, 66L, 'yuwen'), (2L, 50L, 'yingyu'), (3L, 96L, 'shuxue'), (3L, 76L, 'wuli'), (3L, 60L, 'yingyu'), (4L, 76L, 'yingyu'), (4L, 76L, 'huaxue'), (4L, 76L, 'wuli'), (5L, 96L, 'yingyu'), (5L, 96L, 'huaxue'), (5L, 66L, 'yuwen')]
>>> 

或

>>> query2=session.query(Grade.uid,Grade.gre,Cla.cname).join(Stu,Grade.uid==Stid).join(Cla,Grade.cid==Cla.id).order_by(Grade.uid,Grade.gre.desc()).all()
SELECT grade.uid AS grade_uid, grade.gre AS grade_gre, cla.cname AS cla_cname 
FROM grade INNER JOIN stu ON grade.uid = stu.id INNER JOIN cla ON grade.cid = cla.id ORDER BY grade.uid, grade.gre DESC
2016-05-10 15:37:33,628 INFO sqlalchemy.engine.base.Engine ()
>>> for re in query2:
...     print re.uid,re.gre,re.cname
... 
1 76 wuli
1 76 huaxue
2 76 huaxue
2 66 yuwen
2 50 yingyu
3 96 shuxue
3 76 wuli
3 60 yingyu
4 76 yingyu
4 76 huaxue
4 76 wuli
5 96 yingyu
5 96 huaxue
5 66 yuwen
>>> 

只需要把你要查的字段写在query()中就好了

#查找成绩小于等于70同学的学好成绩和课程名，并进行排序
>>>print  session.query(Grade.uid,Grade.gre,Cla.cname).join(Stu,Grade.uid==Stu.id).jn(Cla,Grade.cid==Cla.id).filter(Grade.gre70