接上文：flask+pymysql操作MySQL数据库（上）

一、备份简书文章

上面实现了操作SQLAlchemy把数据存入数据库，接下来我试着把我的简书文章全部存入数据库做备份，直接上脚本：

vim modle.py

# coding:utf-8

from flask.ext.sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config['SECRET_KEY'] ='hard to guess'
app.config['SQLALCHEMY_DATABASE_URI']='mysql+pymysql://jianshu:jianshu@127.0.0.1:3306/jianshu'
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN']=True
db = SQLAlchemy(app)

class Category(db.Model):
    __tablename__ = 'categorys'
    id = db.Column(db.Integer, primary_key=True)
    category_name = db.Column(db.String(64), unique=True, index=True)
    category_url = db.Column(db.String(64), unique=True)
    user = db.relationship('Article', backref='category')

class Article(db.Model):
    __tablename__ = 'articles'
    id = db.Column(db.Integer, primary_key=True)
    article_name = db.Column(db.String(64), unique=True, index=True)
    article_url = db.Column(db.String(64), unique=True, index=True)
    article_text = db.Column(db.Text(10000))
    category_id = db.Column(db.Integer, db.ForeignKey('categorys.id'))

vim jianshu.py

# !/usr/bin/env python
# coding:utf-8

import requests
from bs4 import BeautifulSoup
from .modle import db,Category,Article

class Category_jianshu():
    '''
        获取归档信息
    '''

    def __init__(self,user_url):
        self.user_url = user_url

    def get_category(self):
        html = requests.get(self.user_url).content
        soup = BeautifulSoup(html, 'html.parser', from_encoding='utf-8')
        category_list = soup.find_all('a', class_="fa fa-book")

        category_info = []
        for category in category_list:
            category_url = main_url + category['href']
            category_title = category.get_text()
            category_info.append([category_title,category_url])
        return category_info

class Article_jianshu():
    '''
        获取每个归档中文章信息
    '''

    def __init__(self,category_url):
        self.category_url = category_url

    def get_article(self):
        html = requests.get(self.category_url).content
        soup = BeautifulSoup(html, 'html.parser', from_encoding='utf-8')
        article_list = soup.find_all('h4', class_="title")

        article_info = []
        for article in article_list:
            article_url = main_url + article.a['href']
            article_title = article.get_text()
            article_info.append([article_title,article_url])
        return article_info

class Body_jianshu():
    '''
        获取文章body
    '''

    def __init__(self, article_url):
        self.article_url = article_url

    def get_body(self):
        html = requests.get(self.article_url).content
        soup = BeautifulSoup(html, 'html.parser', from_encoding='utf-8')
        article_content = soup.find_all('div', class_="show-content")

        body = str(article_content[0])
        # 将 body 中 " 转换为 \", ' 转换为 \'
        body = body.replace('"', '\\"')
        body = body.replace("'", "\\'")
        return body

if __name__ == '__main__':
    main_url = 'http://www.jianshu.com'
    user_id = '40758c9db703'
    user_url = 'http://www.jianshu.com/users/' + user_id + '/top_articles'
    category_lst = Category_jianshu(user_url).get_category()

    '''生成数据'''
    db.drop_all()
    db.create_all()

    for cate in category_lst:
        category = Category(category_name=cate[0],category_url=cate[1])
        # 把归档目录存入数据库
        db.session.add_all([category])
        article_lst = Article_jianshu(cate[1]).get_article()
        for art in article_lst:
            body = Body_jianshu(art[1]).get_body()
            article = Article(article_name=art[0], article_url=art[1],article_text=body, category=category)
            # 存入文章信息
            db.session.add_all([article])
    db.session.commit()

执行

python jianshu.py

my-jianshu

可以看到数据都已经放到数据库中了；

二、展示数据库内容到前端

设置路由
直接在上面modle.py中加入

...
@app.route('/',methods=['GET'])
def index():
    category_names = []
    category_query = db.session.query(Category.category_name).all()
    for category_name in category_query:
        category_names.append(category_name[0])
    return render_template('index.html',names=category_names)

if __name__ == '__main__':
    app.run(host="0.0.0.0", port=8888, debug=True)

注：记得在前面 加载 render_template模块

template文件
当前目录下新建templates目录，并在里面放入index.html文件
vim index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script src="http://cdn.bootcss.com/jquery/1.11.2/jquery.min.js"></script>
    <script src="http://cdn.bootcss.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
    <meta charset="UTF-8">
    <title>Blog | Category</title>
</head>
<body>
    <div class="navbar-header">
      <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target=".bs-navbar-collapse">
        <span class="sr-only">Blog | </span>
      <a href="#" class="navbar-brand">简书归档</a>
      </button>
    </div>
    <div id="navbar" class="collapse navbar-collapse bs-navbar-collapse">
      {% for name in names %}
          <ul class="nav navbar-nav">
              <li><a href="#">{{ name }}</a></li>
          </ul>
      {% endfor %}
    </div>
</body>
</html>

运行

python modle.py

blog|category

这里只是展示了归档目录的内容，如需展示其他内容需要好好学习下flask的其他知识，这里我就不继续写了。

好了，关于flask操作数据库的一些简单操作及示例说明到此为止~