SQLAlchemy中的映射关系有四种,分别是一对多,多对一,一对一,多对多
理解好映射关联关系,对后续多表查询,对象间关系非常重要。
下面可以先看看资料,如果不太理解可以看看本文,循序渐进的了解知识点。
一、映射关系理解
一对多(one to many):
因为外键(ForeignKey)始终定义在多的一方.如果relationship定义在多的一方,那就是多对一,一对多与多对一的区别在于其关联(relationship)的属性在多的一方还是一的一方,如果relationship定义在一的一方那就是一对多.
这里的例子中,一指的是Parent,一个parent有多个child:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer,primary_key = True)
children = relationship("Child",backref='parent')
class Child(Base):
__tablename__ = 'child'
id = Column(Integer,primary_key = True)
parent_id = Column(Integer,ForeignKey('parent.id'))
多对一(many to one):
这个例子中many是指parent了,意思是一个child可能有多个parent(父亲和母亲),这里的外键(child_id)和relationship(child)都定义在多(parent)的一方
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", backref="parents")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
为了建立双向关系,可以在relationship()中设置backref,Child对象就有parents属性.设置 cascade= ‘all’,可以级联删除:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer,primary_key = True)
children = relationship("Child",cascade='all',backref='parent')
def delete_parent():
session = Session()
parent = session.query(Parent).get(2)
session.delete(parent)
session.commit()
不过不设置cascade,删除parent时,其关联的chilren不会删除,只会把chilren关联的parent.id置为空,设置cascade后就可以级联删除children
一对一(one to one):
一对一就是多对一和一对多的一个特例,只需在relationship加上一个参数uselist=False替换多的一端就是一对一:
从一对多转换到一对一:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child = relationship("Child", uselist=False, backref="parent")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
多对多(many to many):
多对多关系需要一个中间关联表,通过参数secondary来指定,
from sqlalchemy import Table,Text
post_keywords = Table('post_keywords',Base.metadata,
Column('post_id',Integer,ForeignKey('posts.id')),
Column('keyword_id',Integer,ForeignKey('keywords.id'))
)
class BlogPost(Base):
__tablename__ = 'posts'
id = Column(Integer,primary_key=True)
body = Column(Text)
keywords = relationship('Keyword',secondary=post_keywords,backref='posts')
class Keyword(Base):
__tablename__ = 'keywords'
id = Column(Integer,primary_key = True)
keyword = Column(String(50),nullable=False,unique=True)
二、外键 体现的外键特性
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
import sqlalchemy
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
#创建引擎
from sqlalchemy.orm import relationship
engine=create_engine("mysql+pymysql://test:test123@47.244.28.93/pythonstudy",
encoding='utf-8', echo=True)
#生成ORM基类
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.插入数据
连接db同时填充调试数据 ,sql_insert.py,sql_foreign_insert.py
#sql_insert.py
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import INTEGER, CHAR
from sqlalchemy import create_engine, Column
def insert(new_data):
engine = create_engine("mysql+pymysql://test:test123@47.244.28.93/pythonstudy",
encoding='utf-8', echo=True)
print("创建数据库引擎")
DBSession = sessionmaker(bind=engine)
session = DBSession()
print("创建session对象")
session.add(new_data)
print("添加数据到session")
session.commit()
print("提交数据到数据库")
session.close()
print("关闭数据库连接")
#sql_foreign_insert.py
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)
直接运行sql_foreign_insert.py 就可以完成表创建,数据库数据表填充数据操作,一步到位。
数据库表数据如下
4.sqlalchemy外键操作
总结外键的优点有两个:保证数据的完整性和保证数据的一致性。那么在sqlqlchemy如何体现完整性和一致性呢?通过数据的插入和删除来体现。
- 完整性:附表插入数据时会检查外键所在字段在主表中是否存在
在phone表中插入数据:(7,Blackberry,“RIM”,3200)黑莓手机,所在公司是RIM。
#会直接报错的:pymysql.err.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails
new_phone = Phone(id=7,model="BlackBerry",company_name="RIM",price=3200)
insert(new_phone)
报错:不能添加或者更新一个子行,有一个外键关联。
因为主表company的主键,也就是phone外键关联的字段没有“RIM”,所以当phone写入数据时会检查company_name字段的值是否在company中存在。而company中不存在该值,所以不能写入。这样做就保证了数据的完整性。
- 一致性:一致性的规则有多个,具体如下引用:
外键约束对父表的含义:
在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的on update/on delete子句, InnoDB支持5种方式, 分列如下
. 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的记录。
from sqlalchemy.orm import relationship, sessionmaker
from com.wfc.python.day10sqlalchemy.外键和relationship查询.sql_foreign_models import Company
engine=create_engine("mysql+pymysql://test:test123@47.244.28.93/pythonstudy",
encoding='utf-8', echo=True)
#生成ORM基类
Base=declarative_base() #生成orm基类
Session=sessionmaker(bind=engine) #这里的Session只是一个类, 而不是实例
session=Session()
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表里的数据。这个称之为:反向查询。
正向查询
外键关联_正向查询.py
import sqlalchemy
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
#创建引擎
from sqlalchemy.orm import relationship, sessionmaker
from com.wfc.python.day10sqlalchemy.外键和relationship查询.sql_foreign_models import Company, Phone
engine=create_engine("mysql+pymysql://test:test123@47.244.28.93/pythonstudy",
encoding='utf-8', echo=True)
#生成ORM基类
Base=declarative_base() #生成orm基类
Session=sessionmaker(bind=engine) #这里的Session只是一个类, 而不是实例
session=Session()
#查询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)
反向查询
外键关联_反向查询.py
import sqlalchemy
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
#创建引擎
from sqlalchemy.orm import relationship, sessionmaker
from com.wfc.python.day10sqlalchemy.外键和relationship查询.sql_foreign_models import Company, Phone
engine=create_engine("mysql+pymysql://test:test123@47.244.28.93/pythonstudy",
encoding='utf-8', echo=True)
#生成ORM基类
Base=declarative_base() #生成orm基类
Session=sessionmaker(bind=engine) #这里的Session只是一个类, 而不是实例
session=Session()
#查询company表
company_obj = session.query(Company).filter_by(name = "Nokia").first()
#通过phone表关联的relationship的字段"backref="phone_of_company"",查询phone表数据
print(type(company_obj))
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)