Flask SQLAlchemy管理数据库

Flask SQLAlchemy管理数据库

关系型数据库框架，支持多种数据库，提供高层ORM, 也支持执行原生SQL

$ pip install flask-sqlalchemy
$ pip install pymysql

数据库连接URL

由于使用Python3开发语言，python3中已经不再支持MySQLdb模块，所以这里使用pymysql数据库连接组件操作数据库
mysql+pymysql://username:password@host/database

创建数据库 mysql控制台
mysql> create database school default charset utf8;

from flask import Flask
from flask_script import Manager
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# 设置MySQL数据库URI
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://{username}:{password}@{host}/{database}?charset=UTF8MB4'

# 设置SQL Server数据库URI
# CONNECT_DATABASE = "mssql+pymssql://{username}:{password}@{host}:{port}/{database}?charset=utf8"

# 不跟踪修改，不设置会有警告
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 创建数据库连接对象
db = SQLAlchemy(app)

import application.models
import application.views

manager = Manager(app)

定义模型

from application import db
import datetime

# 用户表
class Users(db.Model):
    __tablename__ = 'users'
    # 主键
    id = db.Column(db.Integer, primary_key=True)
    # 用户名 唯一索引
    username = db.Column(db.String(128), unique=True, nullable=False)
    # 密码 必填字段
    password = db.Column(db.String(512), nullable=False)
    # 姓名 创建索引，加快查询
    fullname = db.Column(db.String(128), index=True, nullable=False)
    # 状态 (1: 生效 0: 禁用)
    status = db.Column(db.SmallInteger, default=1, nullable=False)
    # 创建时间 默认当前时间
    created_time = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow, index=True)

    def __repr__(self):
        return 'username=%s' % self.username    
   

根据model创建表

$ python manage.py shell
>>> from application import db
>>> db.drop_all()
>>> db.create_all()

插入数据

$ python manager.py shell
>>> user = Users(username='lisa', password='asdasdfad', fullname='李霞')
>>> db.session.add(user) # 添加到数据库会话中
>>> db.session.commit() # 提交数据
>>> db.session.rollback() # 数据库回滚无效, 放在commit前面会生效

查询数据

>>> Student.query.all()  # 查询全部，通过遍历能去点对象的属性
[<Student 4>, <Student 3>, <Student 2>, <Student 1>]

>>> c1 = Class.query.filter_by(name='python 1905')  新的查询，不能去点对象的属性
>>> c1
<flask_sqlalchemy.BaseQuery object at 0x00000205C358>

>>> c1 = Class.query.filter_by(name='python 1905').first()  返回查询结果
>>> c1
<Class 1>

>>> c1 = Class.query.filter_by(name='python 1905').all()  返回的是一个查询结果
>>> c1
[<Class 1>]
>>> c1[0].name
'python 1905'

>>> Student.query.filter(Student.name=='lisa').first_or_404() 查询不到返回404

>>> str(Student.query.filter(Student.name=='lisa3'))   查看sql语句

>>> db.session.execute('select * from users where id = 1').first() 执行原生sql语句

修改行

user = Users.query.get(1)
user.password = '123412343'
db.session.add(user)
db.session.commit()

删除行

db.session.delete(user)
db.session.commit()

关系

一对多关系

# 学生表
class Student(db.Model):
    __tablename__ = 'student'
    id = db.Column(db.Integer, primary_key=True)
    # mysql长度超过128位，唯一索引加不上
    name = db.Column(db.String(128), unique=True, nullable=False)
    # 建立指向班级的外键
    class_id = db.Column(db.Integer, db.ForeignKey('class.id'))

    def __repr__(self):
        return 'student=%s' % self.name  

# 班级表
class Class(db.Model):
    __tablename__ = 'class'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=True, nullable=False)
    # 引用学生, 学生可以通过sclass反向引用班级
    students = db.relationship('Student', backref='sclass')

一对多关系查询

>>> from application.models import *
>>> stu = Student.query.filter_by(name='张飞').first()
>>> stu
<Student 1>
>>> stu.sclass
<Class 5>
>>> stu.sclass.name
'HTML 1907'
>>> stu.class_id
5
>>> stu.sclass.id
5
>>>

一对一关系

一对一关系可以用前面介绍的一对多关系 表示，但调用db.relationship() 时要把uselist设为False

student = db.relationship('Student', backref='sclass', uselist=False)

多对多关系建模

class StudentLesson(db.Model):
    __tablename__ = 'student_lesson'
    student_id = db.Column(db.Integer, db.ForeignKey('student.id'), primary_key=True)
    lesson_id = db.Column(db.Integer, db.ForeignKey('lesson.id'), primary_key=True)
    score = db.Column(db.Integer, nullable=True)

class Student(db.Model):
    __tablename__ = 'student'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=True, nullable=False)
    lessons = db.relationship('StudentLesson', foreign_keys=[StudentLesson.student_id], backref=db.backref('student', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan')

    def __repr__(self):
        return 'student=%s' % self.name    

class Lesson(db.Model):
    __tablename__ = 'lesson'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=True, nullable=False)
    students = db.relationship('StudentLesson', foreign_keys=[StudentLesson.lesson_id], backref=db.backref('lesson', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan')

    def __repr__(self):
        return 'lesson=%s' % self.name    

多对多关系测试

>>> from application.models import *
>>> from application import db
>>> db.create_all()
>>> student1 = Student(name='carmack')
>>> student2 = Student(name='lisa')
>>> db.session.add_all([student1, student2])
>>> db.session.commit()
>>> lesson1 = Lesson(name='数学')
>>> lesson2 = Lesson(name='语文')
>>> db.session.add_all([lesson1, lesson2])
>>> db.session.commit()
>>> student_lesson = StudentLesson(student=student1, lesson=lesson1)
>>> db.session.add(student_lesson)
>>> db.session.commit()

常用的SQLAlchemy关系选项

• backref
  在关系的另一个模型中添加反向引用
• primaryjoin
  明确指定两个模型之间使用的联结条件。只在模棱两可的关系中需要指定
• lazy
  指定如何加载相关记录。可选值有 select(首次访问时按需加载)、immediate(源对象加 载后就加载)、joined(加载记录，但使用联结)、subquery(立即加载，但使用子查询)， noload(永不加载)和 dynamic(不加载记录，但提供加载记录的查询)
• uselist
  设为False，不使用列表

使用Flask-Migrate实现数据库迁移

安装migrate

pip install flask-migrate

配置使用migrate

初始化，使用app和db进行migrate对象的初始化

from flask_migrate import Migrate

#绑定app和数据库
migrate = Migrate(app, db)

在Manager()对象上添加迁移指令

from flask_migrate import Migrate, MigrateCommand

app = Flask(__name__)

manager = Manager(app=app)

manager.add_command(‘db’, MigrateCommand)

数据库迁移表操作

python manager.py db init 初始化得到migrations的文件，只需调用一次

python manager.py db migrate -m ‘说明’ 生成迁移文件

python manager.py db upgrade 执行迁移文件中的升级

python manager.py db downgrade 执行迁移文件中的降级

python manager.py db —help 帮助文档