(二)flask搭建博客系列之SQLAlchemy

本篇博客介绍flask中数据库的使用，以mysql数据库为例，请先安装好mysql数据库，并新建一个名为myflask的数据库。

1.相关库安装

pip install flask-sqlalchemy flask-migrate cymysql

2.相关代码

我们新建config.py用来存放相关配置信息，新建extensions.py用来存放相关扩展的实例，新建models.py用来存放数据库相关代码，新建utils.py用来存放一些工具函数，目录结构变更如下：

config.py中写入数据库配置信息:

class Config(object):
    SQLALCHEMY_DATABASE_URI = 'mysql+cymysql://root:root@localhost:3306/myflask?charset=utf8'
    SQLALCHEMY_TRACK_MODIFICATIONS = True

extensions.py中创建连接数据库和迁移数据库的实例:

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

db = SQLAlchemy()
migrate = Migrate()

models.py中建立4个表，分别为管理员表、博客文章表、评论表、博客分类表，分类和文章、文章和评论都是一对多的关系，这里使用了外键:

from datetime import datetime
from werkzeug.security import generate_password_hash, check_password_hash

from myblog.extensions import db


class Admin(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20))
    password_hash = db.Column(db.String(128))

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def validate_password(self, password):
        return check_password_hash(self.password_hash, password)


class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(60))
    body = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)

    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

    category = db.relationship('Category', back_populates='articles')
    comments = db.relationship('Comment', back_populates='article', cascade='all, delete-orphan')


class Comment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    author = db.Column(db.String(30))
    body = db.Column(db.Text)

    article_id = db.Column(db.Integer, db.ForeignKey('article.id'))

    article = db.relationship('Article', back_populates='comments')


class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), unique=True)

    articles = db.relationship('Article', back_populates='category')

__init__.py中的初始化代码修改如下：

from flask import Flask

from myblog.home.blog import blog_bp
from myblog.extensions import db, migrate
from myblog.config import Config


def create_app():
    app = Flask(__name__)
    app.config.from_object(Config)

    register_blueprints(app)
    register_extensions(app)

    return app


def register_blueprints(app):
    app.register_blueprint(blog_bp)


def register_extensions(app):
    db.init_app(app)
    db.create_all(app=app)
    migrate.init_app(app, db)

在utils.py中添加利用faker库生成虚拟数据的工具函数（安装：pip install faker）：

import random
from faker import Faker 
from sqlalchemy.exc import IntegrityError

from myblog.models import Admin, Article, Comment, Category
from myblog.extensions import db


fake = Faker()


def fake_all():
    fake_admin()
    fake_categories()
    fake_articles()
    fake_comments()


def fake_admin():
    if Admin.query.first():
        return 
    else:
        admin = Admin(username='admin')
        admin.set_password('admin')
        db.session.add(admin)
        db.session.commit()


def fake_categories(count=10):
    if Category.query.count() == 10:
        return 
    else:
        for i in range(count):
            category = Category(name = fake.word())
            db.session.add(category)
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()


def fake_articles(count=50):
    for i in range(count):
        article = Article(
            title = fake.sentence(3),
            body = fake.text(2000),
            category = Category.query.get(random.randint(1, Category.query.count())),
            timestamp = fake.date_time_this_year()
        )
        db.session.add(article)
    db.session.commit()


def fake_comments(count=200):
    for i in range(count):
        comment = Comment(
            author = fake.name(),
            body = fake.sentence(),
            article = Article.query.get(random.randint(1, Article.query.count()))
        )
        db.session.add(comment)
    db.session.commit()

然后修改blog.py中的视图函数来展示数据库中的数据：

from flask import Blueprint

from myblog.models import Article, Category, Comment
from myblog.utils import fake_all


blog_bp = Blueprint('blog', __name__)


@blog_bp.route('/')            
def hello_world(): 
    fake_all()
    article = Article.query.first()

    title = article.title
    body = article.body
    category = article.category.name

    if article.comments:
        comment = article.comments[0].body
    else:
        comment = 'no comments yet'

    return """
    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="utf-8">
    <title>SQLAlchemy展示</title>
    </head>
    <body>
        <h1>Title:</h1>
        <p>{}</p>
        <h1>Body:</h1>
        <p>{}</p>
        <h1>Category:</h1>
        <p>{}</p>
        <h1>Comment</h1>
        <p>{}</p>
    </body>
    </html>
    """.format(title, body, category, comment)

最后启动服务 python wsgi.py 在浏览器中可以看到如下效果：

3.数据库迁移介绍

当我们对数据库中的表做了结构调整后，需要保留原数据进行更新时，可以使用flask-migrate工具很方便地进行数据库迁移的工作。例如我们在文章表中添加一个表示摘要的digest字段，之前的文章表如下图所示：

我们修改models.py中的Article文章类如下：

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(60))
    digest = db.Column(db.String(120))
    body = db.Column(db.Text)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)

    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

    category = db.relationship('Category', back_populates='articles')
    comments = db.relationship('Comment', back_populates='article', cascade='all, delete-orphan')

我们在项目根目录myflask下执行如下命令（会自动生成migrations文件夹）：

flask db init                                      # 初始化
flask db migrate -m 'add digest field in Article'  # 提交改变
flask db upgrade                                   # 正式迁移

然后可以看到数据库中的Article表已经添加了digest字段：

若迁移时发生错误，可以使用如下命令方便地进行回滚：

flask db downgrade