Flaskc初探七 ( 数据库模型 / SQLAlchemy / ORM)

ORM

ORM 全拼 Object-Relation Mapping. 中文意为对象-关系映射. 主要实现模型对象到关系数据库数据的映射. 和Java中的JDBC 有异曲同工之处

优点

通过改变数据库模型改变表结构
通过模型类进行数据库的增删改查操作.
只需要面向对象编程, 不需要面向数据库编写代码.
- 对数据库的操作都转化成对类属性和方法的操作.
- 不用编写各种数据库的sql语句.
实现了数据模型与数据库的解耦, 屏蔽了不同数据库操作上的差异.
- 不在关注用的是mysql、oracle...等.
- 通过简单的配置就可以轻松更换数据库, 而不需要修改代码.

缺点

相比较直接使用SQL语句操作数据库,有性能损失.
根据对象的操作转换成SQL语句,根据查询的结果转化成对象, 在映射过程中有性能损失.

关系

关系的分类

一对一^[1], 几乎不用
一对多( 或多对一 )
多对多

SQLALchemy

SQLALchemy 实际上是对数据库的抽象，让开发者不用直接和 SQL 语句打交道，而是通过 Python 对象来操作数据库，在舍弃一些性能开销的同时，换来的是开发效率的较大提升
SQLAlchemy是一个关系型数据库框架，它提供了高层的 ORM 和底层的原生数据库的操作。flask-sqlalchemy 是一个简化了 SQLAlchemy 操作的flask扩展。

一对多( 或多对一 )

01-一对多关系.png

如图, 一个角色可以有多个用户. 如果用户是管理员角色, 就不能是普通用户; 如果用户是VIP会员, 就不能是普通用户.

db_model.py


from datetime import datetime 
from application import db


# 设置基类方便管理公共字段
class BaseModel(object):
    id = db.Column(db.Integer, primary_key=True)  # 主键
    is_del = db.Column(db.Boolean, default=False)  # 默认为False,不删除/显示;当为True时,删除/不显示
    create_time = db.Column(db.DateTime, default=datetime.now)  # 记录的创建时间
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)  # 记录的更新时间

# 多继承,通过列表或者元组继承,坏处:父类属性不提示
base_db_model = (BaseModel, db.Model)


# 角色类
class Role(db.Model):
    """角色类"""
    __tablename__ = "role"

    id = db.Column(db.Integer, primary_key=True)  # 角色id
    name_role = db.Column(db.String(32))  # 角色名称
    
    # 关联引用 Role关联User
    # db.relationship("要关联的数据库模型类", lazy='dynamic')
    list_user = db.relationship("User", lazy='dynamic')


# 用户表, 通过拆包base_db_model 多继承
class User(*base_db_model):
    """用户表"""
    __tablename__ = "user"

    name_nick = db.Column(db.String(32), nullable=False)  # 昵称
    password_hash = db.Column(db.String(128), nullable=False)  # 密码
    mobile = db.Column(db.String(128), unique=True, nullable=False)  # 手机号码

    id_role = db.Column(db.Integer, db.ForeignKey("role.id"), default=2)  # 角色id

在role 指定了一个单向的关联关系, role 关联user, 那么怎么让user 关联role?如下


class User(*base_db_model):
    """用户表"""
    __tablename__ = "user"

    name_nick = db.Column(db.String(32), nullable=False)  # 昵称
    password_hash = db.Column(db.String(128), nullable=False)  # 密码
    mobile = db.Column(db.String(128), unique=True, nullable=False)  # 手机号码

    id_role = db.Column(db.Integer, db.ForeignKey("role.id"), default=2)  # 角色id

    # 添加关联关系  user关联role
    role = db.relationship("Role", lazy='dynamic')

合并,将两个单向的关联关系合并成一个双向的关系


