前面的文章中讲解了外键的基础知识和操作，上一篇文章讲解了sqlalchemy的基本操作。前面两篇文章都是作为铺垫，为下面的文章打好基础。记得初一时第一次期中考试时考的不好，老爸安慰我说：“学习是一个循序渐进的过程”，而我的就是按照这样思路来学习数据库外键。首先是了解外键基础理论，然后是sqlalchemy基本操作，最后才到sqlalchemy操作外键。
一、sqlalchemy体现的外键特性
1.外键回顾
外键的出现是因为两张表之间需要有关联，为了保证数据的完整性和唯一性而产生的。有外键时会有两张以上的表，分为主表和附表。附表中数据往往是主表中数据的延伸，附表中有外键关联到主表的主键上。
在sqlalchemy的ORM模型中，定义表时指定主键和外键。
主键定义：在字段信息后面加上primary_key=True
name = Column(String(),primary_key=True)
外键定义：在字段后面加上Foreignkey(主表.主键)
company_name = Column(String(),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
#coding:utf-8from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE,ForeignKey #导入外键
from sqlalchemy.orm import relationship #创建关系engine = create_engine("mysql+mysqldb://root:123@localhost:3306/test",
encoding="utf-8")Base = declarative_base() #生成orm基类class Company(Base): __tablename__ = "company" name = Column(String(20),primary_key=True)
location = Column(String(20)) def __repr__(self):
return "name:{0} location:{1}".format(self.name,self.location)class Phone(Base): __tablename__ = "phone" id = Column(Integer,primary_key=True)
model = Column(String(32))
price = Column(String(32))
company_name = Column(String(32),ForeignKey("company.name"))
company = relationship("Company",backref="phone_of_company") def __repr__(self):
return "{0} model:{1},sales:{2} sales:{3} price:{4}".format(self.id,self.model,self.sales,self.price)Base.metadata.create_all(engine) #创建表
3.创建表
python sql_foreign_models.py
4.插入数据
sql_insert.py
#coding:utf-8from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import INTEGER,CHAR
from sqlalchemy import create_engine,Columndef insert(new_data): Base = declarative_base()
#修改用户名、密码、数据库的名字
engine = create_engine('mysql+mysqldb://root:123@localhost:3306/test')
print "创建数据库引擎" DBSession = sessionmaker(bind=engine)
session = DBSession()
print "创建session对象" session.add(new_data)
print "添加数据到session" session.commit()
print "提交数据到数据库" session.close()
print "关闭数据库连接"if __name__ == "__main__":
insert(User)
sql_foreign_insert.py
#coding:utf-8from sql_foreign_models import *
from sql_insert import *companys = {
"Apple":"Amercian",
"Xiaomi":"China",
"Huawei":"China",
"Sungsum":"Korea",
"Nokia":"Finland"
}
phones = (
[1,"iphoneX","Apple",8400],
[2,"xiaomi2s","Xiaomi",3299],
[3,"Huaweimate10","Huawei",3399],
[4,"SungsumS8","SungSum",4099],
[5,"NokiaLumia","Nokia",2399],
[6,"iphone4s","Apple",3800]
) for key in companys:
new_company = Company(name=key,location=companys[key])
insert(new_company)for phone in phones:
id = phone[0]
model = phone[1]
company_name = phone[2]
price = phone[3] new_phone = Phone(id=id,model=model,company_name=company_name,price=price)
insert(new_phone)
写入数据库
python sql_foreign_insert.py
5.sqlalchemy外键操作
总结外键的优点有两个：保证数据的完整性和保证数据的一致性。那么在sqlqlchemy如何体现完整性和一致性呢?通过数据的插入和删除来体现。
完整性：附表插入数据时会检查外键所在字段在主表中是否存在
在phone表中插入数据：（7，Blackberry，“RIM”，3200）黑莓手机，所在公司是RIM。
new_phone = Phone(id=,model="BlackBerry",company_name="RIM",price=)
insert(new_phone)
报错：不能添加或者更新一个子行，有一个外键关联。
因为主表company的主键，也就是phone外键关联的字段没有“RIM”，所以当phone写入数据时会检查company_name字段的值是否在company中存在。而company中不存在该值，所以不能写入。这样做就保证了数据的完整性。
一致性：一致性表现在增删改查外键时，主表相对应数据的处理。一致性的规则有多个，具体如下引用：
外键约束对父表的含义:
在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句, InnoDB支持５种方式, 分列如下 . cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录
On delete cascade从mysql3.23.50开始可用; on update cascade从mysql4.0.8开始可用 . set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
On delete set null从mysql3.23.50开始可用; on update set null从mysql4.0.8开始可用 . No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
这个是ANSI SQL-92标准,从mysql4.0.8开始支持 . Restrict方式
同no action, 都是立即检查外键约束
删除主表中的name=Sungsum的记录。
engine = create_engine('mysql+mysqldb://root:123@localhost:3306/test')DBSession = sessionmaker(bind=engine)
session = DBSession()company = session.query(Company).filter_by(name="Sungsum").first()
session.delete(company)
session.commit()
默认的外键关联的动作是 “.set null”，即主表删除数据，附表中关联的字段设为空。
除了默认的设置外，还可以选择：
1、删除主表数据，如果附表有记录则不允许删除
2、删除主表数据，如果附表有记录则一并删除
二、外键和查询
在数据结构上外键对连表查询并没有太多的帮助，但是在sqlalchemy的模型下外键对连表查询有一定的优化，那就是relationship字段，其配合外键一起使用。
在没有relationship字段时，如果想要查询xiaomi2s手机的生产公司的地址如何查询呢？分为两步走：
- 查询出phone表中xiaomi2s的company_name字段
- 通过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是反向关联，使用规则是：
- company是主表，phone是从表。查询phone表，返回phone_obj，可以通过phoen_obj.Company查询到company中外键关联的数据。查phone表返回company表里的数据。这个称之为：正向查询。
- company是主表，phone是从表。查询company表，返回company_obj，可以通过company_obj.phone_of_company查询到phone表的外键关联数据。查company表返回phone表里的数据。这个称之为：反向查询。
1.正向查询
#coding:utf-8from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sql_foreign import *#修改用户名、密码和数据库的名称为自己的
engine = create_engine("mysql+mysqldb://root:123@localhost:3306/test",)
Session_class = sessionmaker(bind=engine)
session = Session_class()#查询phone表
phone_obj = session.query(Phone).filter_by(id = 1).first()
#通过phone表关联的relationship字段"Company"查询出company表的数据
print(phone_obj.company.name)
print(phone_obj.company.location)
通过查询phone得到company表中的字段，起作用的是relationship中的Company字段。
2.反向查询
#coding:utf-8from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sql_foreign_models import *engine = create_engine("mysql+mysqldb://root:123@localhost:3306/test",)
Session_class = sessionmaker(bind=engine)
session = Session_class()#查询company表
company_obj = session.query(Company).filter_by(name = "Nokia").first()#通过phone表关联的relationship的字段"backref="phone_of_company"",查询phone表数据
print company_obj.phone_of_company[0].id
print company_obj.phone_of_company[0].model
print company_obj.phone_of_company[0].price
print company_obj.phone_of_company[0].company_name
通过查询company得到phone表中的字段，起作用的是relationship中的phone_of_compamy字段。
外键还有更多操作，比如在删除主表时附表的动作。等到下次需要使用时再补上，我相信不会等太久。
