小米笔记转移本地数据库

小米笔记转移本地数据库

最近更换手机，但是发现由于云端服务是厂家自有的，笔记不好转移。于是想办法存储在自己的数据库内。

从官网拉取笔记合并成json文件。

如何拉取数据及输出json文件，这里参考知乎的脚本：https://www.zhihu.com/question/35329107/answer/2726573615

拉取到json文件，格式如下：

{
	"folders": [
		{
			"snippet": "",
			"modifyDate": 1541993158000,
			"colorId": 0,
			"subject": "文章",
			"alertTag": 0,
			"alertDate": 0,
			"id": "19318531598729344",
			"tag": "19318531598729344",
			"type": "folder",
			"folderId": 0,
			"createDate": 1541993158000,
			"status": "normal"
		}
    ],
	"notes": [
		{
			"snippet": "xxx",
			"modifyDate": 1513902319554,
			"colorId": 0,
			"subject": "",
			"alertDate": 0,
			"type": "note",
			"folderId": 19318531598729344,
			"content": "xxx",
			"setting": {
				"themeId": 0,
				"stickyTime": 0,
				"version": 0
			},
			"alertTag": 0,
			"id": "15697820090777984",
			"tag": "15697820090767984",
			"createDate": 1513902283638,
			"status": "normal",
			"extraInfo": "{}"
		}
    ]
}

创建对应数据库

可以看到上面有两个表，一个是文件夹的表，一个是笔记的表。
建库：

CREATE DATABASE `minote` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

创建目录表：

CREATE TABLE folders (
    id VARCHAR(50) PRIMARY KEY COMMENT '文件夹唯一ID',
    tag VARCHAR(50) NOT NULL COMMENT '标签标识，通常与ID相同',
    subject VARCHAR(255) NOT NULL COMMENT '文件夹名称/标题',
    type VARCHAR(20) DEFAULT 'folder' COMMENT '类型：folder-文件夹',
    folderId BIGINT DEFAULT 0 COMMENT '父文件夹ID，0-根目录',
    colorId INT DEFAULT 0 COMMENT '颜色标识ID',
    alertTag INT DEFAULT 0 COMMENT '提醒标签：0-无提醒，1-有提醒',
    alertDate BIGINT DEFAULT 0 COMMENT '提醒时间戳(毫秒)，0-无提醒',
    status VARCHAR(20) DEFAULT 'normal' COMMENT '状态：normal-正常',
    snippet TEXT COMMENT '文件夹摘要/描述内容',
    createDate BIGINT NOT NULL COMMENT '创建时间戳(毫秒)',
    modifyDate BIGINT NOT NULL COMMENT '最后修改时间戳(毫秒)',

    INDEX idx_folderId (folderId),
    INDEX idx_status (status),
    INDEX idx_createDate (createDate),
    INDEX idx_modifyDate (modifyDate),
    INDEX idx_alertDate (alertDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

创建笔记表：

CREATE TABLE notes (
    id VARCHAR(50) PRIMARY KEY COMMENT '笔记唯一ID',
    tag VARCHAR(50) NOT NULL COMMENT '标签标识',
    subject VARCHAR(255) DEFAULT '' COMMENT '笔记标题',
    type VARCHAR(20) DEFAULT 'note' COMMENT '类型：note-笔记',
    folderId VARCHAR(50) NOT NULL COMMENT '所属文件夹ID',
    colorId INT DEFAULT 0 COMMENT '颜色标识ID',
    alertTag INT DEFAULT 0 COMMENT '提醒标签',
    alertDate BIGINT DEFAULT 0 COMMENT '提醒时间戳',
    status VARCHAR(20) DEFAULT 'normal' COMMENT '状态',
    snippet TEXT COMMENT '笔记摘要',
    content LONGTEXT COMMENT '笔记内容',
    themeId INT DEFAULT 0 COMMENT '主题ID',
    stickyTime BIGINT DEFAULT 0 COMMENT '置顶时间',
    version INT DEFAULT 0 COMMENT '版本号',
    extraInfo JSON COMMENT '扩展信息',
    createDate BIGINT NOT NULL COMMENT '创建时间戳',
    modifyDate BIGINT NOT NULL COMMENT '修改时间戳',
    
    INDEX idx_folderId (folderId) COMMENT '文件夹ID索引',
    INDEX idx_status (status) COMMENT '状态索引',
    INDEX idx_createDate (createDate) COMMENT '创建时间索引',
    INDEX idx_modifyDate (modifyDate) COMMENT '修改时间索引',
    INDEX idx_alertDate (alertDate) COMMENT '提醒时间索引',
    INDEX idx_stickyTime (stickyTime) COMMENT '置顶时间索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

python处理

需要连接到数据库，如何连接参考python连接mysql。

从目录打开文件并调用对应函数：

if __name__ == '__main__':
    with open(r"F:\notes_1.json", "r", encoding="utf8") as file:
        jsonData = json.load(file)
    putSql(jsonData)

putSql函数实现:

def putSql(jsonData):
	def putSql(jsonData):
    # 创建数据库连接
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='root99',
        database='minote',
        charset='utf8'
    )
    # 游标
    curosr = connection.cursor()

    folders = jsonData["folders"]
    notes = jsonData["notes"]
    print("folders size: ", len(folders))
    print("notes size: ", len(notes))

    #计数
    count = 0
    # 循环写入数据
    for folder in folders:
        sql = "insert into folders (id, tag, subject,type, folderId, colorId, alertTag, alertDate, status, snippet, createDate, modifyDate)" + \
        "values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

        try:
            curosr.execute(sql, (folder["id"], folder["tag"],
                             folder["subject"], folder["type"],
                             folder["folderId"], folder["colorId"],
                             folder["alertTag"], folder["alertDate"],
                             folder["status"] ,folder["snippet"],
                             folder["createDate"],folder["modifyDate"]))
            # 提交
            connection.commit()
        except:
            # 错误回滚
            connection.rollback()
            print("写入数据失败：", folder)
        finally:
            count += curosr.rowcount
            print("folders rowcount: ", count)

    count = 0
    for note in notes:
        sql = "insert into notes(id, tag, subject, type, folderId, colorId, alertTag, alertDate, status, snippet, content, themeId, stickyTime, version, extraInfo, createDate, modifyDate)" + \
            "values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        try:
            curosr.execute(sql, (note["id"], note["tag"],
                                 note["subject"], note["type"],
                                 note["folderId"], note["colorId"],
                                 note["alertTag"], note["alertDate"],
                                 note["status"] ,note["snippet"],
                                 note["content"], note["setting"]["themeId"],
                                 (note["setting"]["stickyTime"]), (note["setting"]["version"]),
                                 note["extraInfo"], note["createDate"],note["modifyDate"]))
            #提交
            connection.commit()
        except:
            connection.rollback()
            print("写入数据失败：", note)
        finally:
            count += curosr.rowcount
            print("notes rowcount: ", count)

    #关闭游标和数据库连接
    curosr.close()
    connection.close()

执行完成后，进入数据库可看到对应数据信息。