时间:2021-07-01 10:21:17 帮助过:11人阅读
基本操作:
1.链接数据库
连接数据库
- <span style="color: #008080"> 1</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
- </span><span style="color: #008080"> 2</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
- </span><span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
- </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.base <span style="color: #0000ff">import</span><span style="color: #000000"> Engine
- </span><span style="color: #008080"> 6</span> <span style="color: #008000">#</span><span style="color: #008000">ok1为数据库名称</span>
- <span style="color: #008080"> 7</span> engine =<span style="color: #000000"> create_engine(
- </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">,
- </span><span style="color: #008080"> 9</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
- <span style="color: #008080">10</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
- <span style="color: #008080">11</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间,否则报错</span>
- <span style="color: #008080">12</span> pool_recycle=-1, <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收(重置)</span>
- <span style="color: #008080">13</span> echo=True <span style="color: #008000">#</span><span style="color: #008000"> 显示执行的所有SQL语句</span>
- <span style="color: #008080">14</span> )
取完数据自动关闭游标
View Code
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
- <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
- <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
- </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
- </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
- </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
- </span><span style="color: #008080"> 8</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">)
- </span><span style="color: #008080">10</span>
- <span style="color: #008080">11</span>
- <span style="color: #008080">12</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
- </span><span style="color: #008080">13</span> conn =<span style="color: #000000"> engine.contextual_connect()
- </span><span style="color: #008080">14</span> <span style="color: #000000"> with conn:
- </span><span style="color: #008080">15</span> cur =<span style="color: #000000"> conn.execute(
- </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>
- <span style="color: #008080">17</span> <span style="color: #000000"> )
- </span><span style="color: #008080">18</span> result =<span style="color: #000000"> cur.fetchall()
- </span><span style="color: #008080">19</span> <span style="color: #0000ff">print</span><span style="color: #000000">(result)
- </span><span style="color: #008080">20</span>
- <span style="color: #008080">21</span>
- <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">):
- </span><span style="color: #008080">23</span> t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
- </span><span style="color: #008080">24</span> t.start()
2. 执行原生sql语句
View Code
- <span style="color: #008080"> 1</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
- </span><span style="color: #008080"> 2</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
- </span><span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
- </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">from</span> sqlalchemy.engine.base <span style="color: #0000ff">import</span><span style="color: #000000"> Engine
- </span><span style="color: #008080"> 6</span>
- <span style="color: #008080"> 7</span> engine =<span style="color: #000000"> create_engine(
- </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">,
- </span><span style="color: #008080"> 9</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
- <span style="color: #008080">10</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
- <span style="color: #008080">11</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间,否则报错</span>
- <span style="color: #008080">12</span> pool_recycle=-1 <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收(重置)</span>
- <span style="color: #008080">13</span> <span style="color: #000000">)
- </span><span style="color: #008080">14</span>
- <span style="color: #008080">15</span>
- <span style="color: #008080">16</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
- </span><span style="color: #008080">17</span> conn =<span style="color: #000000"> engine.raw_connection()
- </span><span style="color: #008080">18</span> cursor =<span style="color: #000000"> conn.cursor()
- </span><span style="color: #008080">19</span> <span style="color: #000000">cursor.execute(
- </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>
- <span style="color: #008080">21</span> <span style="color: #000000">)
- </span><span style="color: #008080">22</span> result =<span style="color: #000000"> cursor.fetchall()
- </span><span style="color: #008080">23</span> <span style="color: #000000">cursor.close()
- </span><span style="color: #008080">24</span> <span style="color: #000000">conn.close()
- </span><span style="color: #008080">25</span>
- <span style="color: #008080">26</span>
- <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">):
- </span><span style="color: #008080">28</span> t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
- </span><span style="color: #008080">29</span> t.start()
View Code
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
- <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
- <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
- </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">import</span><span style="color: #000000"> sqlalchemy
- </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
- </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
- </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
- </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">)
- </span><span style="color: #008080">10</span>
- <span style="color: #008080">11</span>
- <span style="color: #008080">12</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
- </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">)
- </span><span style="color: #008080">14</span> result =<span style="color: #000000"> cur.fetchall()
- </span><span style="color: #008080">15</span> <span style="color: #000000"> cur.close()
- </span><span style="color: #008080">16</span> <span style="color: #0000ff">print</span><span style="color: #000000">(result)
- </span><span style="color: #008080">17</span>
- <span style="color: #008080">18</span>
- <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">):
- </span><span style="color: #008080">20</span> t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
- </span><span style="color: #008080">21</span> t.start()
3.ORM使用
a.表的创建
单表
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
- <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
- <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> datetime
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
- </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
- </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
- </span><span style="color: #008080"> 7</span>
- <span style="color: #008080"> 8</span> Base =<span style="color: #000000"> declarative_base()
- </span><span style="color: #008080"> 9</span>
- <span style="color: #008080">10</span>
- <span style="color: #008080">11</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Users(Base):
- </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>
- <span style="color: #008080">13</span>
- <span style="color: #008080">14</span> id = Column(Integer, primary_key=<span style="color: #000000">True)
- </span><span style="color: #008080">15</span> name = Column(String(32), index=True, nullable=<span style="color: #000000">False)
- </span><span style="color: #008080">16</span> <span style="color: #008000">#</span><span style="color: #008000"> email = Column(String(32), unique=True)</span>
- <span style="color: #008080">17</span> <span style="color: #008000">#</span><span style="color: #008000"> ctime = Column(DateTime, default=datetime.datetime.now)</span>
- <span style="color: #008080">18</span> <span style="color: #008000">#</span><span style="color: #008000"> extra = Column(Text, nullable=True)</span>
- <span style="color: #008080">19</span>
- <span style="color: #008080">20</span> <span style="color: #800080">__table_args__</span> =<span style="color: #000000"> (
- </span><span style="color: #008080">21</span> <span style="color: #008000">#</span><span style="color: #008000"> UniqueConstraint(‘id‘, ‘name‘, name=‘uix_id_name‘),</span>
- <span style="color: #008080">22</span> <span style="color: #008000">#</span><span style="color: #008000"> Index(‘ix_id_name‘, ‘name‘, ‘email‘),</span>
- <span style="color: #008080">23</span> <span style="color: #000000"> )
- </span><span style="color: #008080">24</span>
- <span style="color: #008080">25</span>
- <span style="color: #008080">26</span> <span style="color: #0000ff">def</span><span style="color: #000000"> init_db():
- </span><span style="color: #008080">27</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">28</span> <span style="color: #800000"> 根据类创建数据库表
- </span><span style="color: #008080">29</span> <span style="color: #800000"> :return:
- </span><span style="color: #008080">30</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">31</span> engine =<span style="color: #000000"> create_engine(
- </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">,
- </span><span style="color: #008080">33</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
- <span style="color: #008080">34</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
- <span style="color: #008080">35</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间,否则报错</span>
- <span style="color: #008080">36</span> pool_recycle=-1 <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收(重置)</span>
- <span style="color: #008080">37</span> <span style="color: #000000"> )
- </span><span style="color: #008080">38</span>
- <span style="color: #008080">39</span> <span style="color: #000000"> Base.metadata.create_all(engine)
- </span><span style="color: #008080">40</span>
- <span style="color: #008080">41</span>
- <span style="color: #008080">42</span> <span style="color: #0000ff">def</span><span style="color: #000000"> drop_db():
- </span><span style="color: #008080">43</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">44</span> <span style="color: #800000"> 根据类删除数据库表
- </span><span style="color: #008080">45</span> <span style="color: #800000"> :return:
- </span><span style="color: #008080">46</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">47</span> engine =<span style="color: #000000"> create_engine(
- </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">,
- </span><span style="color: #008080">49</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
- <span style="color: #008080">50</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
- <span style="color: #008080">51</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间,否则报错</span>
- <span style="color: #008080">52</span> pool_recycle=-1 <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收(重置)</span>
- <span style="color: #008080">53</span> <span style="color: #000000"> )
- </span><span style="color: #008080">54</span>
- <span style="color: #008080">55</span> <span style="color: #000000"> Base.metadata.drop_all(engine)
- </span><span style="color: #008080">56</span>
- <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">:
- </span><span style="color: #008080">58</span> <span style="color: #000000"> drop_db()
- </span><span style="color: #008080">59</span> <span style="color: #000000"> init_db()
- </span><span style="color: #008080">60</span>
- <span style="color: #008080">61</span> 创建单表
关联表,fk,m2m
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
- <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
- <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> datetime
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">from</span> sqlalchemy <span style="color: #0000ff">import</span><span style="color: #000000"> create_engine
- </span><span style="color: #008080"> 5</span> <span style="color: #0000ff">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff">import</span><span style="color: #000000"> declarative_base
- </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
- </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
- </span><span style="color: #008080"> 8</span>
- <span style="color: #008080"> 9</span> Base =<span style="color: #000000"> declarative_base()
- </span><span style="color: #008080"> 10</span>
- <span style="color: #008080"> 11</span>
- <span style="color: #008080"> 12</span> <span style="color: #008000">#</span><span style="color: #008000"> ##################### 单表示例 #########################</span>
- <span style="color: #008080"> 13</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Users(Base):
- </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>
- <span style="color: #008080"> 15</span>
- <span style="color: #008080"> 16</span> id = Column(Integer, primary_key=<span style="color: #000000">True)
- </span><span style="color: #008080"> 17</span> name = Column(String(32), index=<span style="color: #000000">True)
- </span><span style="color: #008080"> 18</span> age = Column(Integer, default=18<span style="color: #000000">)
- </span><span style="color: #008080"> 19</span> email = Column(String(32), unique=<span style="color: #000000">True)
- </span><span style="color: #008080"> 20</span> ctime = Column(DateTime, default=<span style="color: #000000">datetime.datetime.now)
- </span><span style="color: #008080"> 21</span> extra = Column(Text, nullable=<span style="color: #000000">True)
- </span><span style="color: #008080"> 22</span>
- <span style="color: #008080"> 23</span> <span style="color: #800080">__table_args__</span> =<span style="color: #000000"> (
- </span><span style="color: #008080"> 24</span> <span style="color: #008000">#</span><span style="color: #008000"> UniqueConstraint(‘id‘, ‘name‘, name=‘uix_id_name‘),</span>
- <span style="color: #008080"> 25</span> <span style="color: #008000">#</span><span style="color: #008000"> Index(‘ix_id_name‘, ‘name‘, ‘extra‘),</span>
- <span style="color: #008080"> 26</span> <span style="color: #000000"> )
- </span><span style="color: #008080"> 27</span>
- <span style="color: #008080"> 28</span>
- <span style="color: #008080"> 29</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Hosts(Base):
- </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>
- <span style="color: #008080"> 31</span>
- <span style="color: #008080"> 32</span> id = Column(Integer, primary_key=<span style="color: #000000">True)
- </span><span style="color: #008080"> 33</span> name = Column(String(32), index=<span style="color: #000000">True)
- </span><span style="color: #008080"> 34</span> ctime = Column(DateTime, default=<span style="color: #000000">datetime.datetime.now)
- </span><span style="color: #008080"> 35</span>
- <span style="color: #008080"> 36</span>
- <span style="color: #008080"> 37</span> <span style="color: #008000">#</span><span style="color: #008000"> ##################### 一对多示例 #########################</span>
- <span style="color: #008080"> 38</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Hobby(Base):
- </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>
- <span style="color: #008080"> 40</span> id = Column(Integer, primary_key=<span style="color: #000000">True)
- </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">)
- </span><span style="color: #008080"> 42</span>
- <span style="color: #008080"> 43</span>
- <span style="color: #008080"> 44</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Person(Base):
- </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>
- <span style="color: #008080"> 46</span> nid = Column(Integer, primary_key=<span style="color: #000000">True)
- </span><span style="color: #008080"> 47</span> name = Column(String(32), index=True, nullable=<span style="color: #000000">True)
- </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">))
- </span><span style="color: #008080"> 49</span>
- <span style="color: #008080"> 50</span> <span style="color: #008000">#</span><span style="color: #008000"> 与生成表结构无关,仅用于查询方便</span>
- <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">)
- </span><span style="color: #008080"> 52</span>
- <span style="color: #008080"> 53</span>
- <span style="color: #008080"> 54</span> <span style="color: #008000">#</span><span style="color: #008000"> ##################### 多对多示例 #########################</span>
- <span style="color: #008080"> 55</span>
- <span style="color: #008080"> 56</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Server2Group(Base):
- </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>
- <span style="color: #008080"> 58</span> id = Column(Integer, primary_key=True, autoincrement=<span style="color: #000000">True)
- </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">))
- </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">))
- </span><span style="color: #008080"> 61</span>
- <span style="color: #008080"> 62</span>
- <span style="color: #008080"> 63</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Group(Base):
- </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>
- <span style="color: #008080"> 65</span> id = Column(Integer, primary_key=<span style="color: #000000">True)
- </span><span style="color: #008080"> 66</span> name = Column(String(64), unique=True, nullable=<span style="color: #000000">False)
- </span><span style="color: #008080"> 67</span>
- <span style="color: #008080"> 68</span> <span style="color: #008000">#</span><span style="color: #008000"> 与生成表结构无关,仅用于查询方便</span>
- <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">)
- </span><span style="color: #008080"> 70</span>
- <span style="color: #008080"> 71</span>
- <span style="color: #008080"> 72</span> <span style="color: #0000ff">class</span><span style="color: #000000"> Server(Base):
- </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>
- <span style="color: #008080"> 74</span>
- <span style="color: #008080"> 75</span> id = Column(Integer, primary_key=True, autoincrement=<span style="color: #000000">True)
- </span><span style="color: #008080"> 76</span> hostname = Column(String(64), unique=True, nullable=<span style="color: #000000">False)
- </span><span style="color: #008080"> 77</span>
- <span style="color: #008080"> 78</span>
- <span style="color: #008080"> 79</span> <span style="color: #0000ff">def</span><span style="color: #000000"> init_db():
- </span><span style="color: #008080"> 80</span> <span style="color: #800000">"""</span>
- <span style="color: #008080"> 81</span> <span style="color: #800000"> 根据类创建数据库表
- </span><span style="color: #008080"> 82</span> <span style="color: #800000"> :return:
- </span><span style="color: #008080"> 83</span> <span style="color: #800000">"""</span>
- <span style="color: #008080"> 84</span> engine =<span style="color: #000000"> create_engine(
- </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">,
- </span><span style="color: #008080"> 86</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
- <span style="color: #008080"> 87</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
- <span style="color: #008080"> 88</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间,否则报错</span>
- <span style="color: #008080"> 89</span> pool_recycle=-1 <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收(重置)</span>
- <span style="color: #008080"> 90</span> <span style="color: #000000"> )
- </span><span style="color: #008080"> 91</span>
- <span style="color: #008080"> 92</span> <span style="color: #000000"> Base.metadata.create_all(engine)
- </span><span style="color: #008080"> 93</span>
- <span style="color: #008080"> 94</span>
- <span style="color: #008080"> 95</span> <span style="color: #0000ff">def</span><span style="color: #000000"> drop_db():
- </span><span style="color: #008080"> 96</span> <span style="color: #800000">"""</span>
- <span style="color: #008080"> 97</span> <span style="color: #800000"> 根据类删除数据库表
- </span><span style="color: #008080"> 98</span> <span style="color: #800000"> :return:
- </span><span style="color: #008080"> 99</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">100</span> engine =<span style="color: #000000"> create_engine(
- </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">,
- </span><span style="color: #008080">102</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
- <span style="color: #008080">103</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
- <span style="color: #008080">104</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间,否则报错</span>
- <span style="color: #008080">105</span> pool_recycle=-1 <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收(重置)</span>
- <span style="color: #008080">106</span> <span style="color: #000000"> )
- </span><span style="color: #008080">107</span>
- <span style="color: #008080">108</span> <span style="color: #000000"> Base.metadata.drop_all(engine)
- </span><span style="color: #008080">109</span>
- <span style="color: #008080">110</span>
- <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">:
- </span><span style="color: #008080">112</span> <span style="color: #000000"> drop_db()
- </span><span style="color: #008080">113</span> <span style="color: #000000"> init_db()
- </span><span style="color: #008080">114</span>
- <span style="color: #008080">115</span> 创建多个表并包含Fk、M2M关系
b.数据库的操作
简单查询
- <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
- </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
- </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
- </span><span style="color: #008080"> 4</span> engine =<span style="color: #000000"> create_engine(
- </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">,
- </span><span style="color: #008080"> 6</span> max_overflow=0, <span style="color: #008000">#</span><span style="color: #008000"> 超过连接池大小外最多创建的连接</span>
- <span style="color: #008080"> 7</span> pool_size=5, <span style="color: #008000">#</span><span style="color: #008000"> 连接池大小</span>
- <span style="color: #008080"> 8</span> pool_timeout=30, <span style="color: #008000">#</span><span style="color: #008000"> 池中没有线程最多等待的时间,否则报错</span>
- <span style="color: #008080"> 9</span> pool_recycle=-1 <span style="color: #008000">#</span><span style="color: #008000"> 多久之后对线程池中的线程进行一次连接的回收(重置)</span>
- <span style="color: #008080">10</span> <span style="color: #000000">)
- </span><span style="color: #008080">11</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
- </span><span style="color: #008080">12</span> <span style="color: #008000">#</span><span style="color: #008000"> 每次执行数据库操作时,都需要创建一个session,申请一个连接</span>
- <span style="color: #008080">13</span> session =<span style="color: #000000"> Session()
- </span><span style="color: #008080">14</span> <span style="color: #008000">#</span><span style="color: #008000"> ############# 执行ORM操作 #############</span>
- <span style="color: #008080">15</span> <span style="color: #008000">#</span><span style="color: #008000"> obj1 = Users(name="xxx")</span>
- <span style="color: #008080">16</span> <span style="color: #008000">#</span><span style="color: #008000"> obj2 = Users(name="yyy")</span>
- <span style="color: #008080">17</span> <span style="color: #008000">#</span><span style="color: #008000"> session.add(obj1)</span>
- <span style="color: #008080">18</span> <span style="color: #008000">#</span><span style="color: #008000"> session.add(obj2)</span>
- <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> 增加多个项目,用add_all</span>
- <span style="color: #008080">20</span> <span style="color: #008000">#</span><span style="color: #008000"> session.add_all([</span>
- <span style="color: #008080">21</span> <span style="color: #008000">#</span><span style="color: #008000"> Users(name="aaa"),</span>
- <span style="color: #008080">22</span> <span style="color: #008000">#</span><span style="color: #008000"> Users(name="bbb")</span>
- <span style="color: #008080">23</span> <span style="color: #008000">#</span><span style="color: #008000"> ])</span>
- <span style="color: #008080">24</span> <span style="color: #008000">#</span><span style="color: #008000"> 提交事务</span>
- <span style="color: #008080">25</span> <span style="color: #008000">#</span><span style="color: #008000"> session.commit()</span>
- <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_
- </span><span style="color: #008080">27</span> ret = session.query(Person).join(Hobby,Person.hobby_id==Hobby.id,isouter=<span style="color: #000000">True)
- </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>
- <span style="color: #008080">29</span> <span style="color: #0000ff">print</span><span style="color: #000000">(ret)
- </span><span style="color: #008080">30</span> <span style="color: #008000">#</span><span style="color: #008000"> 关闭session</span>
- <span style="color: #008080">31</span> session.close()
多线程添加实例
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
- <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
- <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
- </span><span style="color: #008080"> 5</span>
- <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
- </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
- </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
- </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
- </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
- </span><span style="color: #008080">11</span>
- <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">)
- </span><span style="color: #008080">13</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
- </span><span style="color: #008080">14</span>
- <span style="color: #008080">15</span>
- <span style="color: #008080">16</span> <span style="color: #0000ff">def</span><span style="color: #000000"> task(arg):
- </span><span style="color: #008080">17</span> session =<span style="color: #000000"> Session()
- </span><span style="color: #008080">18</span>
- <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">)
- </span><span style="color: #008080">20</span> <span style="color: #000000"> session.add(obj1)
- </span><span style="color: #008080">21</span>
- <span style="color: #008080">22</span> <span style="color: #000000"> session.commit()
- </span><span style="color: #008080">23</span>
- <span style="color: #008080">24</span>
- <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">):
- </span><span style="color: #008080">26</span> t = threading.Thread(target=task, args=<span style="color: #000000">(i,))
- </span><span style="color: #008080">27</span> <span style="color: #000000"> t.start()
- </span><span style="color: #008080">28</span>
- <span style="color: #008080">29</span> 多线程执行示例
基本增删改查
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
- <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
- <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
- </span><span style="color: #008080"> 5</span>
- <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
- </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
- </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
- </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
- </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
- </span><span style="color: #008080">11</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
- </span><span style="color: #008080">13</span>
- <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">)
- </span><span style="color: #008080">15</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
- </span><span style="color: #008080">16</span>
- <span style="color: #008080">17</span> session =<span style="color: #000000"> Session()
- </span><span style="color: #008080">18</span>
- <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 添加 ################</span>
- <span style="color: #008080">20</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">21</span> <span style="color: #800000">obj1 = Users(name="wupeiqi")
- </span><span style="color: #008080">22</span> <span style="color: #800000">session.add(obj1)
- </span><span style="color: #008080">23</span>
- <span style="color: #008080">24</span> <span style="color: #800000">session.add_all([
- </span><span style="color: #008080">25</span> <span style="color: #800000"> Users(name="wupeiqi"),
- </span><span style="color: #008080">26</span> <span style="color: #800000"> Users(name="alex"),
- </span><span style="color: #008080">27</span> <span style="color: #800000"> Hosts(name="c1.com"),
- </span><span style="color: #008080">28</span> <span style="color: #800000">])
- </span><span style="color: #008080">29</span> <span style="color: #800000">session.commit()
- </span><span style="color: #008080">30</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">31</span>
- <span style="color: #008080">32</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 删除 ################</span>
- <span style="color: #008080">33</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">34</span> <span style="color: #800000">session.query(Users).filter(Users.id > 2).delete()
- </span><span style="color: #008080">35</span> <span style="color: #800000">session.commit()
- </span><span style="color: #008080">36</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">37</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 修改 ################</span>
- <span style="color: #008080">38</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">39</span> <span style="color: #800000">session.query(Users).filter(Users.id > 0).update({"name" : "099"})
- </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)
- </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")
- </span><span style="color: #008080">42</span> <span style="color: #800000">session.commit()
- </span><span style="color: #008080">43</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">44</span> <span style="color: #008000">#</span><span style="color: #008000"> ################ 查询 ################</span>
- <span style="color: #008080">45</span> <span style="color: #800000">"""</span>
- <span style="color: #008080">46</span> <span style="color: #800000">r1 = session.query(Users).all()
- </span><span style="color: #008080">47</span> <span style="color: #800000">r2 = session.query(Users.name.label(‘xx‘), Users.age).all()
- </span><span style="color: #008080">48</span> <span style="color: #800000">r3 = session.query(Users).filter(Users.name == "alex").all()
- </span><span style="color: #008080">49</span> <span style="color: #800000">r4 = session.query(Users).filter_by(name=‘alex‘).all()
- </span><span style="color: #008080">50</span> <span style="color: #800000">r5 = session.query(Users).filter_by(name=‘alex‘).first()
- </span><span style="color: #008080">51</span> <span style="color: #800000">r6 = session.query(Users).filter(text("id</span><span style="color: #800000">"""</span>
- <span style="color: #008080">52</span>
- <span style="color: #008080">53</span>
- <span style="color: #008080">54</span> <span style="color: #000000">session.close()
- </span><span style="color: #008080">55</span>
- <span style="color: #008080">56</span> 基本增删改查示例
ps:sqlalchemy session 执行 delete 时 synchronize_session 策略
相关参数含义
- <span style="color: #008080"> 1</span> <span style="color: #000000">False
- </span><span style="color: #008080"> 2</span> <span style="color: #000000"> 不同步 session,如果被删除的 objects 已经在 session 中存在,在 session commit 或者 expire_all 之前,这些被删除的对象都存在 session 中。
- </span><span style="color: #008080"> 3</span>
- <span style="color: #008080"> 4</span> <span style="color: #000000"> 不同步可能会导致获取被删除 objects 时出错。
- </span><span style="color: #008080"> 5</span>
- <span style="color: #008080"> 6</span> <span style="color: #000000">fatch
- </span><span style="color: #008080"> 7</span> <span style="color: #000000"> 删除之前从 db 中匹配被删除的对象并保存在 session 中,然后再从 session 中删除,这样做是为了让 session 的对象管理 identity_map 得知被删除的对象究竟是哪些以便更新引用关系。
- </span><span style="color: #008080"> 8</span>
- <span style="color: #008080"> 9</span> <span style="color: #000000"> evaluate
- </span><span style="color: #008080">10</span> <span style="color: #000000"> 默认值。根据当前的 query criteria 扫描 session 中的 objects,如果不能正确执行则抛出错误,这句话也可以理解为,如果 session 中原本就没有这些被删除的 objects,扫描当然不会发生匹配,相当于匹配未正确执行。
- </span><span style="color: #008080">11</span>
- <span style="color: #008080">12</span> 注意这里报错只会在特定 query criteria 时报错,比如 <span style="color: #0000ff">in</span> 操作。
常用操作
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000"> 条件</span>
- <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()
- </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()
- </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()
- </span><span style="color: #008080"> 5</span> ret = session.query(Users).filter(Users.id.in_([1,3,4<span style="color: #000000">])).all()
- </span><span style="color: #008080"> 6</span> ret = session.query(Users).filter(~Users.id.in_([1,3,4<span style="color: #000000">])).all()
- </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()
- </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_
- </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()
- </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()
- </span><span style="color: #008080">11</span> ret =<span style="color: #000000"> session.query(Users).filter(
- </span><span style="color: #008080">12</span> <span style="color: #000000"> or_(
- </span><span style="color: #008080">13</span> Users.id < 2<span style="color: #000000">,
- </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">),
- </span><span style="color: #008080">15</span> Users.extra != <span style="color: #800000">""</span>
- <span style="color: #008080">16</span> <span style="color: #000000"> )).all()
- </span><span style="color: #008080">17</span>
- <span style="color: #008080">18</span>
- <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> 通配符</span>
- <span style="color: #008080">20</span> 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()
- </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()
- </span><span style="color: #008080">22</span>
- <span style="color: #008080">23</span> <span style="color: #008000">#</span><span style="color: #008000"> 限制</span>
- <span style="color: #008080">24</span> ret = session.query(Users)[1:2<span style="color: #000000">]
- </span><span style="color: #008080">25</span>
- <span style="color: #008080">26</span> <span style="color: #008000">#</span><span style="color: #008000"> 排序</span>
- <span style="color: #008080">27</span> ret =<span style="color: #000000"> session.query(Users).order_by(Users.name.desc()).all()
- </span><span style="color: #008080">28</span> ret =<span style="color: #000000"> session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
- </span><span style="color: #008080">29</span>
- <span style="color: #008080">30</span> <span style="color: #008000">#</span><span style="color: #008000"> 分组</span>
- <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
- </span><span style="color: #008080">32</span>
- <span style="color: #008080">33</span> ret =<span style="color: #000000"> session.query(Users).group_by(Users.extra).all()
- </span><span style="color: #008080">34</span> ret =<span style="color: #000000"> session.query(
- </span><span style="color: #008080">35</span> <span style="color: #000000"> func.max(Users.id),
- </span><span style="color: #008080">36</span> <span style="color: #000000"> func.sum(Users.id),
- </span><span style="color: #008080">37</span> <span style="color: #000000"> func.min(Users.id)).group_by(Users.name).all()
- </span><span style="color: #008080">38</span>
- <span style="color: #008080">39</span> ret =<span style="color: #000000"> session.query(
- </span><span style="color: #008080">40</span> <span style="color: #000000"> func.max(Users.id),
- </span><span style="color: #008080">41</span> <span style="color: #000000"> func.sum(Users.id),
- </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()
- </span><span style="color: #008080">43</span>
- <span style="color: #008080">44</span> <span style="color: #008000">#</span><span style="color: #008000"> 连表</span>
- <span style="color: #008080">45</span>
- <span style="color: #008080">46</span> ret = session.query(Users, Favor).filter(Users.id ==<span style="color: #000000"> Favor.nid).all()
- </span><span style="color: #008080">47</span>
- <span style="color: #008080">48</span> ret =<span style="color: #000000"> session.query(Person).join(Favor).all()
- </span><span style="color: #008080">49</span>
- <span style="color: #008080">50</span> ret = session.query(Person).join(Favor, isouter=<span style="color: #000000">True).all()
- </span><span style="color: #008080">51</span>
- <span style="color: #008080">52</span>
- <span style="color: #008080">53</span> <span style="color: #008000">#</span><span style="color: #008000"> 组合</span>
- <span style="color: #008080">54</span> q1 = session.query(Users.name).filter(Users.id > 2<span style="color: #000000">)
- </span><span style="color: #008080">55</span> q2 = session.query(Favor.caption).filter(Favor.nid < 2<span style="color: #000000">)
- </span><span style="color: #008080">56</span> ret =<span style="color: #000000"> q1.union(q2).all()
- </span><span style="color: #008080">57</span>
- <span style="color: #008080">58</span> q1 = session.query(Users.name).filter(Users.id > 2<span style="color: #000000">)
- </span><span style="color: #008080">59</span> q2 = session.query(Favor.caption).filter(Favor.nid < 2<span style="color: #000000">)
- </span><span style="color: #008080">60</span> ret =<span style="color: #000000"> q1.union_all(q2).all()
- </span><span style="color: #008080">61</span>
- <span style="color: #008080">62</span> 常用操作
- <span style="color: #008080"> 1</span> <span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span>
- <span style="color: #008080"> 2</span> <span style="color: #008000">#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
- <span style="color: #008080"> 3</span> <span style="color: #0000ff">import</span><span style="color: #000000"> time
- </span><span style="color: #008080"> 4</span> <span style="color: #0000ff">import</span><span style="color: #000000"> threading
- </span><span style="color: #008080"> 5</span>
- <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
- </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
- </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
- </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
- </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
- </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
- </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
- </span><span style="color: #008080">13</span>
- <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">)
- </span><span style="color: #008080">15</span> Session = sessionmaker(bind=<span style="color: #000000">engine)
- </span><span style="color: #008080">16</span>
- <span style="color: #008080">17</span> session =<span style="color: #000000"> Session()
- </span><span style="color: #008080">18</span>
- <span style="color: #008080">19</span> <span style="color: #008000">#</span><span style="color: #008000"> 查询</span>
- <span style="color: #008080">20</span> <span style="color: #008000">#</span><span style="color: #008000"> cursor = session.execute(‘select * from users‘)</span>
- <span style="color: #008080">21</span> <span style="color: #008000">#</span><span style="color: #008000"> result = cursor.fetchall()</span>
- <span style="color: #008080">22</span>
- <span style="color: #008080">23</span> <span style="color: #008000">#</span><span style="color: #008000"> 添加</span>
- <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>
- <div class=" "="">
- <ul class="m-news-opt fix">
- <li class="opt-item">
- <a href="/sql_question-411311.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL更改root密码、连接MySQL、MySQL常用命令介绍</p></a>
- </li>
- <li class="opt-item ta-r">
- <a href="/sql_question-411313.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">mysql设置更改root密码、连接mysql、常用命令</p></a>
- </li>
- </ul>
- </span>