# 角色类
class Role(db.Model):
    """角色类"""
    __tablename__ = "role"

    id = db.Column(db.Integer, primary_key=True)  # 角色id
    name_role = db.Column(db.String(32))  # 角色名称
    
    # 通过relationship 指定正向引用, backref 指定反向引用 ,构成一个双向的引用关系
    list_user = db.relationship("User", backref="role" , lazy='dynamic')


# 用户表, 通过拆包base_db_model 多继承
class User(*base_db_model):
    """用户表"""
    __tablename__ = "user"

    name_nick = db.Column(db.String(32), nullable=False)  # 昵称
    password_hash = db.Column(db.String(128), nullable=False)  # 密码
    mobile = db.Column(db.String(128), unique=True, nullable=False)  # 手机号码

    id_role = db.Column(db.Integer, db.ForeignKey("role.id"), default=2)  # 角色id

user 表

user表.png

role 表

role表.png

运行结果

02-运行结果.png

根据Debug结果

一对多, user 表(多的一方) 定义外键, role 表 (一的一方) 定义关系引用.
list_user = db.relationship("User", backref="role" , lazy='dynamic')
- relationship 指定正向关系引用, 在这里是role 向user 的方向, 既一对多的方向
- backref 指定反向关系引用, 在这里是user 向role 的方向, 既多对一的方向
- lazy 决定了什么时候SQLALchemy从数据库中加载数据
  - lazy="subquery", 默认方式, 子查询方式. 当加载查询对象时, 直接加载关联对象的数据. 如果本次查询不需要关联对象的数据或者关联对象的数据数量庞大, 就造成了浪费或者加载缓慢的问题.
  - lazy="dynamic", 动态方式. 不会直接加载关联对象的数据( users ), 只有在使用关联对象数据的时候才会进行加载(users_).
  - lazy="dynamic", 'dynamic' loaders cannot be used with many-to-one/one-to-one relationships and/or uselist=False (不能与多对一 / 一对一关系和/或 uselist = False一起使用)。

一个表的两个外键都是同一张表的主键

03-两个外键都是另一张表的主键.png

如图, 因为用户有角色, 当用户(角色) 发布新闻, 经过编辑(角色) 审核. 通过审核之后, 如果出现什么问题, 责任人是编辑, 而不是用户. 如果有以上的需求就出现了news 表的id_author 和id_charge_editor 字段的外键都是user 表的主键. 数据库是允许这样设计的, 那么SQLAlchemy 中怎样表示这样的关系?


# 一个用户可以发布多篇新闻
list_news = db.relationship("News", foreign_keys=[News.id_author], backref="author", lazy="dynamic")

# 一个编辑可以编辑多篇新闻
list_edit_news = db.relationship("News", foreign_keys=[News.id_charge_editor], backref="editor", lazy="dynamic")

和一个表中只有一个外键是另一张表中的主键类似, relationship 指定关系引用, backref 指定反向的关系引用. 区别在于foreign_keys, foreign_keys 指定关系引用作用的于哪个外键, 或者说关系引用作用于主键与哪个外键之间的关联关系.

多对多

04-多对多关系.png

如图, 用户和评论之间是一个多对多的关系, 一个用户可以给多条评论点赞, 一条评论可以被多个用户点赞. 从图上可以很容易的看出, 多对多是由两个一对多构成的, 并且有一张表存储着是多对多的关系.

数据库模型


# 评论点赞
class CommentPraise(*base_db_model):
    """评论点赞"""

    __tablename__ = "comment_praise"

    id_comment = db.Column(db.Integer, db.ForeignKey("comment.id"))  # 评论id
    id_user = db.Column(db.Integer, db.ForeignKey("user.id"))  # 用户id

# 评论表
class Comment(*base_db_model):
    """评论表"""

    __tablename__ = "comment"
    id_user = db.Column(db.Integer, db.ForeignKey("user.id"))  # 用户id
    id_news = db.Column(db.Integer, db.ForeignKey("news.id"))  # 新闻id
    id_parent = db.Column(db.Integer, db.ForeignKey("comment.id"))  # 父评论id

    content = db.Column(db.Text, nullable=False)  # 评论内容
    praise_num = db.Column(db.Integer, default=0)  # 点赞数(喜欢数)
 

