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