Python,使用,SQLAlchemy,连接,Mysql,
数据库-mysql教程
淘宝客源码程序,vscode vi 配置,天猫魔盒ubuntu,tomcat pdf 中文,怎么打开sqlite视频,discuzx3.2插件附件,微信前端框架ui,厦门爬虫户外俱乐部,win 配置php,meta标签 seo,信息发布微网站模板,网页动态客服代码,免费商业模板,html5 手机页面 加载,集装箱运输管理系统源码,jsp 登录程序lzw
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’元购商城源码,ubuntu怎么设置菜单,c 自己写爬虫,php mual,seo必备文章lzw
源码之家官网工作室,vscode查找并更改,ubuntu 双ip,tomcat主页去除,sqlite3工具介绍,怎么击败天龙座爬虫族,php55,seo优化渠道排名,html优特网站模板,学生管理网页模板源代码,joomla安装模板lzw
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.uid2016-05-10 15:02:36,476 INFO sqlalchemy.engine.base.Engine ()>>> for re in query:... print re.id... 12345>>> #查找有成绩的同学的学号好成绩>>> 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.id2016-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.id2016-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 DESC2016-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 DESC2016-05-10 15:37:33,628 INFO sqlalchemy.engine.base.Engine ()>>> for re in query2:... print re.uid,re.gre,re.cname... 1 76 wuli1 76 huaxue2 76 huaxue2 66 yuwen2 50 yingyu3 96 shuxue3 76 wuli3 60 yingyu4 76 yingyu4 76 huaxue4 76 wuli5 96 yingyu5 96 huaxue5 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.gre<=70).order_by(Grade.uid,Grade.gre.de()).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 WHERE grade.gre >> 或
#用SQL语言来查询>>> query = session.execute("select uid from grade where cid = (select id fromla where cname='yuwen')")select uid from grade where cid = (select id from cla where cname='yuwen')2016-05-10 17:12:57,395 INFO sqlalchemy.engine.base.Engine ()>>> for re in query:... print re.uid... 25>>> #把学好为2成绩为100的学生成绩改为99>>> query5 = session.query(Grade)>>> query5.filter(Grade.uid==2,Grade.gre==100).update({Grade.gre:99})2016-05-10 16:28:46,485 INFO sqlalchemy.engine.base.Engine UPDATE grade SET gre=%s WHERE grade.uid = %s AND grade.gre = %s2016-05-10 16:28:46,486 INFO sqlalchemy.engine.base.Engine (99, 2, 100)1L此时没有提交还没有正在的改变
mysql> select * from grade where gre=100;+----+------+------+------+| id | uid | cid | gre |+----+------+------+------+| 15 | 2 | 3 | 100 |+----+------+------+------+1 row in set (0.00 sec)>>> session.commit()mysql> select * from grade where gre=100;Empty set (0.00 sec)也可以这样搞>>> session.query(Grade).filter(Grade.gre==99).update({Grade.gre:50})update里面使用的是字典类型
或这样搞
#用SQL语句把学语文的成绩都改为110>>> session.execute("update grade set gre=110 where cid = (select id from cla ere cname='yuwen')") update grade set gre=110 where cid = (select id from cla where cname='yuwen')2016-05-10 17:15:59,383 INFO sqlalchemy.engine.base.Engine ()mysql> select * from grade where cid =(select id from cla where cname='yuwen');+----+------+------+------+| id | uid | cid | gre |+----+------+------+------+| 2 | 2 | 1 | 66 || 3 | 5 | 1 | 66 |+----+------+------+------+2 rows in set (0.00 sec)#提交后>>> session.commit()2016-05-10 17:16:18,223 INFO sqlalchemy.engine.base.Engine COMMIT>>> mysql> select * from grade where cid =(select id from cla where cname='yuwen');+----+------+------+------+| id | uid | cid | gre |+----+------+------+------+| 2 | 2 | 1 | 110 || 3 | 5 | 1 | 110 |+----+------+------+------+2 rows in set (0.00 sec)#删除成绩为50的成绩记录>>> session.query(Grade).filter(Grade.gre==50).delete()#删除选修英语的所有成绩记录，用SQL进行>>> session.execute( "delete from grade where cid =(select id from cla where cme='yingyu')")2016-05-10 16:55:55,472 INFO sqlalchemy.engine.base.Engine delete from grade where cid =(select id from cla where cname='yingyu')2016-05-10 16:55:55,472 INFO sqlalchemy.engine.base.Engine ()mysql> select * from grade where cid = (select id from cla where cname='yingyu') -> ;+----+------+------+------+| id | uid | cid | gre |+----+------+------+------+| 5 | 5 | 3 | 96 || 9 | 4 | 3 | 76 || 14 | 3 | 3 | 60 |+----+------+------+------+3 rows in set (0.00 sec)>>> session.commit()2016-05-10 16:56:33,075 INFO sqlalchemy.engine.base.Engine COMMIT>>> mysql> select * from grade where cid = (select id from cla where cname='yingyu') -> ;Empty set (0.00 sec)级联删除：在relationship关联时要加上passive_deletes=True外键要加上ondelete=’CASCADE’,否则sqlalchemy不能级联删除
这些是多表连接的操作
单表的操作请看http://blog.csdn.net/u011573853/article/details/51355113
一些细节会在下面进行说明（事务，加锁，编码等）
http://blog.csdn.net/u011573853/article/details/51366124