11.Flask:数据库操作

Flask(数据库操作 十一)

查询

查询全部

users = User.query.all()

查询第一个

users = User.query.first()

通过主键获取

users = User.query.get(1)

多个主键

users = User.query.get((1,5))
users = User.query.get({"id":1,"project_id":3})

filter_by筛选

只能处理相等

admin = User.query.filter_by(username='admin').first()

filter筛选

User.query.filter(User.email.endswith("@qq.com")).all()

from sqlalchemy.sql.operators import ColumnOperators

支持的字段操作：ColumnOperators:/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/operators.py.ColumnOperators

排序

User.query.order_by(User.username.desc()).all()

现在返回用户的数量

User.query.order_by(User.username.desc()).limit(1).all()

未找到

使用first_or_404没找到就用abort()和异常处理机制配合

删除

db.session.delete(me)
db.session.commit()

更新

user = User.query.get(1)
user.username = "abc"
db.session.add(user)
db.session.commit()

外键关联

多对一

例子:

多个学生 一个班级

写法1

class Project(db.Model):
    # __tablename__ = 'project'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))
    modules = db.relationship('Module', backref='module_project')


class Module(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'))

写法2

class Project(db.Model):
    # __tablename__ = 'project'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))
    modules = db.relationship('Module', back_populates='module_project')  # 数据库中不会有新字段


class Module(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'))
    module_project = db.relationship('Project', back_populates='modules')

backref vs back_populates

• back_populates 需要双向显示说明；backref只需要指名一边隐式声明
• back_populates 更麻烦；backref 更简单
• back_populates 可读性强，可以表明各个表之间的关系

一对一

例子：

一个人一个身份证号

class Project(db.Model):
    modules = db.relationship("Module", back_populates='project', uselist=False)


class Module(db.Model):
    project = db.relationship("Module", back_populates='modules', uselist=False)

多对多

例子：

1个学生 多门课程

1门课程 多个学生

xuanke = db.Table('xuanke',
                  db.Column('user_id'),
                  db.Integer,
                  db.ForeignKey('user.id'),
                  db.Column('subject_id'),
                  db.Integer,
                  db.ForeignKey('subject.id')
                  )


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    subject = db.relationship('Subject', secondary=xuanke, backref=db.backref('users', lazy='dynamic'))


class Subject(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))

插入数据

@app.route('/insert')
def insert():
    user1 = User(name='张三')
    user2 = User(name='李四')
    user3 = User(name='王五')

    subject1 = Subject(name='自动化测试')
    subject2 = Subject(name='测试开发')
    subject3 = Subject(name='Java')

    user1.subject.append(subject1)
    user1.subject.append(subject2)

    db.session.add_all([user1, user2, user3, subject1, subject2, subject3])
    db.session.commit()
    return 'OK'

查找数据

@app.route('/select')
def select():
    user = User.query.get(1)
    s = user.subject
    print(s)
    return 'OK'

自引用

class Follower(db.Model):
    # 粉丝ID
    fensi_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    # 关注者ID
    guanzhuzhe_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)
    sujects = db.relationship('Xuanke', back_populates='student')
    fensis = db.relationship('Follower', foreign_keys=[Follower.guanzhuzhe_id], backref='guanzhuzhe')
    guanzhuzhes = db.relationship('Follower', foreign_keys=[Follower.fensi_id], backref='fensi')