ORM关系模型

SQLAlchemy配置

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import pymysql

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://root:123456@localhost/blog"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# SQLALCHEMY_ON_TEARDOWN | flask-sqlalchemy==2.1

db = SQLAlchemy(app)

1. 一对一(one-to-one)关系

1.1. 模型定义

class Users(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    userinfo = db.relationship('UserInfo', backref='users', cascade="all, delete,delete-orphan",uselist=False)

class UserInfo(db.Model):
    __tablename__ = 'userinfo'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50))
    users_id = db.Column(db.Integer, db.ForeignKey('users.id'))

1.2. CURD操作

# # 创建用户
# u1 = Users(name='王五')
# u2 = Users(name='赵柳')

# db.session.add(u1)
# db.session.add(u2)
# db.session.commit()

# # 创建详情
# i1 = UserInfo(email='ww@qq.com')
# i2 = UserInfo(email='zl@qq.com')

# i1.users_id = u1.id
# i2.users_id = u2.id
# db.session.add(i1)
# db.session.add(i2)
# db.session.commit()

# 根据用户查详情
# u = Users.query.first()
# print(u)
# print(u.name)
# print(u.userinfo.email)

# 根据详情查用户
# i = UserInfo.query.first()
# print(i)
# print(i.email)
# print(i.users.name)

# 删除
# u = Users.query.first()
# db.session.delete(u)
# db.session.commit()

2. 一对多(one-to-many)关系

2.1. 模型定义

# 班级
class Classs(db.Model):
    __tablename__ = 'class'
    id = db.Column(db.Integer, primary_key=True)
    cname = db.Column(db.String(50))
    # stus = db.relationship('Stus',backref="classs")
    stus = db.relationship('Stus',backref="classs",cascade="all, delete,delete-orphan")
    def __repr__(self):
        return '<Classs %r>' % self.cname
# 学员
class Stus(db.Model):
    __tablename__ = 'stu'
    id = db.Column(db.Integer, primary_key=True)
    uname = db.Column(db.String(50))
    c_id = db.Column(db.Integer, db.ForeignKey('class.id'))
    def __repr__(self):
        return '<Stus %r>' % self.uname

2.2. CURD操作

# 添加数据
# cls = Classs(cname="py15")
# db.session.add(cls)
# db.session.commit()

# stu = Stus(uname='lisi')
# stu.c_id=1
# stu2 = Stus(uname="帅帅")
# stu2.c_id =1
#
# db.session.add(stu)
# db.session.add(stu2)
# db.session.commit()

# 查询数据
# 获取所有学员及班级信息
# s = Stus.query.join(Classs).filter(Stus.c_id == Classs.id).all()
# select stu.id,stu.name,class.name from stu inner join class on stu.cid = class.id;
# print(s)
# print(s[0].classs.cname)

# 指定字段
# s = Stus.query.join(Classs).add_columns(Stus.id, Stus.uname, Classs.cname).filter(Stus.c_id == Classs.id).all()
# print(s)

# 得到两个要对象
# [(<Stus 'lisi'>, <Classs 'py15'>), (<Stus '帅帅'>, <Classs 'py15'>)]
# s = db.session.query(Stus, Classs).filter(Stus.c_id == Classs.id).all()
# print(s)

# 根据班级查询员
# c = Classs.query.get(1)
# print(c.cname)
# print(c.stus)

# # 根据学院查班级
# s = Stus.query.get(1)
# print(s.uname)
# print(s.classs.cname)

3. 多对多(many-to-many)关系

3.1. 模型定义

tags = db.Table('post_tag',
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id'))
)
#文章
class Posts(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255))
    tags = db.relationship('Tags',secondary=tags,backref='Posts')
#标签
class Tags(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    # def __repr__(self):
    #     return self.name

3.2. CURD操作

# 添加数据
# 创建文章
# p1=Posts(title='flask开发')
# p2=Posts(title='django开发')
# p3=Posts(title='web框架开发博客系统')

# # # 创建标签
# t1=Tags(name='falsk')
# t2=Tags(name='django')
# t3=Tags(name='web框架')

# # # 添加关系
# p1.tags = [t1,t3]
# p2.tags = [t2,t3]
# p3.tags = [t3]

# # 提交数据
# db.session.add(p1)
# db.session.add(p2)
# db.session.add(p3)
# db.session.add(t1)
# db.session.add(t2)
# db.session.add(t3)
# db.session.commit()


# 给已经有的数据添加 标签
# 给文章标题为PHP加上标签5号
# p = Posts(title='flask开发') #没有php你就改为 flask开发
# t1 = Tags.query.get(2)
# # 赋值关联
# p.tags = [t1]
# db.session.add(p)
# db.session.commit()
# # 新得到一条 2=>4 关联

# 查数据
# p = Posts.query.get(2)
# print(p.title)
# print(p.tags)

# t = Tags.query.get(2)
# print(t.name)
# print(t.Posts)

# 删除数据
# t = Tags.query.get(5)
# db.session.delete(t)
# db.session.commit()