Maison >développement back-end >Tutoriel Python >python jour soixante-sqlalchemy
python jour soixante-sqlalchemy
- PHP中文网original
- 2017-07-07 18:13:111312parcourir
<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>
Opérations de base de 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>
Exemple d'opération de clé étrangère
<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()
Clé étrangère Exemple d'association de clés étrangères multiples : association de clés étrangères multiples.py Clé_opération étrangère multiple.py
Association de clés étrangères multiples.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>
Plusieurs clés étrangères_opération.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()
Exemple de clé étrangère plusieurs à plusieurs :
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()
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!
Déclaration：
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn