sqlalchemy外键和relationship查询

灰太狼 2021-12-10 13:43 334阅读 0赞

**原文地址：[https://www.cnblogs.com/goldsunshine/p/9269880.html][https_www.cnblogs.com_goldsunshine_p_9269880.html]**

**阅读目录**

*  [一、sqlalchemy体现的外键特性][sqlalchemy]
 *  [二、外键和查询][Link 1]

前面的文章中讲解了[外键的基础知识和操作][Link 2]，上一篇文章讲解了[sqlalchemy的基本操作][sqlalchemy 1]。前面两篇文章都是作为铺垫，为下面的文章打好基础。记得初一时第一次期中考试时考的不好，老爸安慰我说：“学习是一个循序渐进的过程”，而我的就是按照这样思路来学习数据库外键。首先是了解外键基础理论，然后是sqlalchemy基本操作，最后才到sqlalchemy操作外键。

[回到顶部][Link 3]

# 一、sqlalchemy体现的外键特性 #

## 1.外键回顾 ##

外键的出现是因为两张表之间需要有关联，为了保证数据的完整性和唯一性而产生的。有外键时会有两张以上的表，分为主表和附表。附表中数据往往是主表中数据的延伸，附表中有外键关联到主表的主键上。

在sqlalchemy的ORM模型中，定义表时指定主键和外键。

主键定义：在字段信息后面加上primary\_key=True

name = Column(String(20),primary_key=True)

外键定义：在字段后面加上Foreignkey(主表.主键)

company_name = Column(String(32),ForeignKey("company.name"))

company = relationship("Company",backref="phone_of_company")

另外在定义主键时往往还会定义一个relationship，什么作用呢？下文见分晓。

## 2.定义表 ##

定义两张表，company和phone，company中的name是主键，phone中的id是主键，并且phone中定义company\_name外键。

sql\_foreign\_models.py

![复制代码][copycode.gif]

1 #coding:utf-8
     2 
     3 from sqlalchemy import create_engine
     4 from sqlalchemy.ext.declarative import declarative_base
     5 from sqlalchemy import Column,Integer,String,DATE,ForeignKey  #导入外键
     6 from sqlalchemy.orm import  relationship  #创建关系
     7  
     8 engine = create_engine("mysql+mysqldb://root:123@localhost:3306/test",
     9                        encoding="utf-8")
    10  
    11 Base = declarative_base() #生成orm基类
    12 
    13 class Company(Base):
    14 
    15     __tablename__ = "company"
    16 
    17     name = Column(String(20),primary_key=True)
    18     location = Column(String(20))
    19  
    20     def __repr__(self):
    21         return "name:{0} location:{1}".format(self.name,self.location)
    22  
    23 class Phone(Base):
    24     
    25     __tablename__ = "phone"
    26 
    27     id = Column(Integer,primary_key=True)
    28     model = Column(String(32))
    29     price = Column(String(32))
    30     company_name = Column(String(32),ForeignKey("company.name"))
    31     company = relationship("Company",backref="phone_of_company") 
    32  
    33     def __repr__(self):
    34         return "{0} model:{1},sales:{2} sales:{3} price:{4}".format(self.id,self.model,self.sales,self.price)
    35  
    36 Base.metadata.create_all(engine) #创建表

![复制代码][copycode.gif]

## **3.创建表** ##

python sql_foreign_models.py

![1060878-20180706090031238-806351559.png][]

## 4.插入数据 ##

sql\_foreign\_insert.py

![复制代码][copycode.gif]

1 #coding:utf-8
     2 
     3 from sql_foreign_models import * 
     4 from sql_insert import *
     5 
     6 companys = {
     7            "Apple":"Amercian",
     8            "Xiaomi":"China",
     9            "Huawei":"China",
    10            "Sungsum":"Korea",
    11            "Nokia":"Finland"
    12            }
    13 
    14 phones = (
    15         [1,"iphoneX","Apple",8400],
    16         [2,"xiaomi2s","Xiaomi",3299],
    17         [3,"Huaweimate10","Huawei",3399],
    18         [4,"SungsumS8","SungSum",4099], 
    19         [5,"NokiaLumia","Nokia",2399],
    20         [6,"iphone4s","Apple",3800]
    21         )        
    22 
    23 
    24 for key in companys:
    25     
    26     new_company = Company(name=key,location=companys[key]) 
    27     insert(new_company)
    28 
    29 for phone in phones:
    30     id = phone[0]
    31     model = phone[1]
    32     company_name = phone[2]
    33     price = phone[3]
    34 
    35     new_phone = Phone(id=id,model=model,company_name=company_name,price=price)
    36     insert(new_phone)

![复制代码][copycode.gif]

写入数据库

python sql_foreign_insert.py

## 5.sqlalchemy外键操作 ##

总结外键的优点有两个：保证数据的**完整性**和保证数据的**一致性**。那么在sqlqlchemy如何体现完整性和一致性呢?通过数据的插入和删除来体现。

**完整性**：附表插入数据时会检查外键所在字段在主表中是否存在

在phone表中插入数据：（7，Blackberry，“RIM”，3200）黑莓手机，所在公司是RIM。

new_phone = Phone(id=7,model="BlackBerry",company_name="RIM",price=3200)
    insert(new_phone)

报错：不能添加或者更新一个子行，有一个外键关联。

![1060878-20180706091139270-1975990198.png][]

因为主表company的主键，也就是phone外键关联的字段没有“RIM”，所以当phone写入数据时会检查company\_name字段的值是否在company中存在。而company中不存在该值，所以不能写入。这样做就保证了数据的完整性。

**一致性**：一致性的规则有多个，具体如下引用：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>外键约束对父表的含义:</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>在父表上进行update</code> 
      <code>/</code> 
      <code>delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update</code> 
      <code>/</code> 
      <code>on delete子句, InnoDB支持５种方式, 分列如下</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>. cascade方式</code> 
     </div> 
     <div> 
      <code>在父表上update</code> 
      <code>/</code> 
      <code>delete记录时，同步update</code> 
      <code>/</code> 
      <code>delete掉子表的匹配记录</code> 
     </div> 
     <div> 
      <code>On delete cascade从mysql3.</code> 
      <code>23.50</code> 
      <code>开始可用; on update cascade从mysql4.</code> 
      <code>0.8</code> 
      <code>开始可用</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>.&nbsp;</code> 
      <code>set</code>&nbsp; 
      <code>null方式&nbsp;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>在父表上update</code> 
      <code>/</code> 
      <code>delete记录时，将子表上匹配记录的列设为null</code> 
     </div> 
     <div> 
      <code>要注意子表的外键列不能为</code> 
      <code>not</code>&nbsp; 
      <code>null</code> 
     </div> 
     <div> 
      <code>On delete&nbsp;</code> 
      <code>set</code>&nbsp; 
      <code>null从mysql3.</code> 
      <code>23.50</code> 
      <code>开始可用; on update&nbsp;</code> 
      <code>set</code>&nbsp; 
      <code>null从mysql4.</code> 
      <code>0.8</code> 
      <code>开始可用</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>. No action方式</code> 
     </div> 
     <div> 
      <code>如果子表中有匹配的记录,则不允许对父表对应候选键进行update</code> 
      <code>/</code> 
      <code>delete操作</code> 
     </div> 
     <div> 
      <code>这个是ANSI SQL</code> 
      <code>-</code> 
      <code>92</code> 
      <code>标准,从mysql4.</code> 
      <code>0.8</code> 
      <code>开始支持</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>. Restrict方式</code> 
     </div> 
     <div> 
      <code>同no action, 都是立即检查外键约束</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

删除主表中的name=Sungsum的记录。

![复制代码][copycode.gif]

1 engine = create_engine('mysql+mysqldb://root:123@localhost:3306/test')
    2 
    3 DBSession = sessionmaker(bind=engine)
    4 session = DBSession()
    5 
    6 company = session.query(Company).filter_by(name="Sungsum").first()
    7 session.delete(company)
    8 session.commit()

![复制代码][copycode.gif]

![1060878-20180706094711660-2107799414.png][]

默认的外键关联的动作是  “.set null”，即主表删除数据，附表中关联的字段设为空。

![1060878-20180706094747447-1424439352.png][]

除了默认的设置外，还可以选择：

1、删除主表数据，如果附表有记录则不允许删除

2、删除主表数据，如果附表有记录则一并删除

[回到顶部][Link 3]

# 二、外键和查询 #

在数据结构上外键对连表查询并没有太多的帮助，但是在sqlalchemy的模型下外键对连表查询有一定的优化，那就是relationship字段，其配合外键一起使用。

在没有relationship字段时，如果想要查询xiaomi2s手机的生产公司的地址如何查询呢？分为两步走：

1.  查询出phone表中xiaomi2s的company\_name字段
2.  通过company\_name字段查询company表中的location字段。（phone.company\_name==company.name）

有了relationship之后就不用分为两步走了，只需要一步就能搞定。在定义表的模型时，relationship将company和phone表关联在一起。phone表中定义：

company_name = Column(String(32),ForeignKey("company.name"))

company = relationship("Company",backref="phone_of_company")

表明将phone表和Company表联系在一起。backref是反向关联，使用规则是：

1.  查询phone表，返回phone\_obj，可以通过phoen\_obj.Company查询到company中外键关联的数据。这个称之为：正向查询。
2.  查询company表，返回company\_obj，可以通过company\_obj.phone\_of\_company查询到phone表的外键关联数据。这个称之为：反向查询。

## 1.正向查询 ##

![复制代码][copycode.gif]

1 #coding:utf-8
     2 
     3 from sqlalchemy.orm import  sessionmaker
     4 from sqlalchemy import create_engine
     5 from sql_foreign import *
     6 
     7 
     8 engine = create_engine("mysql+mysqldb://root:123@localhost:3306/test",)
     9 Session_class = sessionmaker(bind=engine)
    10 session = Session_class()
    11  
       #查询phone表
    12 phone_obj = session.query(Phone).filter_by(id = 1).first()
       #通过phone表关联的relationship字段"Company"查询出company表的数据
    13 print(phone_obj.company.name)
    14 print(phone_obj.company.location)

![复制代码][copycode.gif]

![1060878-20180706133635513-1079998263.png][]

通过查询phone得到company表中的字段，起作用的是relationship中的Company字段。

## 2.反向查询 ##

![复制代码][copycode.gif]

1 #coding:utf-8
     2 
     3 from sqlalchemy.orm import  sessionmaker
     4 from sqlalchemy import create_engine
     5 from sql_foreign_models import *
     6 
     7 
     8 engine = create_engine("mysql+mysqldb://root:123@localhost:3306/test",)
     9 Session_class = sessionmaker(bind=engine)
    10 session = Session_class()
    11 
    12 #查询company表
    13 company_obj = session.query(Company).filter_by(name = "Nokia").first()
    14 
       #通过phone表关联的relationship的字段"backref="phone_of_company"",查询phone表数据
    15 print company_obj.phone_of_company[0].id
    16 print company_obj.phone_of_company[0].model
    17 print company_obj.phone_of_company[0].price
    18 print company_obj.phone_of_company[0].company_name

![复制代码][copycode.gif]

![1060878-20180706134405095-403574604.png][]

通过查询company得到phone表中的字段，起作用的是relationship中的phone\_of\_compamy字段。

外键还有更多操作，比如在删除主表时附表的动作。等到下次需要使用时再补上，我相信不会等太久。

转载于:https://www.cnblogs.com/braveheart007/p/11022887.html

[https_www.cnblogs.com_goldsunshine_p_9269880.html]: https://www.cnblogs.com/goldsunshine/p/9269880.html
[sqlalchemy]: https://www.cnblogs.com/goldsunshine/p/9269880.html#_label0
[Link 1]: https://www.cnblogs.com/goldsunshine/p/9269880.html#_label1
[Link 2]: https://www.cnblogs.com/goldsunshine/p/9226903.html
[sqlalchemy 1]: https://www.cnblogs.com/goldsunshine/p/9265034.html
[Link 3]: https://www.cnblogs.com/goldsunshine/p/9269880.html#_labelTop
[copycode.gif]: /images/20211210/4e79471372244d57a72d905f8d295c7a.png
[1060878-20180706090031238-806351559.png]: /images/20211210/f0995cac53244d938cb8902975a269a7.png
[1060878-20180706091139270-1975990198.png]: /images/20211210/591140b87d6543bda146936a982a9439.png
[1060878-20180706094711660-2107799414.png]: /images/20211210/a0649511258c45419a652cc0336d8ea2.png
[1060878-20180706094747447-1424439352.png]: /images/20211210/fb200892265a46bc9f37a7572920a1b8.png
[1060878-20180706133635513-1079998263.png]: /images/20211210/8dde0c258b90469d8c5960933de189d9.png
[1060878-20180706134405095-403574604.png]: /images/20211210/e20b186f6cb446139e9d50fdf414b732.png