Heim >Backend-Entwicklung >Python-Tutorial >Python-Tag Sechzig-SQL-Alchemie
Python-Tag Sechzig-SQL-Alchemie
- PHP中文网Original
- 2017-07-07 18:13:111288Durchsuche
<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>
Grundoperationen der SQLalchemie
<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>
Beispiel für eine Fremdschlüsseloperation
<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()
Fremdschlüssel-Beispiel für mehrere Fremdschlüsselzuordnungen: Mehrere Fremdschlüsselassoziationen.py Mehrere Fremdschlüssel_operation.py
Mehrere Fremdschlüsselassoziation.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>
Mehrere Fremdschlüssel_operation.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()
Many-to-many-Fremdschlüsselbeispiel:
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()
Das obige ist der detaillierte Inhalt vonPython-Tag Sechzig-SQL-Alchemie. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn