Community Learn Tools Library Leisure
search
English
Home > Backend Development > Python Tutorial > body text

python day sixty-sqlalchemy

PHP中文网
Release： 2017-07-07 18:13:11
Original
1283 people have browsed it
<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>
Copy after login
View Code

sqlalchemy Basic operations

<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>
Copy after login
View Code

Foreign key operation example

<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()
Copy after login
View Code

Foreign key Multiple foreign key association example: Multiple foreign key association.py Multiple foreign key_operation.py

Multiple foreign key association.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>
Copy after login
View Code

Multiple foreign key_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()
Copy after login
View Code

Many-to-many foreign key example:

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>
Copy after login
View Code

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()
Copy after login
View Code

The above is the detailed content of python day sixty-sqlalchemy. For more information, please follow other related articles on the PHP Chinese website!

Related labels：
python
source：php.cn
Previous article：Detailed explanation of examples of converting database data into JSON format Next article：Detailed explanation of examples of python operating mysql
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
function_exists() cannot determine the custom function Function test () {return true;} if (function_exists ('test')) {echo "test is function...
From 2024-04-29 11:01:01
0
2
1986
How to display the mobile version of Google Chrome Hello teacher, how can I change Google Chrome into a mobile version?
From 2024-04-23 00:22:19
0
10
2148
The child window operates the parent window, but the output does not respond. The first two sentences are executable, but the last sentence cannot be implemented.
From 2024-04-19 15:37:47
0
1
1814
There is no output in the parent window document.onclick = function(){ window.opener.document.write('I am the output of the child ...
From 2024-04-18 23:52:34
0
1
1706
Where is the courseware about CSS mind mapping? Courseware
From 2024-04-16 10:10:18
0
0
1724
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template