当前位置：Gxlcms > 数据库问题 > Python学习笔记整理总结【ORM（SQLAlchemy）】

Python学习笔记整理总结【ORM（SQLAlchemy）】

时间：2021-07-01 10:21:17 帮助过：23人阅读


SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

优点：
隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
ORM使我们构造固化数据结构变得简单易行。
缺点：
无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。

技术分享图片

第一个阶段：将SQLAlchemy的对象换成可执行的sql语句（使用者通过ORM对象提交命令；将命令交给SQLAlchemy Core（Schema/Types SQL Expression Language）转换成SQL）
第二个阶段：将sql语句交给数据库执行（匹配使用者事先配置好的egine；egine从连接池中取出一个链接；基于该链接通过Dialect调用DB API，将SQL转交给它去执行）

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作

  1. <span style="color: #008000">#</span><span style="color: #008000">1、MySQL-Python</span>
  2.     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
  3. <span style="color: #008000">#</span><span style="color: #008000">2、pymysql</span>
  4.     mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options><span style="color: #000000">] 
  5. </span><span style="color: #008000">#</span><span style="color: #008000">3、MySQL-Connector</span>
  6.     mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
  7. <span style="color: #008000">#</span><span style="color: #008000">4、cx_Oracle</span>
  8.     oracle+cx_oracle://user:<span style="color: #0000ff">pass</span>@host:port/dbname[?key=value&key=<span style="color: #000000">value...]
  9. 安装： 
  10. pip install SQLAlchemy
  11. pip install pymysql 
  12. </span><span style="color: #008000">#</span><span style="color: #008000">由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互</span>

 

二、sqlalchemy基本使用
注：
　　#类 ==>表
　　#对象 ==>表中的一行记录

<1>基本语法（增/删/改/查/回滚）

  1. <span style="color: #008000">#</span><span style="color: #008000">###用到的表结构1####</span>
  2. <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  3. </span><span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  4. </span><span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column,Integer,String,ForeignKey
  5. </span><span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker
  6. engine </span>= create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:12344321@localhost/solo</span><span style="color: #800000">"</span><span style="color: #000000">,
  7.                        </span><span style="color: #008000">#</span><span style="color: #008000">mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname></span>
  8.                                     encoding=<span style="color: #800000">‘</span><span style="color: #800000">utf-8</span><span style="color: #800000">‘</span>, echo=True) <span style="color: #008000">#</span><span style="color: #008000">echo=True 打印程序运行详细信息</span>
  9. Base=declarative_base()   <span style="color: #008000">#</span><span style="color: #008000">生成orm基类</span>
  10. <span style="color: #008000">#</span><span style="color: #008000">多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工(只有某些公司才把普通员工当成葫芦娃，身兼数职)</span><span style="color: #008000">
  11. #</span><span style="color: #008000">类   ==>表</span><span style="color: #008000">
  12. #</span><span style="color: #008000">对象 ==>表中的一行记录</span>
  13. <span style="color: #0000ff">class</span><span style="color: #000000"> Dep(Base):
  14.     </span><span style="color: #800080">__tablename__</span>=<span style="color: #800000">‘</span><span style="color: #800000">dep</span><span style="color: #800000">‘</span><span style="color: #000000">
  15.     id</span>=Column(Integer,primary_key=True,autoincrement=<span style="color: #000000">True)
  16.     dname</span>=Column(String(64),nullable=False,index=<span style="color: #000000">True)
  17. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Emp(Base):
  18.     </span><span style="color: #800080">__tablename__</span>=<span style="color: #800000">‘</span><span style="color: #800000">emp</span><span style="color: #800000">‘</span>          <span style="color: #008000">#</span><span style="color: #008000">表名</span>
  19.     id=Column(Integer,primary_key=True,autoincrement=<span style="color: #000000">True)
  20.     ename</span>=Column(String(32),nullable=False,index=<span style="color: #000000">True)
  21.     dep_id</span>=Column(Integer,ForeignKey(<span style="color: #800000">‘</span><span style="color: #800000">dep.id</span><span style="color: #800000">‘</span><span style="color: #000000">))
  22. </span><span style="color: #0000ff">def</span><span style="color: #000000"> init_db():
  23.     Base.metadata.create_all(egine)
  24. </span><span style="color: #0000ff">def</span><span style="color: #000000"> drop_db():
  25.     Base.metadata.drop_all(egine)
  26. drop_db()
  27. init_db()
  28. Session</span>=sessionmaker(bind=egine) <span style="color: #008000">#</span><span style="color: #008000">Session_class现在不是实例，而是类</span>
  29. session=Session()                <span style="color: #008000">#</span><span style="color: #008000">生成Session实例</span>

①增

  1. <span style="color: #008000">#</span><span style="color: #008000">增</span>
  2. row_obj=Dep(dname=<span style="color: #800000">‘</span><span style="color: #800000">销售</span><span style="color: #800000">‘</span>)  <span style="color: #008000">#</span><span style="color: #008000">生成你要创建的数据对象//按关键字传参,无需指定id,因其是自增长的</span>
  3. session.add(row_obj)       <span style="color: #008000">#</span><span style="color: #008000">把要创建的数据对象添加到这个session里， 一会统一创建</span>
  4. <span style="color: #000000">session.add_all([
  5.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">技术</span><span style="color: #800000">‘</span><span style="color: #000000">),
  6.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">运营</span><span style="color: #800000">‘</span><span style="color: #000000">),
  7.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">人事</span><span style="color: #800000">‘</span><span style="color: #000000">),
  8. ])
  9. session.commit()    </span><span style="color: #008000">#</span><span style="color: #008000">提交，生成表</span>

②删

  1. <span style="color: #008000">#</span><span style="color: #008000">删</span>
  2. session.query(Dep).filter(Dep.id > 3<span style="color: #000000">).delete()
  3. session.commit()</span>

③改

  1. <span style="color: #008000">#</span><span style="color: #008000">改</span>
  2. session.query(Dep).filter(Dep.id > 0).update({<span style="color: #800000">‘</span><span style="color: #800000">dname</span><span style="color: #800000">‘</span>:<span style="color: #800000">‘</span><span style="color: #800000">翔哥</span><span style="color: #800000">‘</span><span style="color: #000000">})
  3. session.query(Dep).filter(Dep.id </span>> 0).update({<span style="color: #800000">‘</span><span style="color: #800000">dname</span><span style="color: #800000">‘</span>:Dep.dname+<span style="color: #800000">‘</span><span style="color: #800000">_SB</span><span style="color: #800000">‘</span>},synchronize_session=<span style="color: #000000">False)
  4. session.query(Dep).filter(Dep.id </span>> 0).update({<span style="color: #800000">‘</span><span style="color: #800000">id</span><span style="color: #800000">‘</span>:Dep.id*100},synchronize_session=<span style="color: #800000">‘</span><span style="color: #800000">evaluate</span><span style="color: #800000">‘</span><span style="color: #000000">)
  5. session.commit()</span>