# 用户表
class User(*base_db_model):
    """用户表"""
    __tablename__ = "user"

    name_nick = db.Column(db.String(32), nullable=False)  # 昵称
    password_hash = db.Column(db.String(128), nullable=False)  # 密码
    mobile = db.Column(db.String(128), unique=True, nullable=False)  # 手机号码

    id_role = db.Column(db.Integer, db.ForeignKey("role.id"), default=2)  # 角色id
    last_login = db.Column(db.String(32), default=datetime.now)  # 最后登录时间
    is_login = db.Column(db.Boolean, default=False)  # 是否登录
    gender = db.Column(db.Enum("man", "woman"), default="man")

    last_login_ip = db.Column(db.String(64))  # 最后登录ip
    avatar_url = db.Column(db.String(256))  # 头像
    signature = db.Column(db.String(512))  # 个性签名
   
    # 当前用户发布的所有评论
    list_user_comment = db.relationship("Comment", backref="user", lazy="dynamic")

    # 当前用户点赞的所有评论
    list_user_praise_comment = db.relationship("Comment", secondary="comment_praise",
                                               # 当前评论点赞的所有用户
                                               backref=db.backref('list_praise_comment_user', lazy='dynamic'),
                                               lazy="dynamic")

如上数据库模型, 在user 类中建立与Comment 的一对多关系引用.
对于多对多的关系

将关联关系放到第三张表中( comment_praise )
在user 或者 comment 中通过relationship 指定关联关系,
通过secondary 指定关联关系存放的地方

另一种写法


# 通过Table 类得到Table 类的对象association_table 
association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    # secondary = association_table Table类的对象
                    secondary=association_table,
                    backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

secondary 常见方式是使用Table 类的对象作为值, 也可以使用表名字符串作为值.

暂时未找到通过Table 作为数据库模型怎样继承基类的方式, 所以暂时算作是一种缺陷. 使用继承db.Model 的方式可以方便继承.

复杂的多对多

05-复杂的多对多.png

如图,新闻类型和新闻分类是一个多对多关系, 一个类型可以有多个分类的新闻, 一个分类可以有多个类型的新闻. 刚刚建立这个关系的时候一脸蒙圈, 关系存放在哪里? 单独摘出这一部分瞬间明白了, news 本身就是一个表, 当然可以作为type 与category 的关联关系表了, 不需要额外创建第四张表.


# 新闻类型
class NewsType(db.Model):
    """新闻类型"""
    __tablename__ = "news_type"

    id = db.Column(db.Integer, primary_key=True)  # 新闻类型id
    name_type = db.Column(db.String(32))  # 新闻类型

    list_news = db.relationship("News", backref="news_type", lazy='dynamic')

    # 当前新闻类型所属于的所有分类
    list_news_category = db.relationship('NewsCategory', secondary="news", 
                                         backref=db.backref('list_news_type', lazy='dynamic'),
                                         lazy='dynamic')


# 新闻分类
class NewsCategory(db.Model):
    """新闻分类"""

    __tablename__ = "news_category"

    id = db.Column(db.Integer, primary_key=True)  # 新闻类型id
    name_type = db.Column(db.String(32))  # 新闻类型

    list_news = db.relationship("News", backref="news_category", lazy='dynamic')

按照多对多的规则, 在NewsType 或者 NewsCategory 定义一个双向的关系引用. 这里在NewsType 定义了关系引用, 关联关系存放在News 表中.

自关联

一对多( 多对一 )

06-自关联一对多.png

一个父评论可以有多个子评论, 评论表的一对多自关联


