接上文：flask+pymysql操作MySQL數(shù)據(jù)庫（上）

一、備份簡書文章

上面實現(xiàn)了操作SQLAlchemy把數(shù)據(jù)存入數(shù)據(jù)庫，接下來我試著把我的簡書文章全部存入數(shù)據(jù)庫做備份，直接上腳本：

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 中 " 轉(zhuǎn)換為 \", ' 轉(zhuǎn)換為 \'
        body = body.replace('"', '\\"')
        body = body.replace("'", "\\'")
        return body

if __name__ == '__main__':
    main_url = 'http://m.itdecent.cn'
    user_id = '40758c9db703'
    user_url = 'http://m.itdecent.cn/users/' + user_id + '/top_articles'
    category_lst = Category_jianshu(user_url).get_category()

    '''生成數(shù)據(jù)'''
    db.drop_all()
    db.create_all()

    for cate in category_lst:
        category = Category(category_name=cate[0],category_url=cate[1])
        # 把歸檔目錄存入數(shù)據(jù)庫
        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()

執(zhí)行

python jianshu.py

my-jianshu

可以看到數(shù)據(jù)都已經(jīng)放到數(shù)據(jù)庫中了；

二、展示數(shù)據(jù)庫內(nèi)容到前端

設(shè)置路由
直接在上面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文件
當(dāng)前目錄下新建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

這里只是展示了歸檔目錄的內(nèi)容，如需展示其他內(nèi)容需要好好學(xué)習(xí)下flask的其他知識，這里我就不繼續(xù)寫了。

好了，關(guān)于flask操作數(shù)據(jù)庫的一些簡單操作及示例說明到此為止~