python第六十六天--sqlalchemy
- PHP中文网原创
- 2017-07-07 18:13:111288浏览
<span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!usr/bin/env python</span>
<span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">-*-coding:utf-8-*-</span>
<span style="color: #008080"> 3</span> <span style="color: #008000">#</span><span style="color: #008000"> Author calmyan </span>
<span style="color: #008080"> 4</span> <span style="color: #008000">#</span><span style="color: #008000">python </span>
<span style="color: #008080"> 5</span> <span style="color: #008000">#</span><span style="color: #008000">2017/7/6 21:29</span>
<span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">__author__='Administrator'</span>
<span style="color: #008080"> 7</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
</span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
</span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker, relationship
</span><span style="color: #008080">10</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
</span><span style="color: #008080">11</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span> func <span style="color: #008000">#</span><span style="color: #008000">统计</span>
<span style="color: #008080">12</span>
<span style="color: #008080">13</span> <span style="color: #008000">#</span><span style="color: #008000"> 用户 密码 主机 库</span>
<span style="color: #008080">14</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:root@127.0.0.1:3306/test</span><span style="color: #800000">"</span>,encoding=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span>,echo=<span style="color: #000000">False)
</span><span style="color: #008080">15</span>
<span style="color: #008080">16</span> Base = declarative_base()<span style="color: #008000">#</span><span style="color: #008000">生成orm 基类</span>
<span style="color: #008080">17</span> <span style="color: #0000ff">class</span><span style="color: #000000"> User_2(Base):
</span><span style="color: #008080">18</span> <span style="color: #800080">__tablename__</span>=<span style="color: #800000">'</span><span style="color: #800000">user</span><span style="color: #800000">'</span> <span style="color: #008000">#</span><span style="color: #008000">表名</span>
<span style="color: #008080">19</span> id = Column(Integer,primary_key=True)<span style="color: #008000">#</span><span style="color: #008000">整数类型 设为主键</span>
<span style="color: #008080">20</span> name = Column(String(32))<span style="color: #008000">#</span><span style="color: #008000">字符串类型</span>
<span style="color: #008080">21</span> age =<span style="color: #000000"> Column(Integer)
</span><span style="color: #008080">22</span> addr= Column(String(256<span style="color: #000000">))
</span><span style="color: #008080">23</span>
<span style="color: #008080">24</span> <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span>(self):<span style="color: #008000">#</span><span style="color: #008000">输出查询</span>
<span style="color: #008080">25</span> <span style="color: #0000ff">return</span> <span style="color: #800000">'</span><span style="color: #800000">%s>name:%s--age:%saddrs:%s</span><span style="color: #800000">'</span>%<span style="color: #000000">(self.id,self.name,self.age,self.addr)
</span><span style="color: #008080">26</span>
<span style="color: #008080">27</span>
<span style="color: #008080">28</span> Session_class=sessionmaker(bind=engine)<span style="color: #008000">#</span><span style="color: #008000">创建与数据库的会话 类</span>
<span style="color: #008080">29</span> Session=Session_class()<span style="color: #008000">#</span><span style="color: #008000">生成实例</span>
<span style="color: #008080">30</span>
<span style="color: #008080">31</span> <span style="color: #008000">#</span><span style="color: #008000">增加</span>
<span style="color: #008080">32</span> <span style="color: #800000">'''</span>
<span style="color: #008080">33</span> <span style="color: #800000">user_obj =User(name="sa2",addrs="1234")#生成你要创建的数据对象
</span><span style="color: #008080">34</span> <span style="color: #800000">Session.add(user_obj)# 添加记录
</span><span style="color: #008080">35</span> <span style="color: #800000">'''</span>
<span style="color: #008080">36</span> <span style="color: #008000">#</span><span style="color: #008000">添加一组数据</span>
<span style="color: #008080">37</span> <span style="color: #800000">'''</span>
<span style="color: #008080">38</span> <span style="color: #800000">Session.add_all([
</span><span style="color: #008080">39</span> <span style="color: #800000"> User(name="abcg",age=34,addr="sdfsdf"),
</span><span style="color: #008080">40</span> <span style="color: #800000"> User(name="bcdq",age=11,addr="chaense")
</span><span style="color: #008080">41</span> <span style="color: #800000">])
</span><span style="color: #008080">42</span> <span style="color: #800000">'''</span>
<span style="color: #008080">43</span> <span style="color: #008000">#</span><span style="color: #008000">删除</span>
<span style="color: #008080">44</span> <span style="color: #800000">'''</span>
<span style="color: #008080">45</span> <span style="color: #800000">Session.query(User).filter(User.id>3).filter(User.id<6).delete()
</span><span style="color: #008080">46</span> <span style="color: #800000">'''</span>
<span style="color: #008080">47</span> <span style="color: #008000">#</span><span style="color: #008000"> 查询 条件 所有</span>
<span style="color: #008080">48</span> data =Session.query(User_2).filter_by(id=2<span style="color: #000000">).all()
</span><span style="color: #008080">49</span> <span style="color: #008000">#</span><span style="color: #008000"> 多个条件</span>
<span style="color: #008080">50</span> data2 =Session.query(User_2).filter(User_2.id>2).filter(User_2.id<5<span style="color: #000000">).all()
</span><span style="color: #008080">51</span>
<span style="color: #008080">52</span>
<span style="color: #008080">53</span> <span style="color: #008000">#</span><span style="color: #008000">改</span>
<span style="color: #008080">54</span>
<span style="color: #008080">55</span> Session.query(User_2).filter(User_2.id ==2).update({<span style="color: #800000">"</span><span style="color: #800000">name</span><span style="color: #800000">"</span> : <span style="color: #800000">"</span><span style="color: #800000">099</span><span style="color: #800000">"</span><span style="color: #000000">})
</span><span style="color: #008080">56</span> <span style="color: #0000ff">print</span><span style="color: #000000">(data)
</span><span style="color: #008080">57</span> <span style="color: #0000ff">print</span><span style="color: #000000">(data2)
</span><span style="color: #008080">58</span> <span style="color: #800000">'''</span>
<span style="color: #008080">59</span> <span style="color: #800000">print(Session.query(User).filter(User.id>2).filter(User.name.in_(["sa",'sa2'])).all())
</span><span style="color: #008080">60</span> <span style="color: #800000">Session.rollback()#回滚操作
</span><span style="color: #008080">61</span> <span style="color: #800000">print(Session.query(User).filter(User.id>2).filter(User.name.in_(["sa",'sa2'])).all())
</span><span style="color: #008080">62</span> <span style="color: #800000">'''</span>
<span style="color: #008080">63</span> <span style="color: #0000ff">print</span>(Session.query(User_2).filter(User_2.id>2).filter(User_2.name.in_([<span style="color: #800000">"</span><span style="color: #800000">sa</span><span style="color: #800000">"</span>,<span style="color: #800000">'</span><span style="color: #800000">sa2</span><span style="color: #800000">'</span>])).count())<span style="color: #008000">#</span><span style="color: #008000">统计符合条件出现的次数</span>
<span style="color: #008080">64</span> counts=Session.query(User_2).filter(User_2.id>2).filter(User_2.name.in_([<span style="color: #800000">'</span><span style="color: #800000">bcd</span><span style="color: #800000">'</span>])).count()<span style="color: #008000">#</span><span style="color: #008000">统计符合条件出现的次数</span>
<span style="color: #008080">65</span> <span style="color: #0000ff">print</span><span style="color: #000000">(counts)
</span><span style="color: #008080">66</span> <span style="color: #008000">#</span><span style="color: #008000">分组查询 字段 统计次数 字段</span>
<span style="color: #008080">67</span> gurps=<span style="color: #000000">Session.query(User_2.name,func.count(User_2.name)).group_by(User_2.name).all()
</span><span style="color: #008080">68</span> <span style="color: #0000ff">print</span><span style="color: #000000">(gurps)
</span><span style="color: #008080">69</span> <span style="color: #008000">#</span><span style="color: #008000"> 以addrs为条件</span>
<span style="color: #008080">70</span> gurps1=<span style="color: #000000">Session.query(User_2.name,func.count(User_2.name)).group_by(User_2.addr).all()
</span><span style="color: #008080">71</span> <span style="color: #0000ff">print</span><span style="color: #000000">(gurps1)
</span><span style="color: #008080">72</span>
<span style="color: #008080">73</span>
<span style="color: #008080">74</span> Session.commit()<span style="color: #008000">#</span><span style="color: #008000">关闭事务</span>
sqlalchemy 基本操作
<span style="color: #008080"> 1</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base </span><span style="color: #008080"> 2</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, ForeignKey, UniqueConstraint, Index </span><span style="color: #008080"> 3</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker, relationship </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine </span><span style="color: #008080"> 5</span> <span style="color: #008000">#</span><span style="color: #008000"> 用户 密码 主机 库</span> <span style="color: #008080"> 6</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:root@127.0.0.1:3306/test</span><span style="color: #800000">"</span>,encoding=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span>,echo=<span style="color: #000000">True) </span><span style="color: #008080"> 7</span> <span style="color: #008080"> 8</span> Base = declarative_base()<span style="color: #008000">#</span><span style="color: #008000">生成orm 基类</span> <span style="color: #008080"> 9</span> <span style="color: #0000ff">class</span><span style="color: #000000"> User(Base): </span><span style="color: #008080">10</span> <span style="color: #800080">__tablename__</span>=<span style="color: #800000">'</span><span style="color: #800000">info_l</span><span style="color: #800000">'</span> <span style="color: #008000">#</span><span style="color: #008000">表名</span> <span style="color: #008080">11</span> id = Column(Integer,primary_key=True)<span style="color: #008000">#</span><span style="color: #008000">整数类型 设为主键</span> <span style="color: #008080">12</span> name = Column(String(32))<span style="color: #008000">#</span><span style="color: #008000">字符串类型</span> <span style="color: #008080">13</span> addrs= Column(String(256<span style="color: #000000">)) </span><span style="color: #008080">14</span> <span style="color: #008080">15</span> Base.metadata.create_all(engine)<span style="color: #008000">#</span><span style="color: #008000">创建表结构</span> <span style="color: #008080">16</span> <span style="color: #008080">17</span> Session_class=sessionmaker(bind=engine)<span style="color: #008000">#</span><span style="color: #008000">创建与数据库的会话 类</span> <span style="color: #008080">18</span> Session=Session_class()<span style="color: #008000">#</span><span style="color: #008000">生成实例</span> <span style="color: #008080">19</span> <span style="color: #008080">20</span> user_obj =User(name=<span style="color: #800000">"</span><span style="color: #800000">sa</span><span style="color: #800000">"</span>,addrs=<span style="color: #800000">"</span><span style="color: #800000">1234</span><span style="color: #800000">"</span>)<span style="color: #008000">#</span><span style="color: #008000">生成你要创建的数据对象</span> <span style="color: #008080">21</span> <span style="color: #0000ff">print</span>(user_obj.name,user_obj.addrs)<span style="color: #008000">#</span><span style="color: #008000">此时并未创建对象</span> <span style="color: #008080">22</span> Session.add(user_obj)<span style="color: #008000">#</span><span style="color: #008000">开启事务进行连接 添加记录</span> <span style="color: #008080">23</span> Session.commit()<span style="color: #008000">#</span><span style="color: #008000">关闭事务</span>
外键操作示例
<span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!usr/bin/env python</span> <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">-*-coding:utf-8-*-</span> <span style="color: #008080"> 3</span> <span style="color: #008000">#</span><span style="color: #008000"> Author calmyan </span> <span style="color: #008080"> 4</span> <span style="color: #008000">#</span><span style="color: #008000">python </span> <span style="color: #008080"> 5</span> <span style="color: #008000">#</span><span style="color: #008000">2017/7/7 10:28</span> <span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">__author__='Administrator'</span> <span style="color: #008080"> 7</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base </span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, ForeignKey, UniqueConstraint, Index,DATE </span><span style="color: #008080">10</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker, relationship </span><span style="color: #008080">11</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine </span><span style="color: #008080">12</span> <span style="color: #008000">#</span><span style="color: #008000"> 用户 密码 主机 库</span> <span style="color: #008080">13</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:root@127.0.0.1:3306/test</span><span style="color: #800000">"</span>,encoding=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span>,echo=<span style="color: #000000">False) </span><span style="color: #008080">14</span> <span style="color: #008080">15</span> Base = declarative_base()<span style="color: #008000">#</span><span style="color: #008000">生成orm 基类</span> <span style="color: #008080">16</span> <span style="color: #008080">17</span> <span style="color: #0000ff">class</span> Studes(Base):<span style="color: #008000">#</span><span style="color: #008000">学生类</span> <span style="color: #008080">18</span> <span style="color: #800080">__tablename__</span> =<span style="color: #800000">'</span><span style="color: #800000">studes</span><span style="color: #800000">'</span> <span style="color: #008080">19</span> id = Column(Integer,primary_key=True)<span style="color: #008000">#</span><span style="color: #008000">整数类型 设为主键</span> <span style="color: #008080">20</span> name = Column(String(32),nullable=False)<span style="color: #008000">#</span><span style="color: #008000">字符串类型 不能为空</span> <span style="color: #008080">21</span> register_day= Column(DATE,nullable=False)<span style="color: #008000">#</span><span style="color: #008000">日期</span> <span style="color: #008080">22</span> <span style="color: #008080">23</span> <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span>(self):<span style="color: #008000">#</span><span style="color: #008000">输出查询</span> <span style="color: #008080">24</span> <span style="color: #0000ff">return</span> <span style="color: #800000">'</span><span style="color: #800000">%s>name:%s--register_day :%s</span><span style="color: #800000">'</span>%<span style="color: #000000">(self.id,self.name,self.register_day) </span><span style="color: #008080">25</span> <span style="color: #008080">26</span> <span style="color: #008080">27</span> <span style="color: #0000ff">class</span> Studes_Cr(Base):<span style="color: #008000">#</span><span style="color: #008000">课程类</span> <span style="color: #008080">28</span> <span style="color: #800080">__tablename__</span> =<span style="color: #800000">'</span><span style="color: #800000">stud_cr</span><span style="color: #800000">'</span> <span style="color: #008080">29</span> id = Column(Integer,primary_key=True)<span style="color: #008000">#</span><span style="color: #008000">整数类型 设为主键</span> <span style="color: #008080">30</span> day= Column(Integer,nullable=<span style="color: #000000">False) </span><span style="color: #008080">31</span> status= Column(String(35),nullable=False)<span style="color: #008000">#</span><span style="color: #008000">上课记录</span> <span style="color: #008080">32</span> stu_id= Column(Integer,ForeignKey(<span style="color: #800000">"</span><span style="color: #800000">studes.id</span><span style="color: #800000">"</span>,))<span style="color: #008000">#</span><span style="color: #008000">外键关联</span> <span style="color: #008080">33</span> studes=relationship(<span style="color: #800000">"</span><span style="color: #800000">Studes</span><span style="color: #800000">"</span>,backref=<span style="color: #800000">"</span><span style="color: #800000">m_study_reg</span><span style="color: #800000">"</span>)<span style="color: #008000">#</span><span style="color: #008000">自定义关联反查</span> <span style="color: #008080">34</span> <span style="color: #008080">35</span> <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span>(self):<span style="color: #008000">#</span><span style="color: #008000">输出查询 自定义 外键关联调用</span> <span style="color: #008080">36</span> <span style="color: #0000ff">return</span> <span style="color: #800000">'</span><span style="color: #800000">%s>day:%s status:%s</span><span style="color: #800000">'</span>%<span style="color: #000000">(self.studes.name,self.day,self.status) </span><span style="color: #008080">37</span> <span style="color: #008080">38</span> Base.metadata.create_all(engine)<span style="color: #008000">#</span><span style="color: #008000">创建表结构</span> <span style="color: #008080">39</span> <span style="color: #008080">40</span> Session_class=sessionmaker(bind=engine)<span style="color: #008000">#</span><span style="color: #008000">创建与数据库的会话 类</span> <span style="color: #008080">41</span> Session=Session_class()<span style="color: #008000">#</span><span style="color: #008000">生成实例</span> <span style="color: #008080">42</span> <span style="color: #008080">43</span> <span style="color: #800000">'''</span> <span style="color: #008080">44</span> <span style="color: #800000">s1 = Studes(name="Alex",register_day='2017-05-06')#学生 </span><span style="color: #008080">45</span> <span style="color: #800000">s2 = Studes(name="bolg",register_day='2017-03-06') </span><span style="color: #008080">46</span> <span style="color: #800000">s3 = Studes(name="calm",register_day='2017-01-07') </span><span style="color: #008080">47</span> <span style="color: #800000">s4 = Studes(name="Defr",register_day='2017-03-16') </span><span style="color: #008080">48</span> <span style="color: #800000">s5 = Studes(name="Eale",register_day='2017-02-24') </span><span style="color: #008080">49</span> <span style="color: #008080">50</span> <span style="color: #800000">study_obj1=Studes_Cr(day=1,status="YES",stu_id=1)#上课记录 </span><span style="color: #008080">51</span> <span style="color: #800000">study_obj2=Studes_Cr(day=1,status="YES",stu_id=2)#上课记录 </span><span style="color: #008080">52</span> <span style="color: #800000">study_obj3=Studes_Cr(day=1,status="NO",stu_id=3)#上课记录 </span><span style="color: #008080">53</span> <span style="color: #800000">study_obj4=Studes_Cr(day=1,status="YES",stu_id=4)#上课记录 </span><span style="color: #008080">54</span> <span style="color: #800000">study_obj5=Studes_Cr(day=2,status="NO",stu_id=1)#上课记录 </span><span style="color: #008080">55</span> <span style="color: #800000">study_obj6=Studes_Cr(day=2,status="YES",stu_id=2)#上课记录 </span><span style="color: #008080">56</span> <span style="color: #800000">study_obj7=Studes_Cr(day=2,status="YES",stu_id=3)#上课记录 </span><span style="color: #008080">57</span> <span style="color: #008080">58</span> <span style="color: #800000">#一次全部添加 </span><span style="color: #008080">59</span> <span style="color: #800000">Session.add_all([s1,s2,s3,s4,s5,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5,study_obj6,study_obj7]) </span><span style="color: #008080">60</span> <span style="color: #800000">'''</span> <span style="color: #008080">61</span> <span style="color: #008080">62</span> <span style="color: #008080">63</span> <span style="color: #008000">#</span><span style="color: #008000">查询 学生对应的信息</span> <span style="color: #008080">64</span> stu_obj= Session.query(Studes).filter(Studes.name==<span style="color: #800000">"</span><span style="color: #800000">Alex</span><span style="color: #800000">"</span><span style="color: #000000">).first() </span><span style="color: #008080">65</span> <span style="color: #0000ff">print</span><span style="color: #000000">(stu_obj) </span><span style="color: #008080">66</span> <span style="color: #008000">#</span><span style="color: #008000">通过外键进行相关信息查询</span> <span style="color: #008080">67</span> <span style="color: #0000ff">print</span><span style="color: #000000">(stu_obj.m_study_reg) </span><span style="color: #008080">68</span> <span style="color: #008080">69</span> Session.commit()
外键 多外键关联示例: 多外键关联.py 多外键_操作.py
多外键关联.py
<span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!usr/bin/env python</span> <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">-*-coding:utf-8-*-</span> <span style="color: #008080"> 3</span> <span style="color: #008000">#</span><span style="color: #008000"> Author calmyan </span> <span style="color: #008080"> 4</span> <span style="color: #008000">#</span><span style="color: #008000">python </span> <span style="color: #008080"> 5</span> <span style="color: #008000">#</span><span style="color: #008000">2017/7/7 13:06</span> <span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">__author__='Administrator'</span> <span style="color: #008080"> 7</span> <span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Integer, ForeignKey, String, Column </span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base </span><span style="color: #008080">10</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> relationship </span><span style="color: #008080">11</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine </span><span style="color: #008080">12</span> <span style="color: #008080">13</span> Base = declarative_base()<span style="color: #008000">#</span><span style="color: #008000">生成orm 基类</span> <span style="color: #008080">14</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Customer(Base): </span><span style="color: #008080">15</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: #008080">16</span> id = Column(Integer, primary_key=<span style="color: #000000">True) </span><span style="color: #008080">17</span> name = Column(String(32),nullable=<span style="color: #000000">False) </span><span style="color: #008080">18</span> <span style="color: #008080">19</span> billing_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> <span style="color: #008080">20</span> 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> <span style="color: #008080">21</span> <span style="color: #008080">22</span> billing_address = 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">帐单地址自定义反查 对应外键</span> <span style="color: #008080">23</span> shipping_address = relationship(<span style="color: #800000">"</span><span style="color: #800000">Address</span><span style="color: #800000">"</span>,foreign_keys=[shipping_address_id])<span style="color: #008000">#</span><span style="color: #008000">收货地址</span> <span style="color: #008080">24</span> <span style="color: #008080">25</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Address(Base): </span><span style="color: #008080">26</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: #008080">27</span> id = Column(Integer, primary_key=<span style="color: #000000">True) </span><span style="color: #008080">28</span> street = Column(String(32))<span style="color: #008000">#</span><span style="color: #008000">详细</span> <span style="color: #008080">29</span> city = Column(String(32))<span style="color: #008000">#</span><span style="color: #008000">市</span> <span style="color: #008080">30</span> state = Column(String(255))<span style="color: #008000">#</span><span style="color: #008000">省</span> <span style="color: #008080">31</span> <span style="color: #008080">32</span> <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span><span style="color: #000000">(self): </span><span style="color: #008080">33</span> <span style="color: #0000ff">return</span> self.state+self.city+<span style="color: #000000">self.street </span><span style="color: #008080">34</span> <span style="color: #008080">35</span> <span style="color: #008000">#</span><span style="color: #008000"> 用户 密码 主机 库</span> <span style="color: #008080">36</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:root@127.0.0.1:3306/test</span><span style="color: #800000">"</span>,encoding=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span>,echo=<span style="color: #000000">False) </span><span style="color: #008080">37</span> <span style="color: #008080">38</span> <span style="color: #008080">39</span> Base.metadata.create_all(engine)<span style="color: #008000">#</span><span style="color: #008000">创建表结构</span>
多外键_操作.py
<span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!usr/bin/env python</span> <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">-*-coding:utf-8-*-</span> <span style="color: #008080"> 3</span> <span style="color: #008000">#</span><span style="color: #008000"> Author calmyan </span> <span style="color: #008080"> 4</span> <span style="color: #008000">#</span><span style="color: #008000">python </span> <span style="color: #008080"> 5</span> <span style="color: #008000">#</span><span style="color: #008000">2017/7/7 13:21</span> <span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">__author__='Administrator'</span> <span style="color: #008080"> 7</span> <span style="color: #0000ff">import</span><span style="color: #000000"> os ,sys </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> day66 <span style="color: #0000ff">import</span><span style="color: #000000"> 多外键关联 </span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> relationship,sessionmaker </span><span style="color: #008080">10</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine </span><span style="color: #008080">11</span> Session_class=sessionmaker(bind=多外键关联.engine)<span style="color: #008000">#</span><span style="color: #008000">创建与数据库的会话 类</span> <span style="color: #008080">12</span> Session=Session_class()<span style="color: #008000">#</span><span style="color: #008000">生成实例</span> <span style="color: #008080">13</span> <span style="color: #800000">'''</span> <span style="color: #008080">14</span> <span style="color: #800000">addr1=多外键关联.Address(street="btxw",city='xy',state="fj") </span><span style="color: #008080">15</span> <span style="color: #800000">addr2=多外键关联.Address(street="jjqy",city='qz',state="fj") </span><span style="color: #008080">16</span> <span style="color: #800000">addr3=多外键关联.Address(street="ml",city='pt',state="fj") </span><span style="color: #008080">17</span> <span style="color: #008080">18</span> <span style="color: #800000">Session.add_all([addr1,addr2,addr3])#写入数据库 </span><span style="color: #008080">19</span> <span style="color: #008080">20</span> <span style="color: #800000">c1=多外键关联.Customer(name='calmyan',billing_address=addr1,shipping_address=addr1) </span><span style="color: #008080">21</span> <span style="color: #800000">c2=多外键关联.Customer(name='alex',billing_address=addr3,shipping_address=addr2) </span><span style="color: #008080">22</span> <span style="color: #800000">Session.add_all([c1,c2]) </span><span style="color: #008080">23</span> <span style="color: #800000">'''</span> <span style="color: #008080">24</span> <span style="color: #008080">25</span> <span style="color: #008000">#</span><span style="color: #008000">查询</span> <span style="color: #008080">26</span> sobj=Session.query(多外键关联.Customer).filter(多外键关联.Customer.name==<span style="color: #800000">'</span><span style="color: #800000">calmyan</span><span style="color: #800000">'</span><span style="color: #000000">).first() </span><span style="color: #008080">27</span> <span style="color: #008080">28</span> <span style="color: #0000ff">print</span><span style="color: #000000">(sobj.name,sobj.billing_address,sobj.shipping_address) </span><span style="color: #008080">29</span> <span style="color: #008080">30</span> Session.commit()
多对多外键示例:
orm_s.py
<span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!usr/bin/env python</span> <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">-*-coding:utf-8-*-</span> <span style="color: #008080"> 3</span> <span style="color: #008000">#</span><span style="color: #008000"> Author calmyan </span> <span style="color: #008080"> 4</span> <span style="color: #008000">#</span><span style="color: #008000">python </span> <span style="color: #008080"> 5</span> <span style="color: #008000">#</span><span style="color: #008000">2017/7/7 14:07</span> <span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">__author__='Administrator'</span> <span style="color: #008080"> 7</span> <span style="color: #008080"> 8</span> <span style="color: #008000">#</span><span style="color: #008000">一本书可以有多个作者，一个作者又可以出版多本书</span> <span style="color: #008080"> 9</span> <span style="color: #008080">10</span> <span style="color: #008080">11</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Table, Column, Integer,String,DATE, ForeignKey </span><span style="color: #008080">12</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> relationship </span><span style="color: #008080">13</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base </span><span style="color: #008080">14</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine </span><span style="color: #008080">15</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker </span><span style="color: #008080">16</span> <span style="color: #008080">17</span> Base =<span style="color: #000000"> declarative_base() </span><span style="color: #008080">18</span> <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000">创建第三张表,自动维护</span> <span style="color: #008080">20</span> 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, </span><span style="color: #008080">21</span> Column(<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: #008000">#</span><span style="color: #008000">关联外键,书id</span> <span style="color: #008080">22</span> Column(<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: #008000">#</span><span style="color: #008000">关联外键,作者id</span> <span style="color: #008080">23</span> <span style="color: #000000"> ) </span><span style="color: #008080">24</span> <span style="color: #008080">25</span> <span style="color: #0000ff">class</span> Book(Base):<span style="color: #008000">#</span><span style="color: #008000">书名</span> <span style="color: #008080">26</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: #008080">27</span> id = Column(Integer,primary_key=<span style="color: #000000">True) </span><span style="color: #008080">28</span> name = Column(String(64<span style="color: #000000">)) </span><span style="color: #008080">29</span> pub_date =<span style="color: #000000"> Column(DATE) </span><span style="color: #008080">30</span> 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><span style="color: #000000">) </span><span style="color: #008080">31</span> <span style="color: #008080">32</span> <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span><span style="color: #000000">(self): </span><span style="color: #008080">33</span> <span style="color: #0000ff">return</span><span style="color: #000000"> self.name </span><span style="color: #008080">34</span> <span style="color: #008080">35</span> <span style="color: #0000ff">class</span> Author(Base):<span style="color: #008000">#</span><span style="color: #008000">作者</span> <span style="color: #008080">36</span> <span style="color: #800080">__tablename__</span> = <span style="color: #800000">'</span><span style="color: #800000">authors</span><span style="color: #800000">'</span> <span style="color: #008080">37</span> id = Column(Integer, primary_key=<span style="color: #000000">True) </span><span style="color: #008080">38</span> name = Column(String(32<span style="color: #000000">)) </span><span style="color: #008080">39</span> <span style="color: #008080">40</span> <span style="color: #0000ff">def</span> <span style="color: #800080">__repr__</span><span style="color: #000000">(self): </span><span style="color: #008080">41</span> <span style="color: #0000ff">return</span><span style="color: #000000"> self.name </span><span style="color: #008080">42</span> <span style="color: #008080">43</span> <span style="color: #008000">#</span><span style="color: #008000"> 用户 密码 主机 库</span> <span style="color: #008080">44</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:root@127.0.0.1:3306/test?charset=utf8</span><span style="color: #800000">"</span>,encoding=<span style="color: #800000">"</span><span style="color: #800000">utf-8</span><span style="color: #800000">"</span>,echo=<span style="color: #000000">False) </span><span style="color: #008080">45</span> <span style="color: #008080">46</span> <span style="color: #008080">47</span> Base.metadata.create_all(engine)<span style="color: #008000">#</span><span style="color: #008000">创建表结构</span>
orm_s_aut.py
<span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!usr/bin/env python</span>
<span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000">-*-coding:utf-8-*-</span>
<span style="color: #008080"> 3</span> <span style="color: #008000">#</span><span style="color: #008000"> Author calmyan </span>
<span style="color: #008080"> 4</span> <span style="color: #008000">#</span><span style="color: #008000">python </span>
<span style="color: #008080"> 5</span> <span style="color: #008000">#</span><span style="color: #008000">2017/7/7 14:38</span>
<span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">__author__='Administrator'</span>
<span style="color: #008080"> 7</span>
<span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> day66 <span style="color: #0000ff">import</span><span style="color: #000000"> orm_s
</span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> relationship,sessionmaker
</span><span style="color: #008080">10</span> Session_class = sessionmaker(bind=orm_s.engine) <span style="color: #008000">#</span><span style="color: #008000">创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例</span>
<span style="color: #008080">11</span> s = Session_class() <span style="color: #008000">#</span><span style="color: #008000">生成session实例</span>
<span style="color: #008080">12</span> <span style="color: #800000">'''</span>
<span style="color: #008080">13</span> <span style="color: #800000">#book名
</span><span style="color: #008080">14</span> <span style="color: #800000">b1 = orm_s.Book(name="跟Alex学Python")
</span><span style="color: #008080">15</span> <span style="color: #800000">b2 = orm_s.Book(name="跟Alex学把妹")
</span><span style="color: #008080">16</span> <span style="color: #800000">b3 = orm_s.Book(name="跟Alex学装逼")
</span><span style="color: #008080">17</span> <span style="color: #800000">b4 = orm_s.Book(name="跟Alex学开车")
</span><span style="color: #008080">18</span>
<span style="color: #008080">19</span> <span style="color: #800000">#作者名
</span><span style="color: #008080">20</span> <span style="color: #800000">a1 = orm_s.Author(name="Alex")
</span><span style="color: #008080">21</span> <span style="color: #800000">a2 = orm_s.Author(name="Jack")
</span><span style="color: #008080">22</span> <span style="color: #800000">a3 = orm_s.Author(name="Rain")
</span><span style="color: #008080">23</span>
<span style="color: #008080">24</span> <span style="color: #800000">#关联关系
</span><span style="color: #008080">25</span> <span style="color: #800000">b1.authors = [a1,a2]
</span><span style="color: #008080">26</span> <span style="color: #800000">b2.authors = [a1,a2,a3]
</span><span style="color: #008080">27</span>
<span style="color: #008080">28</span> <span style="color: #800000">s.add_all([b1,b2,b3,b4,a1,a2,a3])
</span><span style="color: #008080">29</span> <span style="color: #800000">'''</span>
<span style="color: #008080">30</span>
<span style="color: #008080">31</span>
<span style="color: #008080">32</span> <span style="color: #800000">'''</span>
<span style="color: #008080">33</span> <span style="color: #800000">#查询 作者表 条件 作者名==alex
</span><span style="color: #008080">34</span> <span style="color: #800000">aut_obj=s.query(orm_s.Author).filter(orm_s.Author.name=='Alex').first()#返回一个对象
</span><span style="color: #008080">35</span> <span style="color: #800000">print(aut_obj,aut_obj.books,aut_obj.books[0])
</span><span style="color: #008080">36</span> <span style="color: #800000">'''</span>
<span style="color: #008080">37</span>
<span style="color: #008080">38</span> <span style="color: #008000">#</span><span style="color: #008000">更新</span>
<span style="color: #008080">39</span> s.query(orm_s.Book).filter(orm_s.Book.id ==2).update({<span style="color: #800000">"</span><span style="color: #800000">pub_date</span><span style="color: #800000">"</span> : <span style="color: #800000">"</span><span style="color: #800000">2017-06-07</span><span style="color: #800000">"</span><span style="color: #000000">})
</span><span style="color: #008080">40</span>
<span style="color: #008080">41</span> <span style="color: #008000">#</span><span style="color: #008000">查询 书表 条件 书id==2</span>
<span style="color: #008080">42</span> book_obj=s.query(orm_s.Book).filter(orm_s.Book.id==2<span style="color: #000000">).first()
</span><span style="color: #008080">43</span> book_obj2=s.query(orm_s.Book).filter(orm_s.Book.id==1<span style="color: #000000">).first()
</span><span style="color: #008080">44</span> <span style="color: #0000ff">print</span>(book_obj.authors,book_obj)<span style="color: #008000">#</span><span style="color: #008000">输出书的作者 书名</span>
<span style="color: #008080">45</span> <span style="color: #008000">#</span><span style="color: #008000">book_obj.authors.remove(aut_obj)#删除书中的一个作者</span>
<span style="color: #008080">46</span> <span style="color: #0000ff">print</span>(book_obj2.authors,book_obj2)<span style="color: #008000">#</span><span style="color: #008000">输出书的作者 书名</span>
<span style="color: #008080">47</span>
<span style="color: #008080">48</span> <span style="color: #008000">#</span><span style="color: #008000">删除作者,会把这个作者跟所有书的关联关系数据也自动删除</span>
<span style="color: #008080">49</span> <span style="color: #800000">'''</span>
<span style="color: #008080">50</span> <span style="color: #800000">s.delete(aut_obj)
</span><span style="color: #008080">51</span> <span style="color: #800000">'''</span>
<span style="color: #008080">52</span> book_obj=s.query(orm_s.Book).filter(orm_s.Book.id==2<span style="color: #000000">).first()
</span><span style="color: #008080">53</span> book_obj2=s.query(orm_s.Book).filter(orm_s.Book.id==1<span style="color: #000000">).first()
</span><span style="color: #008080">54</span> <span style="color: #0000ff">print</span>(book_obj.authors,book_obj)<span style="color: #008000">#</span><span style="color: #008000">输出书的作者 书名</span>
<span style="color: #008080">55</span> <span style="color: #0000ff">print</span>(book_obj2.authors,book_obj2)<span style="color: #008000">#</span><span style="color: #008000">输出书的作者 书名</span>
<span style="color: #008080">56</span>
<span style="color: #008080">57</span>
<span style="color: #008080">58</span> s.commit()
以上是python第六十六天--sqlalchemy的详细内容。更多信息请关注PHP中文网其他相关文章！
声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn