当前位置: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. <div class=" "="">
  26. <ul class="m-news-opt fix">
  27. <li class="opt-item">
  28. <a href="/sql_question-411311.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL更改root密码、连接MySQL、MySQL常用命令介绍</p></a>
  29. </li>
  30. <li class="opt-item ta-r">
  31. <a href="/sql_question-411313.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">mysql设置更改root密码、连接mysql、常用命令</p></a>
  32. </li>
  33. </ul>
  34. </span>

人气教程排行