Day052 SQLALchemy实现外键及其四种约束讲解

表关系：

表之间的关系存在三种：一对一、一对多、多对多。

而SQLAlchemy中的ORM也可以模拟这三种关系。

外键：

使用SQLAlchemy创建外键非常简单。在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。从表中外键的字段，必须和主表的主键字段类型保持一致。

示例代码如下：

# 主表 / 从表 # user/news class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) uname = Column(String(50),nullable=False) def __repr__(self): return "<User(uname:%s)>" % self.uname class News(Base): __tablename__ = 'news' id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) content = Column(Text,nullable=False) uid = Column(Integer,ForeignKey("user.id")) def __repr__(self): return "<News(title:%s,content=%s)>" % (self.title,self.content)

外键约束有以下几项：

1. RESTRICT：若子表中有父表对应的关联数据，删除父表对应数据，会阻止删除。默认项

2. NO ACTION：在MySQL中，同RESTRICT。

3. CASCADE：级联删除。

4. SET NULL：父表对应数据被删除，子表对应数据项会设置为NULL。

演示代码如下：

from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,\ Date,DateTime,Time,String,Text,func,or_,and_,ForeignKey from sqlalchemy.dialects.mysql import LONGTEXT from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker import random HOSTNAME = '127.0.0.1' PORT = '3306' DATABASE = 'first_sqlalchemy' USERNAME = 'root' PASSWORD = 'root' DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE) engine = create_engine(DB_URI) Base = declarative_base(engine) session = sessionmaker(engine)() # 父表/从表 # user/news class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) uname = Column(String(50),nullable=False) def __repr__(self): return "<User(uname:%s)>" % self.uname class News(Base): __tablename__ = 'news' id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) content = Column(Text,nullable=False) # uid = Column(Integer,ForeignKey("user.id",ondelete='RESTRICT')) # uid = Column(Integer,ForeignKey("user.id",ondelete='NO ACTION')) # uid = Column(Integer,ForeignKey("user.id",ondelete='CASCADE')) uid = Column(Integer,ForeignKey("user.id",ondelete='SET NULL')) def __repr__(self): return "<News(title:%s,content=%s)>" % (self.title,self.content) Base.metadata.drop_all() Base.metadata.create_all() user = User(uname='momo') session.add(user) session.commit() news1= News(title='AAA',content='123',uid=1) news2= News(title='BBB',content='456',uid=1) session.add_all([news1,news2]) session.commit() ORM关系之一对多：

mysql级别的外键，还不够爽，必须拿到一个表的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了。

SQLAlchemy提供了一个`relationship`，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。另外，可以通过`backref`来指定反向访问的属性名称。newss是指有多篇新闻。他们之间的关系是一个“一对多”的关系。

演示代码如下：

from sqlalchemy import create_engine,Column,Integer,String,Float,Enum,Boolean,DECIMAL,Text,\ Date,DateTime,Time,func,and_,or_,ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.dialects.mysql import LONGTEXT from sqlalchemy.orm import sessionmaker,relationship import random import enum from datetime import date from datetime import datetime from datetime import time #准备数据库的一堆信息 ip port user pwd 数据库的名称 按要求组织格式 HOSTNAME = '127.0.0.1' PORT = '3306' DATABASE = 'first_sqlalchemy' USERNAME = 'root' PASSWORD = 'root' #dialect+driver://username:password@host:port/database?charset=utf8 #按照上述的格式来 组织数据库信息 DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".\ format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE) #创建数据库引擎 engine = create_engine(DB_URI) #创建会话对象 session = sessionmaker(engine)() #创建ORM模型 Base = declarative_base(engine) # 主表 / 从表 # user/news class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) uname = Column(String(50),nullable=False) # newss=relationship("News") #这种写法不是最优的，通常会把它通过反向声明的方式写在“多”的那一方 def __repr__(self): return "<User(uname:%s)>" % self.uname class News(Base): __tablename__ = 'news' id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) content = Column(Text,nullable=False) #外键 uid = Column(Integer,ForeignKey("user.id")) #正向author = relationship("User") #正向 和 反向在一起 表明两个模型之间的关系 author = relationship("User",backref="newss") def __repr__(self): return "<News(title:%s,content=%s)>" % (self.title,self.content) # Base.metadata.drop_all() # Base.metadata.create_all() #需求1：查询 第一篇新闻的 作者是谁 # news= session.query(News).first() # print(news) # print(news.uid) #1 # user = session.query(User).get(news.uid) # print(user.uname) #上述的需求 能够被实现 但是太麻烦，引入relationship进行查询优化 # news = session.query(News).first() # print(news.author) # print(news.author.uname) #需求2：查询xx作者的所有文章 user = session.query(User).first() print(user.newss)

ORM关系之一对一：

在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个`uselist=False`这个参数进去。就是告诉父模型，以后引用这个从模型的时候，不再是一个列表了，而是一个对象了。示例代码如下：

方式1：

class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) uname = Column(String(50),nullable=False) extend = relationship("UserExtend",uselist=False) class UserExtend(Base): __tablename__ = 'user_extend' id = Column(Integer, primary_key=True, autoincrement=True) school = Column(String(50)) uid = Column(Integer,ForeignKey("user.id")) user = relationship("User")

