python第六十六天–sqlalchemy

<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, F<div>本文来源gaodai.ma#com搞##代!^码@网3</div>oreignKey, 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:[email protected]: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>