迁移cnblogs博客园的blog备份db文件到本地的博客系统

1. 先cnblogs后台导出备份文件db 到本地 ， 备份文件为sqlite3 ，字段格式如下 

 

 

2. 查看本地博客系统数据库sqlite3的字段格式如下： 

 

3. 通过python 处理将备份sqlite3中的字段做处理调整成本地博客系统的字段 

 

import pandas as pd
import sqlite3

db_file="./cnblogs_blog_*****.20230912103223.db"

conn = sqlite3.connect(db_file, isolation_level=None)
db_df = pd.read_sql_query("SELECT Id,Title,DateAdded,Body FROM blog_Content",conn)

new_df=db_df.rename(columns={'Id':'id','Title':'title','DateAdded':'date','Body':'body'})
new_df['author_id']= 1
new_df['subtitle']= 1
new_df['img_url']= "http://123.com"
print(new_df)
#db_df.to_csv('database.csv',encoding='utf_8_sig')



new_conn = sqlite3.connect('./posts.db')

print(new_conn)
new_df.to_sql('blog_posts', new_conn, if_exists='replace', index=False)

new_conn.close()

　　

4. 本地blog系统的数据库调整为新的数据库， 修改数据读取按照时间倒序 

 

@app.route('/')
def get_all_posts():
    # result = db.session.execute(db.select(BlogPost))
    result = db.session.query(BlogPost).order_by(BlogPost.date.desc())
    # posts = result.scalars().all()
    posts = result.all()
    return render_template("index.html", all_posts=posts, current_user=current_user)

　　

前端显示页面

<!-- Main Content-->
<div class="container px-4 px-lg-5">
  <div class="row gx-4 gx-lg-5 justify-content-center">
    <div class="col-md-10 col-lg-8 col-xl-7">
      <!-- Post preview-->
      {% for post in all_posts %}
      <div class="post-preview">
        <a href="{{ url_for('show_post', post_id=post.id) }}">
          <h2 class="post-title">{{ post.title }}</h2>
          <h3 class="post-subtitle">{{ post.subtitle }}</h3>
        </a>
        <p class="post-meta">
          Posted by
          <!-- post.author.name is now a User object -->
          <a href="#">{{post.author.name}}</a>
          on {{post.date}}
          <!-- Only show delete button if user id is 1 (admin user) -->
          {% if current_user.id == 1: %}
          <a href="{{url_for('delete_post', post_id=post.id) }}">✘</a>
          {% endif %}
        </p>
      </div>
      <!-- Divider-->
      <hr class="my-4" />
      {% endfor %}

　　

文本内容页面

<!-- Post Content -->
<article>
  <div class="container px-4 px-lg-5">
    <div class="row gx-4 gx-lg-5 justify-content-center">
      <div class="col-md-10 col-lg-8 col-xl-7">
        {{ post.body|safe }}
        <!--Only show Edit Post button if user id is 1 (admin user) -->
        {% if current_user.id == 1 %}
        <div class="d-flex justify-content-end mb-4">
          <a
            class="btn btn-primary float-right"
            href="{{url_for('edit_post', post_id=post.id)}}"
            >Edit Post</a
          >
        </div>
        {% endif %}

　　

5. 启动本地博客系统后， 可以正常显示 

 

 

 

跟cnblogs内容类似 ，可作为本地的一个备份博客