方式2：（用得较多）

当然，也可以借助`sqlalchemy.orm.backref`来简化代码：

class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) uname = Column(String(50),nullable=False) class UserExtend(Base): __tablename__ = 'user_extend' id = Column(Integer, primary_key=True, autoincrement=True) school = Column(String(50)) uid = Column(Integer,ForeignKey("user.id")) user = relationship("User",backref=backref("extend",uselist=False))

演示代码如下：

from sqlalchemy import create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text from sqlalchemy import func,and_,or_,ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.dialects.mysql import LONGTEXT from sqlalchemy.orm import sessionmaker,relationship,backref from datetime import date,datetime,time #在python 3.x中 有enum模块 import enum import random #准备连接数据库基本信息 HOSTNAME = '127.0.0.1' PORT = '3306' DATABASE = 'first_sqlalchemy' USERNAME = 'root' PASSWORD = 'root' #dialect+driver://username:password@host:port/database?charset=utf8 #按照上述的格式来 组织数据库信息 DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE) #创建数据库引擎 engine = create_engine(DB_URI) Base = declarative_base(engine) session = sessionmaker(engine)() # 主表 / 从表 # user/news 1：n # user/user_extend 1：1 #表1 class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) uname = Column(String(50),nullable=False) #添加属性 优化2表查询操作 # newss =relationship("News") #这种写法不是最优的，通常会把它通过反向声明的方式写在“多”的那一方 #1:1关系的表示方式1 # extend =relationship("UserExtend",uselist=False) def __repr__(self): return "<User(uname:%s)>" % self.uname #表3 class UserExtend(Base): __tablename__ = 'user_extend' id = Column(Integer, primary_key=True, autoincrement=True) school = Column(String(50)) #外键 uid = Column(Integer,ForeignKey("user.id")) #1:1关系的表示方式1 # user = relationship("User") # 1:1关系的表示方式2 user = relationship("User",backref = backref("extend",uselist=False)) #表2 class News(Base): __tablename__ = 'news' id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) content = Column(Text,nullable=False) #SQLALchemy实现外键的方法 uid = Column(Integer,ForeignKey("user.id")) #默认删除策略为 ：RESTRICT #添加属性 优化2表查询操作 #正向 # author = relationship("User") #最终：会把正向 和反向 关系 写在一起 author = relationship("User",backref="newss") def __repr__(self): return "<News(title:%s,content=%s)>" % (self.title,self.content) #创建表 # Base.metadata.drop_all() # Base.metadata.create_all() #需求：ORM层面外键 和一对一关系实现 #好处1：添加数据 User 添加 UserExtend # user = User(uname="wangwu") # ux = UserExtend(school="京南大学") # user.extend = ux # # print(type(user.extend)) # session.add(user) # session.commit() #好处1：添加数据 UserExtend 添加 User # ux = UserExtend(school="武汉大学") # user2 = User(uname="李四") # ux.user = user2 # print(type(ux.user)) # session.add(ux) # session.commit() #好处2：查询数据 user3 = session.query(User).first() print(user3.uname) print(user3.extend.school)

ORM关系之多对多：

1. 多对多的关系需要通过一张中间表来绑定他们之间的关系。

2. 先把两个需要做多对多的模型定义出来

3. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。

4. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表对象名

代码演示如下：

from sqlalchemy import create_engine,Column,Integer,Float,Boolean,DECIMAL,Enum,\ Date,DateTime,Time,String,Text,func,or_,and_,ForeignKey,Table from sqlalchemy.dialects.mysql import LONGTEXT from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker,relationship,backref import random HOSTNAME = '127.0.0.1' PORT = '3306' DATABASE = 'first_sqlalchemy' USERNAME = 'root' PASSWORD = 'root' DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE) engine = create_engine(DB_URI) Base = declarative_base(engine) session = sessionmaker(engine)() #表3 中间表 news_tag = Table( "news_tag", Base.metadata, Column("news_id",Integer,ForeignKey("news.id"),primary_key=True), Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True) ) #表1 class News(Base): __tablename__ = 'news' id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) #产生关系 写法1 # tags = relationship("Tag",backref="newss",secondary=news_tag) def __repr__(self): return "<News(title:%s)>" % self.title #表2 class Tag(Base): __tablename__ = 'tag' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(50), nullable=False) # 产生关系 写法2 newss = relationship("News",backref="tags",secondary=news_tag) def __repr__(self): return "<Tag(name:%s)>" % self.name # 1. 先把两个需要做多对多的模型定义出来 # 2. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。 # 3. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系， # 4. 在使用relationship的时候，需要传入一个secondary=中间表对象名。 Base.metadata.drop_all() Base.metadata.create_all() #添加数据的好处 news1 = News(title="世界第一") news2 = News(title="世界第二") tag1 = Tag(name='要闻') tag2 = Tag(name='娱乐') news1.tags.append(tag1) news1.tags.append(tag2) news2.tags.append(tag1) news2.tags.append(tag2) session.add(news1) session.add(news2) session.commit() #查询数据的好处 news3 = session.query(News).first() print(news3.tags) tag = session.query(Tag).first() print(tag.newss)