当前位置：Gxlcms > 数据库问题 > SQLalchemy基本使用

SQLalchemy基本使用

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

+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql: mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector: mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle: oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

　　基本操作：

　　1.链接数据库

技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
  2. </span><span style="color: #008080"> 2</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
  3. </span><span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
  4. </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
  5. </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.base <span style="color: #0000ff">import</span><span style="color: #000000"> Engine
  6. </span><span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">ok1为数据库名称</span>
  7. <span style="color: #008080"> 7</span> engine =<span style="color: #000000"> create_engine(
  8. </span><span style="color: #008080"> 8</span>     <span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@localhost:3306/ok1?charset=utf8</span><span style="color: #800000">"</span><span style="color: #000000">,
  9. </span><span style="color: #008080"> 9</span>     max_overflow=0,  <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
  10. <span style="color: #008080">10</span>     pool_size=5,  <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
  11. <span style="color: #008080">11</span>     pool_timeout=30,  <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间，否则报错</span>
  12. <span style="color: #008080">12</span>     pool_recycle=-1, <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  13. <span style="color: #008080">13</span>     echo=True  <span style="color: #008000">#</span><span style="color: #008000"> 显示执行的所有SQL语句</span>
  14. <span style="color: #008080">14</span> )
连接数据库

　　取完数据自动关闭游标

技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
  2. <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
  4. </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
  5. </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
  6. </span><span style="color: #008080"> 6</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  7. </span><span style="color: #008080"> 7</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.base <span style="color: #0000ff">import</span><span style="color: #000000"> Engine
  8. </span><span style="color: #008080"> 8</span> 
  9. <span style="color: #008080"> 9</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/t1</span><span style="color: #800000">"</span>, max_overflow=0, pool_size=5<span style="color: #000000">)
  10. </span><span style="color: #008080">10</span> 
  11. <span style="color: #008080">11</span> 
  12. <span style="color: #008080">12</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
  13. </span><span style="color: #008080">13</span>     conn =<span style="color: #000000"> engine.contextual_connect()
  14. </span><span style="color: #008080">14</span> <span style="color: #000000">    with conn:
  15. </span><span style="color: #008080">15</span>         cur =<span style="color: #000000"> conn.execute(
  16. </span><span style="color: #008080">16</span>             <span style="color: #800000">"</span><span style="color: #800000">select * from t1</span><span style="color: #800000">"</span>
  17. <span style="color: #008080">17</span> <span style="color: #000000">        )
  18. </span><span style="color: #008080">18</span>         result =<span style="color: #000000"> cur.fetchall()
  19. </span><span style="color: #008080">19</span>         <span style="color: #0000ff">print</span><span style="color: #000000">(result)
  20. </span><span style="color: #008080">20</span> 
  21. <span style="color: #008080">21</span> 
  22. <span style="color: #008080">22</span> <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> range(20<span style="color: #000000">):
  23. </span><span style="color: #008080">23</span>     t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
  24. </span><span style="color: #008080">24</span>     t.start()
View Code

　　2. 执行原生sql语句

技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
  2. </span><span style="color: #008080"> 2</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
  3. </span><span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
  4. </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
  5. </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.base <span style="color: #0000ff">import</span><span style="color: #000000"> Engine
  6. </span><span style="color: #008080"> 6</span>  
  7. <span style="color: #008080"> 7</span> engine =<span style="color: #000000"> create_engine(
  8. </span><span style="color: #008080"> 8</span> <span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8</span><span style="color: #800000">"</span><span style="color: #000000">,
  9. </span><span style="color: #008080"> 9</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
  10. <span style="color: #008080">10</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
  11. <span style="color: #008080">11</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间，否则报错</span>
  12. <span style="color: #008080">12</span> pool_recycle=-1 <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  13. <span style="color: #008080">13</span> <span style="color: #000000">)
  14. </span><span style="color: #008080">14</span>  
  15. <span style="color: #008080">15</span>  
  16. <span style="color: #008080">16</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
  17. </span><span style="color: #008080">17</span> conn =<span style="color: #000000"> engine.raw_connection()
  18. </span><span style="color: #008080">18</span> cursor =<span style="color: #000000"> conn.cursor()
  19. </span><span style="color: #008080">19</span> <span style="color: #000000">cursor.execute(
  20. </span><span style="color: #008080">20</span> <span style="color: #800000">"</span><span style="color: #800000">select * from book</span><span style="color: #800000">"</span>
  21. <span style="color: #008080">21</span> <span style="color: #000000">)
  22. </span><span style="color: #008080">22</span> result =<span style="color: #000000"> cursor.fetchall()
  23. </span><span style="color: #008080">23</span> <span style="color: #000000">cursor.close()
  24. </span><span style="color: #008080">24</span> <span style="color: #000000">conn.close()
  25. </span><span style="color: #008080">25</span>  
  26. <span style="color: #008080">26</span>  
  27. <span style="color: #008080">27</span> <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> range(10<span style="color: #000000">):
  28. </span><span style="color: #008080">28</span> t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
  29. </span><span style="color: #008080">29</span> t.start()