④查

  1. <span style="color: #008000">#</span><span style="color: #008000">查所有,取所有字段</span>
  2. res=session.query(Dep).all() <span style="color: #008000">#</span><span style="color: #008000">for row in res:print(row.id,row.dname)</span>
  3. <span style="color: #008000">#</span><span style="color: #008000">查所有,取指定字段</span>
  4. res=session.query(Dep.dname).order_by(Dep.id).all() <span style="color: #008000">#</span><span style="color: #008000">for row in res:print(row.dname)</span>
  5. <span style="color: #000000">
  6. res</span>=<span style="color: #000000">session.query(Dep.dname).first()
  7. </span><span style="color: #0000ff">print</span>(res) <span style="color: #008000">#</span><span style="color: #008000"> (‘翔哥_SB‘,)</span>
  8. <span style="color: #008000">#</span><span style="color: #008000">过滤查</span>
  9. res=session.query(Dep).filter(Dep.id > 1,Dep.id <1000) <span style="color: #008000">#</span><span style="color: #008000">逗号分隔,默认为and</span>
  10. <span style="color: #0000ff">print</span>([(row.id,row.dname) <span style="color: #0000ff">for</span> row <span style="color: #0000ff">in</span> res])

⑤回滚

  1. <span style="color: #008000">#</span><span style="color: #008000"> session.add xxoo       //数据添加</span><span style="color: #008000">
  2. #</span><span style="color: #008000"> Session.rollback()     //回滚</span><span style="color: #008000">
  3. #</span><span style="color: #008000"> Session.query xxoo     //刚刚添加的数据不见了（也就是滚到了原来的状态）</span>

<2>更多的查询操作

  1. <span style="color: #008000">#</span><span style="color: #008000">##用到的表结构2###</span>
  2. <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  3. </span><span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  4. </span><span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column,Integer,String,ForeignKey
  5. </span><span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker
  6. engine </span>= create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:12344321@localhost/solo</span><span style="color: #800000">"</span><span style="color: #000000">,
  7.                        </span><span style="color: #008000">#</span><span style="color: #008000">mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname></span>
  8.                                     encoding=<span style="color: #800000">‘</span><span style="color: #800000">utf-8</span><span style="color: #800000">‘</span>, echo=True) <span style="color: #008000">#</span><span style="color: #008000">echo=True 打印程序运行详细信息</span>
  9. Base=declarative_base()   <span style="color: #008000">#</span><span style="color: #008000">生成orm基类</span>
  10. <span style="color: #008000">#</span><span style="color: #008000">多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工(只有某些公司才把普通员工当成葫芦娃，身兼数职)</span><span style="color: #008000">
  11. #</span><span style="color: #008000">类   ==>表</span><span style="color: #008000">
  12. #</span><span style="color: #008000">对象 ==>表中的一行记录</span>
  13. <span style="color: #0000ff">class</span><span style="color: #000000"> Dep(Base):
  14.     </span><span style="color: #800080">__tablename__</span>=<span style="color: #800000">‘</span><span style="color: #800000">dep</span><span style="color: #800000">‘</span><span style="color: #000000">
  15.     id</span>=Column(Integer,primary_key=True,autoincrement=<span style="color: #000000">True)
  16.     dname</span>=Column(String(64),nullable=False,index=<span style="color: #000000">True)
  17. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Emp(Base):
  18.     </span><span style="color: #800080">__tablename__</span>=<span style="color: #800000">‘</span><span style="color: #800000">emp</span><span style="color: #800000">‘</span>          <span style="color: #008000">#</span><span style="color: #008000">表名</span>
  19.     id=Column(Integer,primary_key=True,autoincrement=<span style="color: #000000">True)
  20.     ename</span>=Column(String(32),nullable=False,index=<span style="color: #000000">True)
  21.     dep_id</span>=Column(Integer,ForeignKey(<span style="color: #800000">‘</span><span style="color: #800000">dep.id</span><span style="color: #800000">‘</span><span style="color: #000000">))
  22. </span><span style="color: #0000ff">def</span><span style="color: #000000"> init_db():
  23.     Base.metadata.create_all(egine)
  24. </span><span style="color: #0000ff">def</span><span style="color: #000000"> drop_db():
  25.     Base.metadata.drop_all(egine)
  26. drop_db()
  27. init_db()
  28. Session</span>=sessionmaker(bind=egine) <span style="color: #008000">#</span><span style="color: #008000">Session_class现在不是实例，而是类</span>
  29. session=Session()                <span style="color: #008000">#</span><span style="color: #008000">生成Session实例</span>
  30. <span style="color: #008000">#</span><span style="color: #008000"> 准备数据</span>
  31. <span style="color: #000000">session.add_all([
  32.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">技术</span><span style="color: #800000">‘</span><span style="color: #000000">),
  33.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">销售</span><span style="color: #800000">‘</span><span style="color: #000000">),
  34.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">运营</span><span style="color: #800000">‘</span><span style="color: #000000">),
  35.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">人事</span><span style="color: #800000">‘</span><span style="color: #000000">),
  36. ])
  37. session.add_all([
  38.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span>,dep_id=1<span style="color: #000000">),
  39.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李杰</span><span style="color: #800000">‘</span>,dep_id=1<span style="color: #000000">),
  40.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">武配齐</span><span style="color: #800000">‘</span>,dep_id=1<span style="color: #000000">),
  41.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李伟</span><span style="color: #800000">‘</span>,dep_id=2<span style="color: #000000">),
  42.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李钢弹</span><span style="color: #800000">‘</span>,dep_id=3<span style="color: #000000">),
  43.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">张二丫</span><span style="color: #800000">‘</span>,dep_id=4<span style="color: #000000">),
  44.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李坦克</span><span style="color: #800000">‘</span>,dep_id=2<span style="color: #000000">),
  45.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">王大炮</span><span style="color: #800000">‘</span>,dep_id=4<span style="color: #000000">),
  46.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">牛榴弹</span><span style="color: #800000">‘</span>,dep_id=3<span style="color: #000000">)
  47. ])
  48. session.commit()</span>