# 评论表
class Comment(*base_db_model):
    """评论表"""

    __tablename__ = "comment"
    id_user = db.Column(db.Integer, db.ForeignKey("user.id"))  # 用户id
    id_news = db.Column(db.Integer, db.ForeignKey("news.id"))  # 新闻id
    id_parent = db.Column(db.Integer, db.ForeignKey("comment.id"))  # 父评论id

    content = db.Column(db.Text, nullable=False)  # 评论内容
    praise_num = db.Column(db.Integer, default=0)  # 点赞数(喜欢数)

    parent = db.relationship("Comment", remote_side="comment.c.id",
                             backref=db.backref('childs', lazy='dynamic'))

自关联一对多, 其本质也是一对多, 只不过两端都是一张表, 所以和一对多基本类似.

在一的一方定义关系, 在多的一方定义外键, 两端都是一张表, 所以外键和关联关系在同一个数据库模型类里.
在类中定义外键 id_parent = db.Column(db.Integer, db.ForeignKey("comment.id")) # 父评论id
在类中定义关系 parent = db.relationship("Comment", remote_side="comment.c.id",backref=
db.backref('childs', lazy='dynamic'))
通过remote_side 指定远端主键
- remote_side="comment.c.id" 等价 remote_side=[id]

多对多

07-自关联多对多.png

如图, 一个用户可以有多个粉丝, 一个用户可以被多个人关注, 用户表的多对多自关联


# 用户粉丝表
class FollowsUser(*base_db_model):
    """用户粉丝表"""

    __tablename__ = "user_follows"

    id_user = db.Column(db.Integer, db.ForeignKey("user.id"))  # 用户id
    id_follower = db.Column(db.Integer, db.ForeignKey("user.id"))  # 新闻id

# 用户表
class User(*base_db_model):
    """用户表"""
    __tablename__ = "user"

    name_nick = db.Column(db.String(32), nullable=False)  # 昵称
    password_hash = db.Column(db.String(128), nullable=False)  # 密码
    mobile = db.Column(db.String(128), unique=True, nullable=False)  # 手机号码

    id_role = db.Column(db.Integer, db.ForeignKey("role.id"), default=2)  # 角色id
    last_login = db.Column(db.String(32), default=datetime.now)  # 最后登录时间
    is_login = db.Column(db.Boolean, default=False)  # 是否登录
    gender = db.Column(db.Enum("man", "woman"), default="man")

    last_login_ip = db.Column(db.String(64))  # 最后登录ip
    avatar_url = db.Column(db.String(256))  # 头像
    signature = db.Column(db.String(512))  # 个性签名

    followers = db.relationship('User',
                                secondary="user_follows",
                                primaryjoin="user.c.id == user_follows.c.id_user",
                                secondaryjoin="user.c.id == user_follows.c.id_follower",
                                backref=db.backref('user', lazy='dynamic'),
                                lazy='dynamic')

自关联多对多, 其本质也是多对多, 只不过两端都是一张表, 所以和多对多基本类似, 同时多对多是两个一对多组成的, 并且是自关联, 所以类似于两个自关联一对多.

需要第三张表存储关联关系 user_follows
两端都是自己需要进行区分, 在自关联多对多中使用primaryjoin 和/或 secondaryjoin 进行区分.
- primaryjoin 一个SQL表达式，它将用作此子对象与父对象的主要连接，或者在多对多关系中，主对象与关联表的连接. 默认情况下，此值基于父表和子表（或关联表）的外键关系计算。
- secondaryjoin 一个SQL表达式，将用作关联表与子对象的连接。默认情况下，此值基于关联和子表的外键关系计算。

关联关系模板

多表之间

一对多


class Role(db.Model):
    """角色表"""
    __tablename__ = 'roles'

    id = db.Column(db.Integer, primary_key=True) 
    users = db.relationship('User', backref='role', lazy='dynamic')

class User(db.Model):
    """用户表"""
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)

多对多

通过Table 类