View Code 技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
  2. <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
  4. </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
  5. </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
  6. </span><span style="color: #008080"> 6</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  7. </span><span style="color: #008080"> 7</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.base <span style="color: #0000ff">import</span><span style="color: #000000"> Engine
  8. </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.result <span style="color: #0000ff">import</span><span style="color: #000000"> ResultProxy
  9. </span><span style="color: #008080"> 9</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/t1</span><span style="color: #800000">"</span>, max_overflow=0, pool_size=5<span style="color: #000000">)
  10. </span><span style="color: #008080">10</span> 
  11. <span style="color: #008080">11</span> 
  12. <span style="color: #008080">12</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
  13. </span><span style="color: #008080">13</span>     cur = engine.execute(<span style="color: #800000">"</span><span style="color: #800000">select * from t1</span><span style="color: #800000">"</span><span style="color: #000000">)
  14. </span><span style="color: #008080">14</span>     result =<span style="color: #000000"> cur.fetchall()
  15. </span><span style="color: #008080">15</span> <span style="color: #000000">    cur.close()
  16. </span><span style="color: #008080">16</span>     <span style="color: #0000ff">print</span><span style="color: #000000">(result)
  17. </span><span style="color: #008080">17</span> 
  18. <span style="color: #008080">18</span> 
  19. <span style="color: #008080">19</span> <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> range(20<span style="color: #000000">):
  20. </span><span style="color: #008080">20</span>     t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
  21. </span><span style="color: #008080">21</span>     t.start()
View Code

　　3.ORM使用

 　　a.表的创建

技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
  2. <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> datetime
  4. </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
  5. </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  6. </span><span style="color: #008080"> 6</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
  7. </span><span style="color: #008080"> 7</span> 
  8. <span style="color: #008080"> 8</span> Base =<span style="color: #000000"> declarative_base()
  9. </span><span style="color: #008080"> 9</span> 
  10. <span style="color: #008080">10</span> 
  11. <span style="color: #008080">11</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Users(Base):
  12. </span><span style="color: #008080">12</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">users</span><span style="color: #800000">‘</span>
  13. <span style="color: #008080">13</span> 
  14. <span style="color: #008080">14</span>     id = Column(Integer, primary_key=<span style="color: #000000">True)
  15. </span><span style="color: #008080">15</span>     name = Column(String(32), index=True, nullable=<span style="color: #000000">False)
  16. </span><span style="color: #008080">16</span>     <span style="color: #008000">#</span><span style="color: #008000"> email = Column(String(32), unique=True)</span>
  17. <span style="color: #008080">17</span>     <span style="color: #008000">#</span><span style="color: #008000"> ctime = Column(DateTime, default=datetime.datetime.now)</span>
  18. <span style="color: #008080">18</span>     <span style="color: #008000">#</span><span style="color: #008000"> extra = Column(Text, nullable=True)</span>
  19. <span style="color: #008080">19</span> 
  20. <span style="color: #008080">20</span>     <span style="color: #800080">__table_args__</span> =<span style="color: #000000"> (
  21. </span><span style="color: #008080">21</span>         <span style="color: #008000">#</span><span style="color: #008000"> UniqueConstraint(‘id‘, ‘name‘, name=‘uix_id_name‘),</span>
  22. <span style="color: #008080">22</span>         <span style="color: #008000">#</span><span style="color: #008000"> Index(‘ix_id_name‘, ‘name‘, ‘email‘),</span>
  23. <span style="color: #008080">23</span> <span style="color: #000000">    )
  24. </span><span style="color: #008080">24</span> 
  25. <span style="color: #008080">25</span> 
  26. <span style="color: #008080">26</span> <span style="color: #0000ff">def</span><span style="color: #000000"> init_db():
  27. </span><span style="color: #008080">27</span>     <span style="color: #800000">"""</span>
  28. <span style="color: #008080">28</span> <span style="color: #800000">    根据类创建数据库表
  29. </span><span style="color: #008080">29</span> <span style="color: #800000">    :return: 
  30. </span><span style="color: #008080">30</span>     <span style="color: #800000">"""</span>
  31. <span style="color: #008080">31</span>     engine =<span style="color: #000000"> create_engine(
  32. </span><span style="color: #008080">32</span>         <span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000">"</span><span style="color: #000000">,
  33. </span><span style="color: #008080">33</span>         max_overflow=0,  <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
  34. <span style="color: #008080">34</span>         pool_size=5,  <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
  35. <span style="color: #008080">35</span>         pool_timeout=30,  <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间，否则报错</span>
  36. <span style="color: #008080">36</span>         pool_recycle=-1  <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  37. <span style="color: #008080">37</span> <span style="color: #000000">    )
  38. </span><span style="color: #008080">38</span> 
  39. <span style="color: #008080">39</span> <span style="color: #000000">    Base.metadata.create_all(engine)
  40. </span><span style="color: #008080">40</span> 
  41. <span style="color: #008080">41</span> 
  42. <span style="color: #008080">42</span> <span style="color: #0000ff">def</span><span style="color: #000000"> drop_db():
  43. </span><span style="color: #008080">43</span>     <span style="color: #800000">"""</span>
  44. <span style="color: #008080">44</span> <span style="color: #800000">    根据类删除数据库表
  45. </span><span style="color: #008080">45</span> <span style="color: #800000">    :return: 
  46. </span><span style="color: #008080">46</span>     <span style="color: #800000">"""</span>
  47. <span style="color: #008080">47</span>     engine =<span style="color: #000000"> create_engine(
  48. </span><span style="color: #008080">48</span>         <span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000">"</span><span style="color: #000000">,
  49. </span><span style="color: #008080">49</span>         max_overflow=0,  <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
  50. <span style="color: #008080">50</span>         pool_size=5,  <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
  51. <span style="color: #008080">51</span>         pool_timeout=30,  <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间，否则报错</span>
  52. <span style="color: #008080">52</span>         pool_recycle=-1  <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  53. <span style="color: #008080">53</span> <span style="color: #000000">    )
  54. </span><span style="color: #008080">54</span> 
  55. <span style="color: #008080">55</span> <span style="color: #000000">    Base.metadata.drop_all(engine)
  56. </span><span style="color: #008080">56</span> 
  57. <span style="color: #008080">57</span> <span style="color: #0000ff">if</span> <span style="color: #800080">__name__</span> == <span style="color: #800000">‘</span><span style="color: #800000">__main__</span><span style="color: #800000">‘</span><span style="color: #000000">:
  58. </span><span style="color: #008080">58</span> <span style="color: #000000">    drop_db()
  59. </span><span style="color: #008080">59</span> <span style="color: #000000">    init_db()
  60. </span><span style="color: #008080">60</span> 
  61. <span style="color: #008080">61</span> 创建单表
单表 技术分享图片 
  1. <span style="color: #008080">  1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
  2. <span style="color: #008080">  2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #008080">  3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> datetime
  4. </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
  5. </span><span style="color: #008080">  5</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  6. </span><span style="color: #008080">  6</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
  7. </span><span style="color: #008080">  7</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> relationship
  8. </span><span style="color: #008080">  8</span> 
  9. <span style="color: #008080">  9</span> Base =<span style="color: #000000"> declarative_base()
  10. </span><span style="color: #008080"> 10</span> 
  11. <span style="color: #008080"> 11</span> 
  12. <span style="color: #008080"> 12</span> <span style="color: #008000">#</span><span style="color: #008000"> ##################### 单表示例 #########################</span>
  13. <span style="color: #008080"> 13</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Users(Base):
  14. </span><span style="color: #008080"> 14</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">users</span><span style="color: #800000">‘</span>
  15. <span style="color: #008080"> 15</span> 
  16. <span style="color: #008080"> 16</span>     id = Column(Integer, primary_key=<span style="color: #000000">True)
  17. </span><span style="color: #008080"> 17</span>     name = Column(String(32), index=<span style="color: #000000">True)
  18. </span><span style="color: #008080"> 18</span>     age = Column(Integer, default=18<span style="color: #000000">)
  19. </span><span style="color: #008080"> 19</span>     email = Column(String(32), unique=<span style="color: #000000">True)
  20. </span><span style="color: #008080"> 20</span>     ctime = Column(DateTime, default=<span style="color: #000000">datetime.datetime.now)
  21. </span><span style="color: #008080"> 21</span>     extra = Column(Text, nullable=<span style="color: #000000">True)
  22. </span><span style="color: #008080"> 22</span> 
  23. <span style="color: #008080"> 23</span>     <span style="color: #800080">__table_args__</span> =<span style="color: #000000"> (
  24. </span><span style="color: #008080"> 24</span>         <span style="color: #008000">#</span><span style="color: #008000"> UniqueConstraint(‘id‘, ‘name‘, name=‘uix_id_name‘),</span>
  25. <span style="color: #008080"> 25</span>         <span style="color: #008000">#</span><span style="color: #008000"> Index(‘ix_id_name‘, ‘name‘, ‘extra‘),</span>
  26. <span style="color: #008080"> 26</span> <span style="color: #000000">    )
  27. </span><span style="color: #008080"> 27</span> 
  28. <span style="color: #008080"> 28</span> 
  29. <span style="color: #008080"> 29</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Hosts(Base):
  30. </span><span style="color: #008080"> 30</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">hosts</span><span style="color: #800000">‘</span>
  31. <span style="color: #008080"> 31</span> 
  32. <span style="color: #008080"> 32</span>     id = Column(Integer, primary_key=<span style="color: #000000">True)
  33. </span><span style="color: #008080"> 33</span>     name = Column(String(32), index=<span style="color: #000000">True)
  34. </span><span style="color: #008080"> 34</span>     ctime = Column(DateTime, default=<span style="color: #000000">datetime.datetime.now)
  35. </span><span style="color: #008080"> 35</span> 
  36. <span style="color: #008080"> 36</span> 
  37. <span style="color: #008080"> 37</span> <span style="color: #008000">#</span><span style="color: #008000"> ##################### 一对多示例 #########################</span>
  38. <span style="color: #008080"> 38</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Hobby(Base):
  39. </span><span style="color: #008080"> 39</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">hobby</span><span style="color: #800000">‘</span>
  40. <span style="color: #008080"> 40</span>     id = Column(Integer, primary_key=<span style="color: #000000">True)
  41. </span><span style="color: #008080"> 41</span>     caption = Column(String(50), default=<span style="color: #800000">‘</span><span style="color: #800000">篮球</span><span style="color: #800000">‘</span><span style="color: #000000">)
  42. </span><span style="color: #008080"> 42</span> 
  43. <span style="color: #008080"> 43</span> 
  44. <span style="color: #008080"> 44</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Person(Base):
  45. </span><span style="color: #008080"> 45</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">person</span><span style="color: #800000">‘</span>
  46. <span style="color: #008080"> 46</span>     nid = Column(Integer, primary_key=<span style="color: #000000">True)
  47. </span><span style="color: #008080"> 47</span>     name = Column(String(32), index=True, nullable=<span style="color: #000000">True)
  48. </span><span style="color: #008080"> 48</span>     hobby_id = Column(Integer, ForeignKey(<span style="color: #800000">"</span><span style="color: #800000">hobby.id</span><span style="color: #800000">"</span><span style="color: #000000">))
  49. </span><span style="color: #008080"> 49</span> 
  50. <span style="color: #008080"> 50</span>     <span style="color: #008000">#</span><span style="color: #008000"> 与生成表结构无关，仅用于查询方便</span>
  51. <span style="color: #008080"> 51</span>     hobby = relationship(<span style="color: #800000">"</span><span style="color: #800000">Hobby</span><span style="color: #800000">"</span>, backref=<span style="color: #800000">‘</span><span style="color: #800000">pers</span><span style="color: #800000">‘</span><span style="color: #000000">)
  52. </span><span style="color: #008080"> 52</span> 
  53. <span style="color: #008080"> 53</span> 
  54. <span style="color: #008080"> 54</span> <span style="color: #008000">#</span><span style="color: #008000"> ##################### 多对多示例 #########################</span>
  55. <span style="color: #008080"> 55</span> 
  56. <span style="color: #008080"> 56</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Server2Group(Base):
  57. </span><span style="color: #008080"> 57</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">server2group</span><span style="color: #800000">‘</span>
  58. <span style="color: #008080"> 58</span>     id = Column(Integer, primary_key=True, autoincrement=<span style="color: #000000">True)
  59. </span><span style="color: #008080"> 59</span>     server_id = Column(Integer, ForeignKey(<span style="color: #800000">‘</span><span style="color: #800000">server.id</span><span style="color: #800000">‘</span><span style="color: #000000">))
  60. </span><span style="color: #008080"> 60</span>     group_id = Column(Integer, ForeignKey(<span style="color: #800000">‘</span><span style="color: #800000">group.id</span><span style="color: #800000">‘</span><span style="color: #000000">))
  61. </span><span style="color: #008080"> 61</span> 
  62. <span style="color: #008080"> 62</span> 
  63. <span style="color: #008080"> 63</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Group(Base):
  64. </span><span style="color: #008080"> 64</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">group</span><span style="color: #800000">‘</span>
  65. <span style="color: #008080"> 65</span>     id = Column(Integer, primary_key=<span style="color: #000000">True)
  66. </span><span style="color: #008080"> 66</span>     name = Column(String(64), unique=True, nullable=<span style="color: #000000">False)
  67. </span><span style="color: #008080"> 67</span> 
  68. <span style="color: #008080"> 68</span>     <span style="color: #008000">#</span><span style="color: #008000"> 与生成表结构无关，仅用于查询方便</span>
  69. <span style="color: #008080"> 69</span>     servers = relationship(<span style="color: #800000">‘</span><span style="color: #800000">Server</span><span style="color: #800000">‘</span>, secondary=<span style="color: #800000">‘</span><span style="color: #800000">server2group</span><span style="color: #800000">‘</span>, backref=<span style="color: #800000">‘</span><span style="color: #800000">groups</span><span style="color: #800000">‘</span><span style="color: #000000">)
  70. </span><span style="color: #008080"> 70</span> 
  71. <span style="color: #008080"> 71</span> 
  72. <span style="color: #008080"> 72</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Server(Base):
  73. </span><span style="color: #008080"> 73</span>     <span style="color: #800080">__tablename__</span> = <span style="color: #800000">‘</span><span style="color: #800000">server</span><span style="color: #800000">‘</span>
  74. <span style="color: #008080"> 74</span> 
  75. <span style="color: #008080"> 75</span>     id = Column(Integer, primary_key=True, autoincrement=<span style="color: #000000">True)
  76. </span><span style="color: #008080"> 76</span>     hostname = Column(String(64), unique=True, nullable=<span style="color: #000000">False)
  77. </span><span style="color: #008080"> 77</span> 
  78. <span style="color: #008080"> 78</span> 
  79. <span style="color: #008080"> 79</span> <span style="color: #0000ff">def</span><span style="color: #000000"> init_db():
  80. </span><span style="color: #008080"> 80</span>     <span style="color: #800000">"""</span>
  81. <span style="color: #008080"> 81</span> <span style="color: #800000">    根据类创建数据库表
  82. </span><span style="color: #008080"> 82</span> <span style="color: #800000">    :return: 
  83. </span><span style="color: #008080"> 83</span>     <span style="color: #800000">"""</span>
  84. <span style="color: #008080"> 84</span>     engine =<span style="color: #000000"> create_engine(
  85. </span><span style="color: #008080"> 85</span>         <span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000">"</span><span style="color: #000000">,
  86. </span><span style="color: #008080"> 86</span>         max_overflow=0,  <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
  87. <span style="color: #008080"> 87</span>         pool_size=5,  <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
  88. <span style="color: #008080"> 88</span>         pool_timeout=30,  <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间，否则报错</span>
  89. <span style="color: #008080"> 89</span>         pool_recycle=-1  <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  90. <span style="color: #008080"> 90</span> <span style="color: #000000">    )
  91. </span><span style="color: #008080"> 91</span> 
  92. <span style="color: #008080"> 92</span> <span style="color: #000000">    Base.metadata.create_all(engine)
  93. </span><span style="color: #008080"> 93</span> 
  94. <span style="color: #008080"> 94</span> 
  95. <span style="color: #008080"> 95</span> <span style="color: #0000ff">def</span><span style="color: #000000"> drop_db():
  96. </span><span style="color: #008080"> 96</span>     <span style="color: #800000">"""</span>
  97. <span style="color: #008080"> 97</span> <span style="color: #800000">    根据类删除数据库表
  98. </span><span style="color: #008080"> 98</span> <span style="color: #800000">    :return: 
  99. </span><span style="color: #008080"> 99</span>     <span style="color: #800000">"""</span>
  100. <span style="color: #008080">100</span>     engine =<span style="color: #000000"> create_engine(
  101. </span><span style="color: #008080">101</span>         <span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000">"</span><span style="color: #000000">,
  102. </span><span style="color: #008080">102</span>         max_overflow=0,  <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
  103. <span style="color: #008080">103</span>         pool_size=5,  <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
  104. <span style="color: #008080">104</span>         pool_timeout=30,  <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间，否则报错</span>
  105. <span style="color: #008080">105</span>         pool_recycle=-1  <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  106. <span style="color: #008080">106</span> <span style="color: #000000">    )
  107. </span><span style="color: #008080">107</span> 
  108. <span style="color: #008080">108</span> <span style="color: #000000">    Base.metadata.drop_all(engine)
  109. </span><span style="color: #008080">109</span> 
  110. <span style="color: #008080">110</span> 
  111. <span style="color: #008080">111</span> <span style="color: #0000ff">if</span> <span style="color: #800080">__name__</span> == <span style="color: #800000">‘</span><span style="color: #800000">__main__</span><span style="color: #800000">‘</span><span style="color: #000000">:
  112. </span><span style="color: #008080">112</span> <span style="color: #000000">    drop_db()
  113. </span><span style="color: #008080">113</span> <span style="color: #000000">    init_db()
  114. </span><span style="color: #008080">114</span> 
  115. <span style="color: #008080">115</span> 创建多个表并包含Fk、M2M关系
关联表，fk,m2m

　　b.数据库的操作

技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker
  2. </span><span style="color: #008080"> 2</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  3. </span><span style="color: #008080"> 3</span> <span style="color: #0000ff">from</span> models <span style="color: #0000ff">import</span><span style="color: #000000"> Users,Person,Hobby
  4. </span><span style="color: #008080"> 4</span> engine =<span style="color: #000000"> create_engine(
  5. </span><span style="color: #008080"> 5</span>         <span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@localhost:3306/ok1?charset=utf8</span><span style="color: #800000">"</span><span style="color: #000000">,
  6. </span><span style="color: #008080"> 6</span>         max_overflow=0,  <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
  7. <span style="color: #008080"> 7</span>         pool_size=5,  <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
  8. <span style="color: #008080"> 8</span>         pool_timeout=30,  <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间，否则报错</span>
  9. <span style="color: #008080"> 9</span>         pool_recycle=-1  <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  10. <span style="color: #008080">10</span> <span style="color: #000000">)
  11. </span><span style="color: #008080">11</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
  12. </span><span style="color: #008080">12</span> <span style="color: #008000">#</span><span style="color: #008000"> 每次执行数据库操作时，都需要创建一个session，申请一个连接</span>
  13. <span style="color: #008080">13</span> session =<span style="color: #000000"> Session()
  14. </span><span style="color: #008080">14</span> <span style="color: #008000">#</span><span style="color: #008000"> ############# 执行ORM操作 #############</span>
  15. <span style="color: #008080">15</span> <span style="color: #008000">#</span><span style="color: #008000"> obj1 = Users(name="xxx")</span>
  16. <span style="color: #008080">16</span> <span style="color: #008000">#</span><span style="color: #008000"> obj2 = Users(name="yyy")</span>
  17. <span style="color: #008080">17</span> <span style="color: #008000">#</span><span style="color: #008000"> session.add(obj1)</span>
  18. <span style="color: #008080">18</span> <span style="color: #008000">#</span><span style="color: #008000"> session.add(obj2)</span>
  19. <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> 增加多个项目，用add_all</span>
  20. <span style="color: #008080">20</span> <span style="color: #008000">#</span><span style="color: #008000"> session.add_all([</span>
  21. <span style="color: #008080">21</span> <span style="color: #008000">#</span><span style="color: #008000">         Users(name="aaa"),</span>
  22. <span style="color: #008080">22</span> <span style="color: #008000">#</span><span style="color: #008000">         Users(name="bbb")</span>
  23. <span style="color: #008080">23</span> <span style="color: #008000">#</span><span style="color: #008000"> ])</span>
  24. <span style="color: #008080">24</span> <span style="color: #008000">#</span><span style="color: #008000"> 提交事务</span>
  25. <span style="color: #008080">25</span> <span style="color: #008000">#</span><span style="color: #008000"> session.commit()</span>
  26. <span style="color: #008080">26</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> and_, or_
  27. </span><span style="color: #008080">27</span> ret = session.query(Person).join(Hobby,Person.hobby_id==Hobby.id,isouter=<span style="color: #000000">True)
  28. </span><span style="color: #008080">28</span> <span style="color: #008000">#</span><span style="color: #008000"> ret = session.query(Person).join(Hobby,and_(Person.hobby_id==Hobby.id,Person.id >2),isouter=True)</span>
  29. <span style="color: #008080">29</span> <span style="color: #0000ff">print</span><span style="color: #000000">(ret)
  30. </span><span style="color: #008080">30</span> <span style="color: #008000">#</span><span style="color: #008000"> 关闭session</span>
  31. <span style="color: #008080">31</span> session.close()
简单查询 技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
  2. <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
  4. </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
  5. </span><span style="color: #008080"> 5</span> 
  6. <span style="color: #008080"> 6</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  7. </span><span style="color: #008080"> 7</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  8. </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker, relationship
  9. </span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  10. </span><span style="color: #008080">10</span> <span style="color: #0000ff">from</span> db <span style="color: #0000ff">import</span><span style="color: #000000"> Users
  11. </span><span style="color: #008080">11</span> 
  12. <span style="color: #008080">12</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/s6</span><span style="color: #800000">"</span>, max_overflow=0, pool_size=5<span style="color: #000000">)
  13. </span><span style="color: #008080">13</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
  14. </span><span style="color: #008080">14</span> 
  15. <span style="color: #008080">15</span> 
  16. <span style="color: #008080">16</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
  17. </span><span style="color: #008080">17</span>     session =<span style="color: #000000"> Session()
  18. </span><span style="color: #008080">18</span> 
  19. <span style="color: #008080">19</span>     obj1 = Users(name=<span style="color: #800000">"</span><span style="color: #800000">alex1</span><span style="color: #800000">"</span><span style="color: #000000">)
  20. </span><span style="color: #008080">20</span> <span style="color: #000000">    session.add(obj1)
  21. </span><span style="color: #008080">21</span> 
  22. <span style="color: #008080">22</span> <span style="color: #000000">    session.commit()
  23. </span><span style="color: #008080">23</span> 
  24. <span style="color: #008080">24</span> 
  25. <span style="color: #008080">25</span> <span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> range(10<span style="color: #000000">):
  26. </span><span style="color: #008080">26</span>     t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
  27. </span><span style="color: #008080">27</span> <span style="color: #000000">    t.start()
  28. </span><span style="color: #008080">28</span> 
  29. <span style="color: #008080">29</span> 多线程执行示例
多线程添加实例 技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
  2. <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
  4. </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
  5. </span><span style="color: #008080"> 5</span> 
  6. <span style="color: #008080"> 6</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  7. </span><span style="color: #008080"> 7</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  8. </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker, relationship
  9. </span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  10. </span><span style="color: #008080">10</span> <span style="color: #0000ff">from</span> sqlalchemy.sql <span style="color: #0000ff">import</span><span style="color: #000000"> text
  11. </span><span style="color: #008080">11</span> 
  12. <span style="color: #008080">12</span> <span style="color: #0000ff">from</span> db <span style="color: #0000ff">import</span><span style="color: #000000"> Users, Hosts
  13. </span><span style="color: #008080">13</span> 
  14. <span style="color: #008080">14</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/s6</span><span style="color: #800000">"</span>, max_overflow=0, pool_size=5<span style="color: #000000">)
  15. </span><span style="color: #008080">15</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
  16. </span><span style="color: #008080">16</span> 
  17. <span style="color: #008080">17</span> session =<span style="color: #000000"> Session()
  18. </span><span style="color: #008080">18</span> 
  19. <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 添加 ################</span>
  20. <span style="color: #008080">20</span> <span style="color: #800000">"""</span>
  21. <span style="color: #008080">21</span> <span style="color: #800000">obj1 = Users(name="wupeiqi")
  22. </span><span style="color: #008080">22</span> <span style="color: #800000">session.add(obj1)
  23. </span><span style="color: #008080">23</span> 
  24. <span style="color: #008080">24</span> <span style="color: #800000">session.add_all([
  25. </span><span style="color: #008080">25</span> <span style="color: #800000">    Users(name="wupeiqi"),
  26. </span><span style="color: #008080">26</span> <span style="color: #800000">    Users(name="alex"),
  27. </span><span style="color: #008080">27</span> <span style="color: #800000">    Hosts(name="c1.com"),
  28. </span><span style="color: #008080">28</span> <span style="color: #800000">])
  29. </span><span style="color: #008080">29</span> <span style="color: #800000">session.commit()
  30. </span><span style="color: #008080">30</span> <span style="color: #800000">"""</span>
  31. <span style="color: #008080">31</span> 
  32. <span style="color: #008080">32</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 删除 ################</span>
  33. <span style="color: #008080">33</span> <span style="color: #800000">"""</span>
  34. <span style="color: #008080">34</span> <span style="color: #800000">session.query(Users).filter(Users.id > 2).delete()
  35. </span><span style="color: #008080">35</span> <span style="color: #800000">session.commit()
  36. </span><span style="color: #008080">36</span> <span style="color: #800000">"""</span>
  37. <span style="color: #008080">37</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 修改 ################</span>
  38. <span style="color: #008080">38</span> <span style="color: #800000">"""</span>
  39. <span style="color: #008080">39</span> <span style="color: #800000">session.query(Users).filter(Users.id > 0).update({"name" : "099"})
  40. </span><span style="color: #008080">40</span> <span style="color: #800000">session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False)
  41. </span><span style="color: #008080">41</span> <span style="color: #800000">session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")
  42. </span><span style="color: #008080">42</span> <span style="color: #800000">session.commit()
  43. </span><span style="color: #008080">43</span> <span style="color: #800000">"""</span>
  44. <span style="color: #008080">44</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 查询 ################</span>
  45. <span style="color: #008080">45</span> <span style="color: #800000">"""</span>
  46. <span style="color: #008080">46</span> <span style="color: #800000">r1 = session.query(Users).all()
  47. </span><span style="color: #008080">47</span> <span style="color: #800000">r2 = session.query(Users.name.label(‘xx‘), Users.age).all()
  48. </span><span style="color: #008080">48</span> <span style="color: #800000">r3 = session.query(Users).filter(Users.name == "alex").all()
  49. </span><span style="color: #008080">49</span> <span style="color: #800000">r4 = session.query(Users).filter_by(name=‘alex‘).all()
  50. </span><span style="color: #008080">50</span> <span style="color: #800000">r5 = session.query(Users).filter_by(name=‘alex‘).first()
  51. </span><span style="color: #008080">51</span> <span style="color: #800000">r6 = session.query(Users).filter(text("id</span><span style="color: #800000">"""</span>
  52. <span style="color: #008080">52</span> 
  53. <span style="color: #008080">53</span> 
  54. <span style="color: #008080">54</span> <span style="color: #000000">session.close()
  55. </span><span style="color: #008080">55</span> 
  56. <span style="color: #008080">56</span> 基本增删改查示例
基本增删改查

ps：sqlalchemy session 执行 delete 时 synchronize_session 策略

技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #000000">False
  2. </span><span style="color: #008080"> 2</span> <span style="color: #000000">    不同步 session，如果被删除的 objects 已经在 session 中存在，在 session commit 或者 expire_all 之前，这些被删除的对象都存在 session 中。
  3. </span><span style="color: #008080"> 3</span> 
  4. <span style="color: #008080"> 4</span> <span style="color: #000000">    不同步可能会导致获取被删除 objects 时出错。
  5. </span><span style="color: #008080"> 5</span> 
  6. <span style="color: #008080"> 6</span> <span style="color: #000000">fatch
  7. </span><span style="color: #008080"> 7</span> <span style="color: #000000">    删除之前从 db 中匹配被删除的对象并保存在 session 中，然后再从 session 中删除，这样做是为了让 session 的对象管理 identity_map 得知被删除的对象究竟是哪些以便更新引用关系。
  8. </span><span style="color: #008080"> 8</span> 
  9. <span style="color: #008080"> 9</span> <span style="color: #000000"> evaluate
  10. </span><span style="color: #008080">10</span> <span style="color: #000000">    默认值。根据当前的 query criteria 扫描 session 中的 objects，如果不能正确执行则抛出错误，这句话也可以理解为，如果 session 中原本就没有这些被删除的 objects，扫描当然不会发生匹配，相当于匹配未正确执行。
  11. </span><span style="color: #008080">11</span> 
  12. <span style="color: #008080">12</span> 注意这里报错只会在特定 query criteria 时报错，比如 <span style="color: #0000ff">in</span> 操作。
相关参数含义 技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">　条件</span>
  2. <span style="color: #008080"> 2</span> ret = session.query(Users).filter_by(name=<span style="color: #800000">‘</span><span style="color: #800000">alex</span><span style="color: #800000">‘</span><span style="color: #000000">).all()
  3. </span><span style="color: #008080"> 3</span> ret = session.query(Users).filter(Users.id > 1, Users.name == <span style="color: #800000">‘</span><span style="color: #800000">eric</span><span style="color: #800000">‘</span><span style="color: #000000">).all()
  4. </span><span style="color: #008080"> 4</span> ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == <span style="color: #800000">‘</span><span style="color: #800000">eric</span><span style="color: #800000">‘</span><span style="color: #000000">).all()
  5. </span><span style="color: #008080"> 5</span> ret = session.query(Users).filter(Users.id.in_([1,3,4<span style="color: #000000">])).all()
  6. </span><span style="color: #008080"> 6</span> ret = session.query(Users).filter(~Users.id.in_([1,3,4<span style="color: #000000">])).all()
  7. </span><span style="color: #008080"> 7</span> ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name=<span style="color: #800000">‘</span><span style="color: #800000">eric</span><span style="color: #800000">‘</span><span style="color: #000000">))).all()
  8. </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> and_, or_
  9. </span><span style="color: #008080"> 9</span> ret = session.query(Users).filter(and_(Users.id > 3, Users.name == <span style="color: #800000">‘</span><span style="color: #800000">eric</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  10. </span><span style="color: #008080">10</span> ret = session.query(Users).filter(or_(Users.id < 2, Users.name == <span style="color: #800000">‘</span><span style="color: #800000">eric</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  11. </span><span style="color: #008080">11</span> ret =<span style="color: #000000"> session.query(Users).filter(
  12. </span><span style="color: #008080">12</span> <span style="color: #000000">    or_(
  13. </span><span style="color: #008080">13</span>         Users.id < 2<span style="color: #000000">,
  14. </span><span style="color: #008080">14</span>         and_(Users.name == <span style="color: #800000">‘</span><span style="color: #800000">eric</span><span style="color: #800000">‘</span>, Users.id > 3<span style="color: #000000">),
  15. </span><span style="color: #008080">15</span>         Users.extra != <span style="color: #800000">""</span>
  16. <span style="color: #008080">16</span> <span style="color: #000000">    )).all()
  17. </span><span style="color: #008080">17</span> 
  18. <span style="color: #008080">18</span> 
  19. <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> 通配符</span>
  20. <span style="color: #008080">20</span> ret = session.query(Users).filter(Users.name.like(<span style="color: #800000">‘</span><span style="color: #800000">e%</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  21. </span><span style="color: #008080">21</span> ret = session.query(Users).filter(~Users.name.like(<span style="color: #800000">‘</span><span style="color: #800000">e%</span><span style="color: #800000">‘</span><span style="color: #000000">)).all()
  22. </span><span style="color: #008080">22</span> 
  23. <span style="color: #008080">23</span> <span style="color: #008000">#</span><span style="color: #008000"> 限制</span>
  24. <span style="color: #008080">24</span> ret = session.query(Users)[1:2<span style="color: #000000">]
  25. </span><span style="color: #008080">25</span> 
  26. <span style="color: #008080">26</span> <span style="color: #008000">#</span><span style="color: #008000"> 排序</span>
  27. <span style="color: #008080">27</span> ret =<span style="color: #000000"> session.query(Users).order_by(Users.name.desc()).all()
  28. </span><span style="color: #008080">28</span> ret =<span style="color: #000000"> session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
  29. </span><span style="color: #008080">29</span> 
  30. <span style="color: #008080">30</span> <span style="color: #008000">#</span><span style="color: #008000"> 分组</span>
  31. <span style="color: #008080">31</span> <span style="color: #0000ff">from</span> sqlalchemy.sql <span style="color: #0000ff">import</span><span style="color: #000000"> func
  32. </span><span style="color: #008080">32</span> 
  33. <span style="color: #008080">33</span> ret =<span style="color: #000000"> session.query(Users).group_by(Users.extra).all()
  34. </span><span style="color: #008080">34</span> ret =<span style="color: #000000"> session.query(
  35. </span><span style="color: #008080">35</span> <span style="color: #000000">    func.max(Users.id),
  36. </span><span style="color: #008080">36</span> <span style="color: #000000">    func.sum(Users.id),
  37. </span><span style="color: #008080">37</span> <span style="color: #000000">    func.min(Users.id)).group_by(Users.name).all()
  38. </span><span style="color: #008080">38</span> 
  39. <span style="color: #008080">39</span> ret =<span style="color: #000000"> session.query(
  40. </span><span style="color: #008080">40</span> <span style="color: #000000">    func.max(Users.id),
  41. </span><span style="color: #008080">41</span> <span style="color: #000000">    func.sum(Users.id),
  42. </span><span style="color: #008080">42</span>     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2<span style="color: #000000">).all()
  43. </span><span style="color: #008080">43</span> 
  44. <span style="color: #008080">44</span> <span style="color: #008000">#</span><span style="color: #008000"> 连表</span>
  45. <span style="color: #008080">45</span> 
  46. <span style="color: #008080">46</span> ret = session.query(Users, Favor).filter(Users.id ==<span style="color: #000000"> Favor.nid).all()
  47. </span><span style="color: #008080">47</span> 
  48. <span style="color: #008080">48</span> ret =<span style="color: #000000"> session.query(Person).join(Favor).all()
  49. </span><span style="color: #008080">49</span> 
  50. <span style="color: #008080">50</span> ret = session.query(Person).join(Favor, isouter=<span style="color: #000000">True).all()
  51. </span><span style="color: #008080">51</span> 
  52. <span style="color: #008080">52</span> 
  53. <span style="color: #008080">53</span> <span style="color: #008000">#</span><span style="color: #008000"> 组合</span>
  54. <span style="color: #008080">54</span> q1 = session.query(Users.name).filter(Users.id > 2<span style="color: #000000">)
  55. </span><span style="color: #008080">55</span> q2 = session.query(Favor.caption).filter(Favor.nid < 2<span style="color: #000000">)
  56. </span><span style="color: #008080">56</span> ret =<span style="color: #000000"> q1.union(q2).all()
  57. </span><span style="color: #008080">57</span> 
  58. <span style="color: #008080">58</span> q1 = session.query(Users.name).filter(Users.id > 2<span style="color: #000000">)
  59. </span><span style="color: #008080">59</span> q2 = session.query(Favor.caption).filter(Favor.nid < 2<span style="color: #000000">)
  60. </span><span style="color: #008080">60</span> ret =<span style="color: #000000"> q1.union_all(q2).all()
  61. </span><span style="color: #008080">61</span> 
  62. <span style="color: #008080">62</span> 常用操作
常用操作 技术分享图片 
  1. <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
  2. <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
  4. </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
  5. </span><span style="color: #008080"> 5</span> 
  6. <span style="color: #008080"> 6</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
  7. </span><span style="color: #008080"> 7</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  8. </span><span style="color: #008080"> 8</span> <span style="color: #0000ff">from</span> sqlalchemy.orm <span style="color: #0000ff">import</span><span style="color: #000000"> sessionmaker, relationship
  9. </span><span style="color: #008080"> 9</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
  10. </span><span style="color: #008080">10</span> <span style="color: #0000ff">from</span> sqlalchemy.sql <span style="color: #0000ff">import</span><span style="color: #000000"> text
  11. </span><span style="color: #008080">11</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.result <span style="color: #0000ff">import</span><span style="color: #000000"> ResultProxy
  12. </span><span style="color: #008080">12</span> <span style="color: #0000ff">from</span> db <span style="color: #0000ff">import</span><span style="color: #000000"> Users, Hosts
  13. </span><span style="color: #008080">13</span> 
  14. <span style="color: #008080">14</span> engine = create_engine(<span style="color: #800000">"</span><span style="color: #800000">mysql+pymysql://root:123@127.0.0.1:3306/s6</span><span style="color: #800000">"</span>, max_overflow=0, pool_size=5<span style="color: #000000">)
  15. </span><span style="color: #008080">15</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
  16. </span><span style="color: #008080">16</span> 
  17. <span style="color: #008080">17</span> session =<span style="color: #000000"> Session()
  18. </span><span style="color: #008080">18</span> 
  19. <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> 查询</span>
  20. <span style="color: #008080">20</span> <span style="color: #008000">#</span><span style="color: #008000"> cursor = session.execute(‘select * from users‘)</span>
  21. <span style="color: #008080">21</span> <span style="color: #008000">#</span><span style="color: #008000"> result = cursor.fetchall()</span>
  22. <span style="color: #008080">22</span> 
  23. <span style="color: #008080">23</span> <span style="color: #008000">#</span><span style="color: #008000"> 添加</span>
  24. <span style="color: #008080">24</span> cursor = session.execute(<span style="color: #800000">‘</span><span style="color: #800000">insert into users(name) values(:value)</span><span style="color: #800000">‘</span>,params={<span style="color: #80000                    </div>
  25.                   
  26.  
  27.                     <div class=" "="">
  28.                         <ul class="m-news-opt fix">
  29.                             <li class="opt-item">
  30.                                 <a href="/sql_question-411311.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL更改root密码、连接MySQL、MySQL常用命令介绍</p></a>
  31.                             </li>
  32.                             <li class="opt-item ta-r">
  33.                                  <a href="/sql_question-411313.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">mysql设置更改root密码、连接mysql、常用命令</p></a>
  34.                             </li>
  35.                         </ul>
  36.                     </span>

人气教程排行

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