①条件、通配符、limit、排序、分组、连表、组合

  1. <span style="color: #008000">#</span><span style="color: #008000">一、条件</span>
  2. sql=session.query(Emp).filter_by(ename=<span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span>) <span style="color: #008000">#</span><span style="color: #008000">filter_by只能传参数:什么等于什么</span>
  3. res=sql.all() <span style="color: #008000">#</span><span style="color: #008000">sql语句的执行结果</span>
  4. <span style="color: #000000">
  5. res</span>=session.query(Emp).filter(Emp.id>0,Emp.ename == <span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span>).all() <span style="color: #008000">#</span><span style="color: #008000">filter内传的是表达式,逗号分隔,默认为and,</span>
  6. res=session.query(Emp).filter(Emp.id.between(1,3),Emp.ename == <span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span><span style="color: #000000">).all()
  7. res</span>=session.query(Emp).filter(Emp.id.in_([1,3,99,101]),Emp.ename == <span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span><span style="color: #000000">).all()
  8. res</span>=session.query(Emp).filter(~Emp.id.in_([1,3,99,101]),Emp.ename == <span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span>) <span style="color: #008000">#</span><span style="color: #008000">~代表取反,转换成sql就是关键字not</span>
  9. <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> and_,or_
  10. res</span>=session.query(Emp).filter(and_(Emp.id > 0,Emp.ename==<span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  11. res</span>=session.query(Emp).filter(or_(Emp.id < 2,Emp.ename==<span style="color: #800000">‘</span><span style="color: #800000">功夫熊猫</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  12. res</span>=<span style="color: #000000">session.query(Emp).filter(
  13.     or_(
  14.         Emp.dep_id </span>== 3<span style="color: #000000">,
  15.         and_(Emp.id </span>> 1,Emp.ename==<span style="color: #800000">‘</span><span style="color: #800000">功夫熊猫</span><span style="color: #800000">‘</span><span style="color: #000000">),
  16.         Emp.ename </span>!= <span style="color: #800000">‘‘</span><span style="color: #000000">
  17.     )
  18. ).all()
  19. </span><span style="color: #008000">#</span><span style="color: #008000">二、通配符</span>
  20. res=session.query(Emp).filter(Emp.ename.like(<span style="color: #800000">‘</span><span style="color: #800000">%芦_%</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  21. res</span>=session.query(Emp).filter(~Emp.ename.like(<span style="color: #800000">‘</span><span style="color: #800000">%芦_%</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  22. </span><span style="color: #008000">#</span><span style="color: #008000">三、limit</span>
  23. res=session.query(Emp)[0:5:2<span style="color: #000000">]
  24. </span><span style="color: #008000">#</span><span style="color: #008000">四、排序</span>
  25. res=<span style="color: #000000">session.query(Emp).order_by(Emp.dep_id.desc()).all()
  26. res</span>=<span style="color: #000000">session.query(Emp).order_by(Emp.dep_id.desc(),Emp.id.asc()).all()
  27. </span><span style="color: #008000">#</span><span style="color: #008000">五、分组</span>
  28. <span style="color: #0000ff">from</span> sqlalchemy.sql <span style="color: #0000ff">import</span><span style="color: #000000"> func
  29. res</span>=<span style="color: #000000">session.query(Emp.dep_id).group_by(Emp.dep_id).all()
  30. res</span>=<span style="color: #000000">session.query(
  31.     func.max(Emp.dep_id),
  32.     func.min(Emp.dep_id),
  33.     func.sum(Emp.dep_id),
  34.     func.avg(Emp.dep_id),
  35.     func.count(Emp.dep_id),
  36. ).group_by(Emp.dep_id).all()
  37. res</span>=<span style="color: #000000">session.query(
  38.     Emp.dep_id,
  39.     func.count(</span>1<span style="color: #000000">),
  40. ).group_by(Emp.dep_id).having(func.count(</span>1) > 2<span style="color: #000000">).all()
  41. </span><span style="color: #008000">#</span><span style="color: #008000">六、连表</span><span style="color: #008000">
  42. #</span><span style="color: #008000">笛卡尔积</span>
  43. res=session.query(Emp,Dep).all() <span style="color: #008000">#</span><span style="color: #008000">select * from emp,dep;</span>
  44. <span style="color: #008000">#</span><span style="color: #008000">where条件</span>
  45. res=session.query(Emp,Dep).filter(Emp.dep_id==<span style="color: #000000">Dep.id).all()
  46. </span><span style="color: #008000">#</span><span style="color: #008000"> for row in res:</span><span style="color: #008000">
  47. #</span><span style="color: #008000">     emp_tb=row[0]</span><span style="color: #008000">
  48. #</span><span style="color: #008000">     dep_tb=row[1]</span><span style="color: #008000">
  49. #</span><span style="color: #008000">     print(emp_tb.id,emp_tb.ename,dep_tb.id,dep_tb.dname)</span>
  50. <span style="color: #008000">#</span><span style="color: #008000">内连接</span>
  51. res=<span style="color: #000000">session.query(Emp).join(Dep)
  52. </span><span style="color: #008000">#</span><span style="color: #008000">join默认为内连接,SQLAlchemy会自动帮我们通过foreign key字段去找关联关系</span><span style="color: #008000">
  53. #</span><span style="color: #008000">但是上述查询的结果均为Emp表的字段,这样链表还有毛线意义,于是我们修改为</span>
  54. res=<span style="color: #000000">session.query(Emp.id,Emp.ename,Emp.dep_id,Dep.dname).join(Dep).all()
  55. </span><span style="color: #008000">#</span><span style="color: #008000">左连接:isouter=True</span>
  56. res=session.query(Emp.id,Emp.ename,Emp.dep_id,Dep.dname).join(Dep,isouter=<span style="color: #000000">True).all()
  57. </span><span style="color: #008000">#</span><span style="color: #008000">右连接:同左连接,只是把两个表的位置换一下</span>
  58. <span style="color: #008000">#</span><span style="color: #008000">七、组合</span>
  59. q1=session.query(Emp.id,Emp.ename).filter(Emp.id > 0,Emp.id < 5<span style="color: #000000">)
  60. q2</span>=<span style="color: #000000">session.query(Emp.id,Emp.ename).filter(
  61.     or_(
  62.         Emp.ename.like(</span><span style="color: #800000">‘</span><span style="color: #800000">%芦%</span><span style="color: #800000">‘</span><span style="color: #000000">),
  63.         Emp.ename.like(</span><span style="color: #800000">‘</span><span style="color: #800000">%伟%</span><span style="color: #800000">‘</span><span style="color: #000000">),
  64.     )
  65. )
  66. res1</span>=q1.union(q2) <span style="color: #008000">#</span><span style="color: #008000">组合+去重</span>
  67. res2=q1.union_all(q2) <span style="color: #008000">#</span><span style="color: #008000">组合,不去重</span>
  68. <span style="color: #0000ff">print</span>([i.ename <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> q1.all()]) <span style="color: #008000">#</span><span style="color: #008000">[‘葫芦娃‘, ‘李杰‘, ‘武配齐‘, ‘李伟‘]</span>
  69. <span style="color: #0000ff">print</span>([i.ename <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> q2.all()]) <span style="color: #008000">#</span><span style="color: #008000">[‘葫芦娃‘, ‘李伟‘]</span>
  70. <span style="color: #0000ff">print</span>([i.ename <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> res1.all()]) <span style="color: #008000">#</span><span style="color: #008000">[‘葫芦娃‘, ‘李杰‘, ‘武配齐‘, ‘李伟‘]</span>
  71. <span style="color: #0000ff">print</span>([i.ename <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> res2.all()]) <span style="color: #008000">#</span><span style="color: #008000">[‘葫芦娃‘, ‘李杰‘, ‘武配齐‘, ‘李伟‘, ‘李伟‘, ‘葫芦娃‘]</span>

②子查询
注意：子查询的sql必须用括号包起来,尤其在形式三中需要注意这一点

  1. <span style="color: #000000">形式一:子查询当做一张表来用,调用subquery()
  2. </span><span style="color: #008000">#</span><span style="color: #008000">示例:查出id大于2的员工,当做子查询的表使用</span>
  3. <span style="color: #008000">#</span><span style="color: #008000">原生SQL:</span><span style="color: #008000">
  4. #</span><span style="color: #008000"> select * from (select * from emp where id > 2);</span>
  5. <span style="color: #008000">#</span><span style="color: #008000">ORM:</span>
  6. res=<span style="color: #000000">session.query(
  7.     session.query(Emp).filter(Emp.id </span>> 8<span style="color: #000000">).subquery()
  8. ).all()
  9. 形式二:子查询当做in的范围用,调用in_
  10. </span><span style="color: #008000">#</span><span style="color: #008000">示例:#查出销售部门的员工姓名</span>
  11. <span style="color: #008000">#</span><span style="color: #008000">原生SQL:</span><span style="color: #008000">
  12. #</span><span style="color: #008000"> select ename from emp where dep_id in (select id from dep where dname=‘销售‘);</span>
  13. <span style="color: #008000">#</span><span style="color: #008000">ORM:</span>
  14. res=<span style="color: #000000">session.query(Emp.ename).filter(Emp.dep_id.in_(
  15.     session.query(Dep.id).filter_by(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">销售</span><span style="color: #800000">‘</span>), <span style="color: #008000">#</span><span style="color: #008000">传的是参数</span>
  16.     <span style="color: #008000">#</span><span style="color: #008000"> session.query(Dep.id).filter(Dep.dname==‘销售‘) #传的是表达式</span>
  17. <span style="color: #000000">)).all()
  18. 形式三:子查询当做select后的字段,调用as_scalar()
  19. </span><span style="color: #008000">#</span><span style="color: #008000">示例:查询所有的员工姓名与部门名</span>
  20. <span style="color: #008000">#</span><span style="color: #008000">原生SQL:</span><span style="color: #008000">
  21. #</span><span style="color: #008000"> select ename as 员工姓名,(select dname from dep where id = emp.dep_id) as 部门名 from emp;</span>
  22. <span style="color: #008000">#</span><span style="color: #008000">ORM:</span>
  23. sub_sql=session.query(Dep.dname).filter(Dep.id==Emp.dep_id) <span style="color: #008000">#</span><span style="color: #008000">SELECT dep.dname FROM dep, emp WHERE dep.id = emp.dep_id</span>
  24. sub_sql.as_scalar() <span style="color: #008000">#</span><span style="color: #008000">as_scalar的功能就是把上面的sub_sql加上了括号</span>
  25. <span style="color: #000000">
  26. res</span>=session.query(Emp.ename,sub_sql.as_scalar()).all()

③正查、反查

  1. <span style="color: #008000">#</span><span style="color: #008000">##用到的表结构3###</span>
  2. <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  3. </span><span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  4. </span><span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column,Integer,String,ForeignKey
  5. </span><span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker
  6. engine </span>= create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:12344321@localhost/solo</span><span style="color: #800000">"</span><span style="color: #000000">,
  7.                        </span><span style="color: #008000">#</span><span style="color: #008000">mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname></span>
  8.                                     encoding=<span style="color: #800000">‘</span><span style="color: #800000">utf-8</span><span style="color: #800000">‘</span>, echo=True) <span style="color: #008000">#</span><span style="color: #008000">echo=True 打印程序运行详细信息</span>
  9. Base=declarative_base()   <span style="color: #008000">#</span><span style="color: #008000">生成orm基类</span>
  10. <span style="color: #008000">#</span><span style="color: #008000">多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工(只有某些公司才把普通员工当成葫芦娃，身兼数职)</span><span style="color: #008000">
  11. #</span><span style="color: #008000">类   ==>表</span><span style="color: #008000">
  12. #</span><span style="color: #008000">对象 ==>表中的一行记录</span>
  13. <span style="color: #0000ff">class</span><span style="color: #000000"> Dep(Base):
  14.     </span><span style="color: #800080">__tablename__</span>=<span style="color: #800000">‘</span><span style="color: #800000">dep</span><span style="color: #800000">‘</span><span style="color: #000000">
  15.     id</span>=Column(Integer,primary_key=True,autoincrement=<span style="color: #000000">True)
  16.     dname</span>=Column(String(64),nullable=False,index=<span style="color: #000000">True)
  17. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Emp(Base):
  18.     </span><span style="color: #800080">__tablename__</span>=<span style="color: #800000">‘</span><span style="color: #800000">emp</span><span style="color: #800000">‘</span><span style="color: #000000">
  19.     id</span>=Column(Integer,primary_key=True,autoincrement=<span style="color: #000000">True)
  20.     ename</span>=Column(String(32),nullable=False,index=<span style="color: #000000">True)
  21.     dep_id</span>=Column(Integer,ForeignKey(<span style="color: #800000">‘</span><span style="color: #800000">dep.id</span><span style="color: #800000">‘</span><span style="color: #000000">))
  22.     </span><span style="color: #008000">#</span><span style="color: #008000">在ForeignKey所在的类内添加relationship的字段,注意:</span>
  23.     <span style="color: #008000">#</span><span style="color: #008000">1:Dep是类名</span>
  24.     <span style="color: #008000">#</span><span style="color: #008000">2:depart字段不会再数据库表中生成字段</span>
  25.     <span style="color: #008000">#</span><span style="color: #008000">3:depart用于Emp表查询Dep表(正向查询),而xxoo用于Dep表查询Emp表(反向查询),</span>
  26.     depart=relationship(<span style="color: #800000">‘</span><span style="color: #800000">Dep</span><span style="color: #800000">‘</span>,backref=<span style="color: #800000">‘</span><span style="color: #800000">xxoo</span><span style="color: #800000">‘</span><span style="color: #000000">) 
  27. </span><span style="color: #0000ff">def</span><span style="color: #000000"> init_db():
  28.     Base.metadata.create_all(egine)
  29. </span><span style="color: #0000ff">def</span><span style="color: #000000"> drop_db():
  30.     Base.metadata.drop_all(egine)
  31. drop_db()
  32. init_db()
  33. Session</span>=sessionmaker(bind=egine) <span style="color: #008000">#</span><span style="color: #008000">Session_class现在不是实例，而是类</span>
  34. session=Session()                <span style="color: #008000">#</span><span style="color: #008000">生成Session实例</span>
  35. <span style="color: #008000">#</span><span style="color: #008000"> 准备数据</span>
  36. <span style="color: #000000">session.add_all([
  37.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">技术</span><span style="color: #800000">‘</span><span style="color: #000000">),
  38.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">销售</span><span style="color: #800000">‘</span><span style="color: #000000">),
  39.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">运营</span><span style="color: #800000">‘</span><span style="color: #000000">),
  40.     Dep(dname</span>=<span style="color: #800000">‘</span><span style="color: #800000">人事</span><span style="color: #800000">‘</span><span style="color: #000000">),
  41. ])
  42. session.add_all([
  43.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">葫芦娃</span><span style="color: #800000">‘</span>,dep_id=1<span style="color: #000000">),
  44.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李杰</span><span style="color: #800000">‘</span>,dep_id=1<span style="color: #000000">),
  45.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">武配齐</span><span style="color: #800000">‘</span>,dep_id=1<span style="color: #000000">),
  46.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李伟</span><span style="color: #800000">‘</span>,dep_id=2<span style="color: #000000">),
  47.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李钢弹</span><span style="color: #800000">‘</span>,dep_id=3<span style="color: #000000">),
  48.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">张二丫</span><span style="color: #800000">‘</span>,dep_id=4<span style="color: #000000">),
  49.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">李坦克</span><span style="color: #800000">‘</span>,dep_id=2<span style="color: #000000">),
  50.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">王大炮</span><span style="color: #800000">‘</span>,dep_id=4<span style="color: #000000">),
  51.     Emp(ename</span>=<span style="color: #800000">‘</span><span style="color: #800000">牛榴弹</span><span style="color: #800000">‘</span>,dep_id=3<span style="color: #000000">)
  52. ])
  53. session.commit()</span>
  1. 1<span style="color: #000000">.标准连表查询
  2. </span><span style="color: #008000">#</span><span style="color: #008000"> 示例:查询员工名与其部门名</span>
  3. res=session.query(Emp.ename,Dep.dname).join(Dep) <span style="color: #008000">#</span><span style="color: #008000">迭代器</span>
  4. <span style="color: #0000ff">for</span> row <span style="color: #0000ff">in</span><span style="color: #000000"> res:
  5.     </span><span style="color: #0000ff">print</span>(row[0],row[1]) <span style="color: #008000">#</span><span style="color: #008000">等同于print(row.ename,row.dname)</span>
  6. 2<span style="color: #000000">.基于relationship的正查、反查
  7. </span><span style="color: #008000">#</span><span style="color: #008000">SQLAlchemy的relationship在内部帮我们做好表的链接</span>
  8. <span style="color: #008000">#</span><span style="color: #008000">查询员工名与其部门名(正向查)</span>
  9. res=<span style="color: #000000">session.query(Emp)
  10. </span><span style="color: #0000ff">for</span> row <span style="color: #0000ff">in</span><span style="color: #000000"> res:
  11.     </span><span style="color: #0000ff">print</span><span style="color: #000000">(row.ename,row.id,row.depart.dname)
  12. </span><span style="color: #008000">#</span><span style="color: #008000">查询部门名以及该部门下的员工(反向查)</span>
  13. res=<span style="color: #000000">session.query(Dep)
  14. </span><span style="color: #0000ff">for</span> row <span style="color: #0000ff">in</span><span style="color: #000000"> res:
  15.     </span><span style="color: #008000">#</span><span style="color: #008000"> print(row.dname,row.xxoo)</span>
  16.     <span style="color: #0000ff">print</span>(row.dname,[r.ename <span style="color: #0000ff">for</span> r <span style="color: #0000ff">in</span> row.xxoo])

<3>关联问题

①外键关联（一对一）

  1. <span style="color: #008000">#</span><span style="color: #008000"> 外键关联</span><span style="color: #008000">
  2. #</span><span style="color: #008000">##用到的表 ###</span>
  3. <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  4. </span><span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  5. </span><span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> String,Column,Integer,ForeignKey,DATE
  6. </span><span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker,relationship
  7.  
  8. engine </span>= create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:12344321@localhost/solo</span><span style="color: #800000">"</span><span style="color: #000000">,,
  9.                        encoding</span>=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span><span style="color: #000000">)
  10.  
  11. Base </span>=<span style="color: #000000"> declarative_base()
  12.  
  13. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Student(Base):
  14.     </span><span style="color: #800080">__tablename__</span> =<span style="color: #800000">"</span><span style="color: #800000">student</span><span style="color: #800000">"</span><span style="color: #000000">
  15.     id </span>= Column(Integer,primary_key=<span style="color: #000000">True)
  16.     name </span>= Column(String(32),nullable=<span style="color: #000000">False)
  17.     register_date </span>= Column(DATE,nullable=<span style="color: #000000">False)
  18.  
  19.     </span><span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span><span style="color: #000000">(self):
  20.         </span><span style="color: #0000ff">return</span> <span style="color: #800000">"</span><span style="color: #800000"><%s name:%s></span><span style="color: #800000">"</span>%<span style="color: #000000">(self.id,self.name)
  21.  
  22. </span><span style="color: #0000ff">class</span><span style="color: #000000"> StudyRecord(Base):
  23.     </span><span style="color: #800080">__tablename__</span> = <span style="color: #800000">"</span><span style="color: #800000">study_record</span><span style="color: #800000">"</span><span style="color: #000000">
  24.     id </span>= Column(Integer,primary_key=<span style="color: #000000">True)
  25.     day </span>= Column(Integer,nullable=<span style="color: #000000">False)
  26.     status </span>= Column(String(32),nullable=<span style="color: #000000">False)
  27.     stu_id </span>= Column(Integer,ForeignKey(<span style="color: #800000">"</span><span style="color: #800000">student.id</span><span style="color: #800000">"</span>))              <span style="color: #008000">#</span><span style="color: #008000">关联student表里的id</span>
  28. <span style="color: #000000"> 
  29.     my_student </span>= relationship(<span style="color: #800000">"</span><span style="color: #800000">Student</span><span style="color: #800000">"</span>,backref=<span style="color: #800000">"</span><span style="color: #800000">my_study_record</span><span style="color: #800000">"</span>) <span style="color: #008000">#</span><span style="color: #008000"> Student为关联的类</span>
  30.     <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span><span style="color: #000000">(self):
  31.         </span><span style="color: #0000ff">return</span> <span style="color: #800000">"</span><span style="color: #800000"><%s name:%s></span><span style="color: #800000">"</span> %<span style="color: #000000"> (self.id, self.name)
  32.  
  33. Base.metadata.create_all(engine)
  34.  
  35. Session_class </span>= sessionmaker(bind=<span style="color: #000000">engine)
  36. session </span>=<span style="color: #000000"> Session_class()
  37.  
  38. s1 </span>= Student(name=<span style="color: #800000">"</span><span style="color: #800000">solo</span><span style="color: #800000">"</span>,register_date=<span style="color: #800000">"</span><span style="color: #800000">2016-10-26</span><span style="color: #800000">"</span><span style="color: #000000">)
  39. s2 </span>= Student(name=<span style="color: #800000">"</span><span style="color: #800000">alex</span><span style="color: #800000">"</span>,register_date=<span style="color: #800000">"</span><span style="color: #800000">2015-10-26</span><span style="color: #800000">"</span><span style="color: #000000">)
  40. s3 </span>= Student(name=<span style="color: #800000">"</span><span style="color: #800000">eric</span><span style="color: #800000">"</span>,register_date=<span style="color: #800000">"</span><span style="color: #800000">2014-10-26</span><span style="color: #800000">"</span><span style="color: #000000">)
  41. s4 </span>= Student(name=<span style="color: #800000">"</span><span style="color: #800000">rain</span><span style="color: #800000">"</span>,register_date=<span style="color: #800000">"</span><span style="color: #800000">2013-10-26</span><span style="color: #800000">"</span><span style="color: #000000">)
  42.  
  43. r1 </span>= StudyRecord(day=1,status=<span style="color: #800000">"</span><span style="color: #800000">YES</span><span style="color: #800000">"</span>,stu_id=1<span style="color: #000000">)
  44. r2 </span>= StudyRecord(day=2,status=<span style="color: #800000">"</span><span style="color: #800000">No</span><span style="color: #800000">"</span>,stu_id=1<span style="color: #000000">)
  45. r3 </span>= StudyRecord(day=3,status=<span style="color: #800000">"</span><span style="color: #800000">YES</span><span style="color: #800000">"</span>,stu_id=1<span style="color: #000000">)
  46. r4 </span>= StudyRecord(day=1,status=<span style="color: #800000">"</span><span style="color: #800000">YES</span><span style="color: #800000">"</span>,stu_id=2<span style="color: #000000">)
  47.  
  48. session.add_all([s1,s2,s3,s4,r1,r2,r3,r4])
  49. session.commit()
  50. </span><span style="color: #008000">#</span><span style="color: #008000">查询</span><span style="color: #008000">
  51. #</span><span style="color: #008000">stu_obj = session.query(Student).filter(Student.name=="solo").first()</span><span style="color: #008000">
  52. #</span><span style="color: #008000">print(stu_obj)</span><span style="color: #008000">
  53. #</span><span style="color: #008000"><id:1 name:solo></span>
  54.  
  55. <span style="color: #008000">#</span><span style="color: #008000">print(stu_obj.my_study_record)</span><span style="color: #008000">
  56. #</span><span style="color: #008000">[<name:solo day:1 status:YES>, <name:solo day:2 status:No>, <name:solo day:3 status:YES>]</span>

②多外键关联（一对多）

  1. <span style="color: #008000">#</span><span style="color: #008000">##orm_many_fk.py文件###</span>
  2. <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span><span style="color: #008000">
  3. #</span><span style="color: #008000"> -*- coding:utf-8 -*-</span><span style="color: #008000">
  4. #</span><span style="color: #008000"> _author_soloLi</span>
  5. <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  6. </span><span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Integer,String,Column,ForeignKey
  7. </span><span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  8. </span><span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker,relationship
  9. engine </span>= create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:12344321@localhost/solo</span><span style="color: #800000">"</span><span style="color: #000000">,
  10.                        encoding</span>=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span>,echo=<span style="color: #000000"> True)
  11. Base </span>=<span style="color: #000000">  declarative_base()
  12. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Customer(Base):
  13.     </span><span style="color: #800080">__tablename__</span> = <span style="color: #800000">"</span><span style="color: #800000">customer</span><span style="color: #800000">"</span><span style="color: #000000">
  14.     id </span>= Column(Integer,primary_key=<span style="color: #000000">True)
  15.     name </span>= Column(String(32<span style="color: #000000">))
  16.     billing_address_id </span>= Column(Integer,ForeignKey(<span style="color: #800000">"</span><span style="color: #800000">address.id</span><span style="color: #800000">"</span>))    <span style="color: #008000">#</span><span style="color: #008000">账单地址</span>
  17.     shipping_address_id = Column(Integer, ForeignKey(<span style="color: #800000">"</span><span style="color: #800000">address.id</span><span style="color: #800000">"</span>))  <span style="color: #008000">#</span><span style="color: #008000">邮寄地址        #可以帮其他人买东西，也可以自己给自己买</span>
  18.     <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span><span style="color: #000000">(self):
  19.        </span><span style="color: #0000ff">return</span> <span style="color: #800000">"</span><span style="color: #800000"><name:%s billing_add:%s shipping_add:%s></span><span style="color: #800000">"</span>%<span style="color: #000000">(self.name,self.billing_address.street,
  20.                                                           self.shipping_address.street)
  21.     billing_address </span>= relationship(<span style="color: #800000">"</span><span style="color: #800000">Address</span><span style="color: #800000">"</span>,foreign_keys=[billing_address_id])    <span style="color: #008000">#</span><span style="color: #008000">必须写foreign_keys</span>
  22.     shipping_address = relationship(<span style="color: #800000">"</span><span style="color: #800000">Address</span><span style="color: #800000">"</span>,foreign_keys=<span style="color: #000000">[shipping_address_id])
  23. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Address(Base):
  24.     </span><span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">address</span><span style="color: #800000">‘</span><span style="color: #000000">
  25.     id </span>= Column(Integer, primary_key=<span style="color: #000000">True)
  26.     street </span>= Column(String(32<span style="color: #000000">))
  27.     city </span>= Column(String(32<span style="color: #000000">))
  28.     state </span>= Column(String(32<span style="color: #000000">))
  29. Base.metadata.create_all(engine)</span>
  1. <span style="color: #008000">#</span><span style="color: #008000">##orm_api.py文件###</span>
  2. <span style="color: #008000">#</span><span style="color: #008000">规矩：创建数据表要与增删改查的操作表分开，进行导入操作</span><span style="color: #008000">
  3. #</span><span style="color: #008000">!/usr/bin/env python</span><span style="color: #008000">
  4. #</span><span style="color: #008000"> -*- coding:utf-8 -*-</span><span style="color: #008000">
  5. #</span><span style="color: #008000"> _author_soloLi</span>
  6. <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker
  7. </span><span style="color: #0000ff">from</span> test <span style="color: #0000ff">import</span> orm_many_fk   <span style="color: #008000">#</span><span style="color: #008000">此项为导入上一个文件</span>
  8. <span style="color: #000000">
  9. Session </span>= sessionmaker(bind=<span style="color: #000000">orm_many_fk.engine)
  10. session </span>=<span style="color: #000000"> Session()
  11. a1 </span>= orm_many_fk.Address(street=<span style="color: #800000">"</span><span style="color: #800000">Tiantongyuan</span><span style="color: #800000">"</span>,city=<span style="color: #800000">"</span><span style="color: #800000">ChangPing</span><span style="color: #800000">"</span>,state=<span style="color: #800000">"</span><span style="color: #800000">BJ</span><span style="color: #800000">"</span><span style="color: #000000">)
  12. a2 </span>= orm_many_fk.Address(street=<span style="color: #800000">"</span><span style="color: #800000">Wudaokou</span><span style="color: #800000">"</span>,city=<span style="color: #800000">"</span><span style="color: #800000">HaiDian</span><span style="color: #800000">"</span>,state=<span style="color: #800000">"</span><span style="color: #800000">BJ</span><span style="color: #800000">"</span><span style="color: #000000">)
  13. a3 </span>= orm_many_fk.Address(street=<span style="color: #800000">"</span><span style="color: #800000">DongGuan</span><span style="color: #800000">"</span>,city=<span style="color: #800000">"</span><span style="color: #800000">QinHuangDao</span><span style="color: #800000">"</span>,state=<span style="color: #800000">"</span><span style="color: #800000">HB</span><span style="color: #800000">"</span><span style="color: #000000">)
  14. session.add_all([a1,a2,a3])
  15. c1 </span>= orm_many_fk.Customer(name=<span style="color: #800000">"</span><span style="color: #800000">solo</span><span style="color: #800000">"</span>,billing_address_id=1,shipping_address_id=2<span style="color: #000000">)
  16. c2 </span>= orm_many_fk.Customer(name=<span style="color: #800000">"</span><span style="color: #800000">Alex</span><span style="color: #800000">"</span>,billing_address_id=3,shipping_address_id=3<span style="color: #000000">)
  17. session.add_all([c1,c2])
  18. cus_obj </span>= session.query(orm_many_fk.Customer).filter_by(name=<span style="color: #800000">"</span><span style="color: #800000">solo</span><span style="color: #800000">"</span><span style="color: #000000">).first()
  19. </span><span style="color: #0000ff">print</span><span style="color: #000000">(cus_obj)
  20. session.commit()</span>

③多外键关联（多对多）

现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
1.一本书可以有好几个作者一起出版
2.一个作者可以写好几本书

  1. <span style="color: #008000">#</span><span style="color: #008000">##many_to_many.py文件###</span>
  2. <span style="color: #008000">#</span><span style="color: #008000">创建表结构：</span><span style="color: #008000">
  3. #</span><span style="color: #008000">一本书可以有多个作者，一个作者又可以出版多本书</span><span style="color: #008000">
  4. #</span><span style="color: #008000">!/usr/bin/env python</span><span style="color: #008000">
  5. #</span><span style="color: #008000"> -*- coding:utf-8 -*-</span><span style="color: #008000">
  6. #</span><span style="color: #008000"> _author_soloLi</span>
  7. <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Table, Column, Integer,String,DATE, ForeignKey
  8. </span><span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> relationship
  9. </span><span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  10. </span><span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  11. </span><span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker
  12. engine </span>= create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:12344321@localhost/solo</span><span style="color: #800000">"</span><span style="color: #000000">,
  13.                        encoding</span>=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span><span style="color: #000000">)
  14. Base </span>=<span style="color: #000000"> declarative_base()
  15. </span><span style="color: #008000">#</span><span style="color: #008000">创建book_m2m_author表，表不用用户操作，系统自动维护，自动添加数据</span>
  16. book_m2m_author = Table(<span style="color: #800000">‘</span><span style="color: #800000">book_m2m_author</span><span style="color: #800000">‘</span><span style="color: #000000">, Base.metadata,
  17.                         Column(</span><span style="color: #800000">‘</span><span style="color: #800000">book_id</span><span style="color: #800000">‘</span>,Integer,ForeignKey(<span style="color: #800000">‘</span><span style="color: #800000">books.id</span><span style="color: #800000">‘</span><span style="color: #000000">)),
  18.                         Column(</span><span style="color: #800000">‘</span><span style="color: #800000">author_id</span><span style="color: #800000">‘</span>,Integer,ForeignKey(<span style="color: #800000">‘</span><span style="color: #800000">authors.id</span><span style="color: #800000">‘</span><span style="color: #000000">)),
  19.                         )
  20. </span><span style="color: #0000ff">class</span><span style="color: #000000"> Book(Base):
  21.     </span><span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">books</span><span style="color: #800000">‘</span><span style="color: #000000">
  22.     id </span>= Column(Integer,primary_key=<span style="color: #000000">True)
  23.     name </span>= Column(String(64<span style="color: #000000">))
  24.     pub_date </span>=<span style="color: #000000"> Column(DATE)
  25.     </span><span style="color: #008000">#</span><span style="color: #008000">关联Author类，secondary表示通过book_m2m_author表进行查询关联数据，backref反向查询也一样</span>
  26.     authors = relationship(<span style="color: #800000">‘</span><span style="color: #800000">Author</span><span style="color: #800000">‘</span>,secondary=book_m2m_author,backref=<span style="color: #800000">‘</span><span style="color: #800000">books</span><span style="color: #800000">‘</span>)

 

  1. <span style="color: #008000">#</span><span style="color: #008000">##many_to_many_api.py文件###</span>
  2. <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span><span style="color: #008000">
  3. #</span><span style="color: #008000"> -*- coding:utf-8 -*-</span><span style="color: #008000">
  4. #</span><span style="color: #008000"> _author_soloLi</span>
  5. <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker
  6. </span><span style="color: #0000ff">from</span> day04 <span style="color: #0000ff">import</span><span style="color: #000000"> many_to_many
  7. Session </span>= sessionmaker(bind=<span style="color: #000000">many_to_many.engine)
  8. session </span>=<span style="color: #000000"> Session()
  9. b1 </span>= many_to_many.Book(name=<span style="color: #800000">"</span><span style="color: #800000">learn python with Alex</span><span style="color: #800000">"</span>,pub_date=<span style="color: #800000">"</span><span style="color: #800000">2014-05-02</span><span style="color: #800000">"</span><span style="color: #000000">)
  10. b2 </span>= many_to_many.Book(name=<span style="color: #800000">"</span><span style="color: #800000">learn linux with Alex</span><span style="color: #800000">"</span>,pub_date=<span style="color: #800000">"</span><span style="color: #800000">2015-05-02</span><span style="color: #800000">"</span><span style="color: #000000">)
  11. b3 </span>= many_to_many.Book(name=<span style="color: #800000">"</span><span style="color: #800000">learn go with Alex</span><span style="color: #800000">"</span>,pub_date=<span style="color: #800000">"</span><span style="color: #800000">2016-05-02</span><span style="color: #800000">"</span><span style="color: #000000">)
  12. a1 </span>= many_to_many.Author(name=<span style="color: #800000">"</span><span style="color: #800000">Alex</span><span style="color: #800000">"</span><span style="color: #000000">)
  13. a2 </span>= many_to_many.Author(name=<span style="color: #800000">"</span><span style="color: #800000">Jack</span><span style="color: #800000">"</span><span style="color: #000000">)
  14. a3 </span>= many_to_many.Author(name=<span style="color: #800000">"</span><span style="color: #800000">Rain</span><span style="color: #800000">"</span><span style="color: #000000">)
  15. </span><span style="color: #008000">#</span><span style="color: #008000">关键来了，创建关联关系</span>
  16. b1.authors =<span style="color: #000000"> [a1,a3]
  17. b3.authors </span>=<span style="color: #000000"> [a1,a2,a3]
  18. session.add_all([b1,b2,b3,a1,a2,a3])
  19. session.commit()
  20. author_obj </span>= session.query(many_to_many.Author).filter_by(name=<span style="color: #800000">"</span><span style="color: #800000">Alex</span><span style="color: #800000">"</span><span style="color: #000000">).first()
  21. </span><span style="color: #0000ff">print</span><span style="color: #000000">(author_obj,author_obj.books)
  22. book_obj </span>= session.query(many_to_many.Book).filter_by(id=2<span style="color: #000000">).first()
  23. </span><span style="color: #0000ff">print</span><span style="color: #000000">(book_obj,book_obj.authors)
  24. </span><span style="color: #008000">#</span><span style="color: #008000"> Alex [learn python with Alex, learn go with Alex]</span><span style="color: #008000">
  25. #</span><span style="color: #008000"> learn go with Alex [Alex, Jack, Rain]</span>
  26. <span style="color: #008000">#</span><span style="color: #008000">#通过书删除作者##</span><span style="color: #008000">
  27. #</span><span style="color: #008000">author_obj =s.query(Author).filter_by(name="Jack").first()</span><span style="color: #008000">
  28. #</span><span style="color: #008000">book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()</span><span style="color: #008000">
  29. #</span><span style="color: #008000">book_obj.authors.remove(author_obj) #从一本书里删除一个作者</span><span style="color: #008000">
  30. #</span><span style="color: #008000">s.commit()</span>
  31. <span style="color: #008000">#</span><span style="color: #008000">#直接删除作者##　</span><span style="color: #008000">
  32. #</span><span style="color: #008000">删除作者时，会把这个作者跟所有书的关联关系数据也自动删除</span><span style="color: #008000">
  33. #</span><span style="color: #008000">author_obj =s.query(Author).filter_by(name="Alex").first()</span><span style="color: #008000">
  34. #</span><span style="color: #008000"> print(author_obj.name , author_obj.books)</span><span style="color: #008000">
  35. #</span><span style="color: #008000">s.delete(author_obj)</span><span style="color: #008000">
  36. #</span><span style="color: #008000">s.commit()</span>

<4>中文问题

  1. engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:12344321@localhost/solo</span><span style="color: #800000">"</span><span style="color: #000000">)
  2.  
  3.  
  4. </span><span style="color: #008000">#</span><span style="color: #008000">修改查看数据库字符编码</span>
  5. mysql><span style="color: #000000"> alter database solo character set utf8;
  6. Query OK, </span>1 row affected (0.00<span style="color: #000000"> sec)
  7.  
  8. mysql</span>>  show variables like <span style="color: #800000">‘</span><span style="color: #800000">character_set_database</span><span style="color: #800000">‘</span><span style="color: #000000">;
  9. </span>+------------------------+-------+
  10. | Variable_name          | Value |
  11. +------------------------+-------+
  12. | character_set_database | utf8  |

 

Python学习笔记整理总结【ORM（SQLAlchemy）】

标签：subquery   映射   max   idt   括号   缺点   内连接   ase   ted   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！