tb_student_course = db.Table('tb_student_course',
                             db.Column('student_id', db.Integer, db.ForeignKey('students.id')),
                             db.Column('course_id', db.Integer, db.ForeignKey('courses.id'))
                             )

class Student(db.Model):
    __tablename__ = "students"
    id = db.Column(db.Integer, primary_key=True) 

    courses = db.relationship('Course', secondary=tb_student_course,
                              backref=db.backref('students', lazy='dynamic'),
                              lazy='dynamic')

class Course(db.Model):
    __tablename__ = "courses"
    id = db.Column(db.Integer, primary_key=True)

**通过Model **


# 评论点赞  db SQLAlchemy的实例对象
class CommentPraise(db.Model):
    """评论点赞"""

    __tablename__ = "comment_praise"

    id_comment = db.Column(db.Integer, db.ForeignKey("comment.id"))  # 评论id
    id_user = db.Column(db.Integer, db.ForeignKey("user.id"))  # 用户id

# 评论表
class Comment(db.Model):
    """评论表"""

    __tablename__ = "comment"
    
    id = db.Column(db.Integer, primary_key=True)  
 

# 用户表
class User(db.Model):
    """用户表"""
    __tablename__ = "user"

    id = db.Column(db.Integer, primary_key=True)  

    # 当前用户点赞的所有评论
    list_user_praise_comment = db.relationship("Comment", secondary="comment_praise",
                                               # 当前评论点赞的所有用户
                                               backref=db.backref('list_praise_comment_user', lazy='dynamic'),
                                               lazy="dynamic")

自关联

一对多


class Comment(db.Model):
    """评论"""
    __tablename__ = "comments"

    id = db.Column(db.Integer, primary_key=True) 
     
    parent_id = db.Column(db.Integer, db.ForeignKey("comments.id"))
 
    parent = db.relationship("Comment", remote_side=[id],
                             backref=db.backref('childs', lazy='dynamic'))

多对多

通过Table


tb_user_follows = db.Table(
    "tb_user_follows",
    db.Column('follower_id', db.Integer, db.ForeignKey('info_user.id'), primary_key=True),  # 粉丝id
    db.Column('followed_id', db.Integer, db.ForeignKey('info_user.id'), primary_key=True)  # 被关注人的id
)

class User(db.Model):
    """用户表"""
    __tablename__ = "info_user"

    id = db.Column(db.Integer, primary_key=True)   
    
    # 用户所有的粉丝，添加了反向引用followed，代表用户都关注了哪些人
    followers = db.relationship('User',
                                secondary=tb_user_follows,
                                primaryjoin=id == tb_user_follows.c.followed_id,
                                secondaryjoin=id == tb_user_follows.c.follower_id,
                                backref=db.backref('followed', lazy='dynamic'),
                                lazy='dynamic')

通过Model



# 用户粉丝表
class FollowsUser(db.Model):
    """用户粉丝表"""

    __tablename__ = "user_follows"

    id_user = db.Column(db.Integer, db.ForeignKey("user.id"))  # 用户id
    id_follower = db.Column(db.Integer, db.ForeignKey("user.id"))  # 新闻id

# 用户表
class User(db.Model):
    """用户表"""
    __tablename__ = "user"
 
    id = db.Column(db.Integer, primary_key=True)    

    followers = db.relationship('User', secondary="user_follows",
                                primaryjoin="user.c.id == user_follows.c.id_user",
                                secondaryjoin="user.c.id == user_follows.c.id_follower",
                                backref=db.backref('user', lazy='dynamic'),
                                lazy='dynamic')

到此结 DragonFangQy 2018.7.12

一对一 ↩

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 205,132评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,802评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,566评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,858评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,867评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,695评论 1赞 282
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,064评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,705评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 42,915评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,677评论 2赞 323
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,796评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,432评论 4赞 322
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,041评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,992评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,223评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,185评论 2赞 352
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,535评论 2赞 343