Anki 笔记读取器

发表于 本文字数： 11k 阅读时长 ≈ 10 分钟

Anki 笔记数据库

Anki 的笔记数据都存储在 collection.anki2 文件中，包含了用户的所有笔记、卡片、标签等信息。

文件路径 
1
C:\Users\Administrator\AppData\Roaming\Anki2\账户1\collection.anki2

可以使用 SQLite 或 Python 的 sqlite3 模块连接并查询该数据库，查看具体的表和字段。

本地笔记检索

使用 sqlite3 链接数据库

打印 collection.anki2 数据库中的所有表格 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import sqlite3  
  
# 连接到 Anki 数据库（此处注意替换为自己的文件路径）  
anki_db_path = r'C:\Users\Administrator\AppData\Roaming\Anki2\本地账户\collection.anki2'  
conn = sqlite3.connect(anki_db_path)  
  
# 查看 notes 表的结构  
cursor = conn.cursor()  
cursor.execute("PRAGMA table_info(notes);")  
columns = cursor.fetchall()  
  
# 打印字段名  
for column in columns:  
    print(column)  
  
# 关闭连接  
conn.close()

输出结果为

1
2
3
4
5
6
7
8
9
10
11
(0, 'id', 'INTEGER', 0, None, 1)  
(1, 'guid', 'TEXT', 1, None, 0)  
(2, 'mid', 'INTEGER', 1, None, 0)  
(3, 'mod', 'INTEGER', 1, None, 0)  
(4, 'usn', 'INTEGER', 1, None, 0)  
(5, 'tags', 'TEXT', 1, None, 0)  
(6, 'flds', 'TEXT', 1, None, 0)  
(7, 'sfld', 'INTEGER', 1, None, 0)  
(8, 'csum', 'INTEGER', 1, None, 0)  
(9, 'flags', 'INTEGER', 1, None, 0)  
(10, 'data', 'TEXT', 1, None, 0)

说明可以正常连接数据库，准备工作已做好

制作一个 Python 小程序读取数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
import sys
import sqlite3
import pandas as pd
import re
from PyQt6.QtWidgets import (
    QApplication, QWidget, QVBoxLayout, QPushButton,
    QLineEdit, QTextEdit, QMessageBox
)

class AnkiNoteReader(QWidget):
    def __init__(self):
        super().__init__()
        self.initUI()
        self.notes_df = None

    def initUI(self):
        self.setWindowTitle('Anki 笔记读取器')

        layout = QVBoxLayout()

        self.load_button = QPushButton('加载笔记', self)
        self.load_button.clicked.connect(self.load_notes)
        layout.addWidget(self.load_button)

        self.search_box = QLineEdit(self)
        self.search_box.setPlaceholderText('搜索关键词')
        layout.addWidget(self.search_box)

        self.search_button = QPushButton('搜索', self)
        self.search_button.clicked.connect(self.search_notes)
        layout.addWidget(self.search_button)

        self.text_area = QTextEdit(self)
        layout.addWidget(self.text_area)

        self.setLayout(layout)

    def load_notes(self):
        try:
            # 连接到 Anki 数据库
            anki_db_path = r'C:\Users\Administrator\AppData\Roaming\Anki2\本地账户\collection.anki2'
            conn = sqlite3.connect(anki_db_path)
            query = 'SELECT id, flds FROM notes'
            self.notes_df = pd.read_sql_query(query, conn)
            conn.close()

            self.display_notes(self.notes_df)
        except Exception as e:
            QMessageBox.critical(self, "错误", str(e))

    def display_notes(self, notes):
        self.text_area.clear()
        for index, row in notes.iterrows():
            # 清除不可见字符，保留中文和其他可见字符
            fields = re.sub(r'[^\u4e00-\u9fa5\x20-\x7E]', ' ', row['flds'])  # 保留中文和可见字符
            self.text_area.append(f"Note ID: {row['id']}\nFields: {fields.strip()}\n")

    def search_notes(self):
        if self.notes_df is not None:
            search_term = self.search_box.text()
            if search_term:
                filtered_notes = self.notes_df[self.notes_df['flds'].str.contains(search_term, na=False)]
                self.display_notes(filtered_notes)
            else:
                self.display_notes(self.notes_df)
        else:
            QMessageBox.warning(self, "警告", "请先加载笔记！")

if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = AnkiNoteReader()
    ex.resize(600, 400)
    ex.show()
    sys.exit(app.exec())

image.png

加载笔记以及搜索笔记如下图
image.png

image.png

远程笔记检索

功能实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
from flask import Flask, request, jsonify  
import sqlite3  
import pandas as pd  
  
app = Flask(__name__)  
  
# 数据库文件路径  
DATABASE_PATH = r'C:\Users\Administrator\AppData\Roaming\Anki2\本地账户\collection.anki2'  
  
  
def query_database(query):  
    """执行 SQL 查询并返回结果为 DataFrame"""    conn = sqlite3.connect(DATABASE_PATH)  
    df = pd.read_sql_query(query, conn)  
    conn.close()  
    return df  
  
  
@app.route('/search', methods=['GET'])  
def search_notes():  
    """根据查询词搜索笔记"""  
    search_term = request.args.get('term', '')  
    query = f"SELECT id, flds FROM notes WHERE flds LIKE '%{search_term}%'"  
  
    try:  
        results = query_database(query)  
        notes = results.to_dict(orient='records')  
        return jsonify(notes)  
    except Exception as e:  
        return jsonify({'error': str(e)})  
  
  
if __name__ == '__main__':  
    app.run(host='0.0.0.0', port=5000)  # 可访问所有 IP

运行后提示：

1
2
3
4
5
6
 * Serving Flask app 'app'
 * Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on all addresses (0.0.0.0)
 * Running on http://127.0.0.1:5000
 * Running on http://192.168.1.94:5000

这时，我们可以在浏览器输入

1
http://192.168.1.94:5000/search?term= 要搜索的内容

image.png

完善输出

  • 增加搜索按钮
  • 优化输出前端效果
  • 表格形式输出
  • 添加图片显示（需添加图片数据库地址）
  • 添加导出 CSV 功能

项目结构为：

1
2
3
4
5
6
7
/project
    /static
        styles.css
        javascript.js
    /templates
        index.html
    app.py

image.png

Anki.gif

主程序

以下代码中，需要将 Anki 数据库路径及图片媒体路径替换为自己电脑对应的路径

app.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
from flask import Flask, request, send_from_directory, render_template, send_file
import sqlite3
import pandas as pd
import os
import re
import csv

app = Flask(__name__)

# 数据库文件路径（需要替换成自己Anki数据库及图片路径）
DATABASE_PATH = r'C:\Users\Administrator\Desktop\collection.anki2'
MEDIA_PATH = r'C:\Users\Administrator\AppData\Roaming\Anki2\账户1\collection.media'

# 允许访问媒体文件
@app.route('/media/<path:filename>')
def media(filename):
    full_path = os.path.join(MEDIA_PATH, filename)
    print(f"Attempting to access: {full_path}")
    return send_from_directory(MEDIA_PATH, filename)

def query_database(query, params):
    """执行 SQL 查询并返回结果为 DataFrame"""
    conn = sqlite3.connect(DATABASE_PATH)
    df = pd.read_sql_query(query, conn, params=params)
    conn.close()
    return df

@app.route('/', methods=['GET', 'POST'])
def index():
    results = []
    message = ""
    if request.method == 'POST':
        search_term = request.form.get('term', '')
        print(f"Searching for: {search_term}")
        # query = "SELECT id, flds FROM notes WHERE flds LIKE ?"

        # 更新查询，获取 flags 字段
        # query = """
        #     SELECT id, flds, flags
        #     FROM notes
        #     WHERE flds LIKE ?
        # """

        query = """
            SELECT id, flds
            FROM notes 
            WHERE flds LIKE ?
        """

        try:
            results_df = query_database(query, (f'%{search_term}%',))
            print(f"Query results: {results_df}")
            if results_df.empty:
                message = "未找到相关结果。"
            else:
                for _, row in results_df.iterrows():
                    fields = row['flds'].split('\x1f')
                    print(f"Fields content: {fields}")

                    for i, field in enumerate(fields):
                        matches = re.findall(r'src="([^"]+\.(?:jpg|png|gif))"', field)
                        for match in matches:
                            img_tag = f' class="image" src="/media/{match}" alt="Image" '
                            field = re.sub(r'src="[^"]*"', img_tag, field, count=1)
                        fields[i] = field.strip()

                    # results.append({'id': row['id'], 'flds': fields})

                    # 添加 flags 数据
                    # results.append({'id': row['id'], 'flds': fields, 'flags': row['flags']})
                    results.append({'id': row['id'], 'flds': fields})

        except Exception as e:
            message = f"查询错误: {str(e)}"
            print(message)

    # 使用 render_template 加载 HTML 模板
    return render_template('index.html', results=results, message=message)


@app.route('/export', methods=['GET'])
def export_notes():
    search_term = request.args.get('term', '')
    print(f"导出请求，搜索词: {search_term}")  # 确认请求是否到达

    # query = """
    #     SELECT id, flds, flags
    #     FROM notes
    #     WHERE flds LIKE ?
    # """

    query = """
        SELECT id, flds
        FROM notes 
        WHERE flds LIKE ?
    """

    try:
        results_df = query_database(query, (f'%{search_term}%',))
        temp_file = 'exported_notes.csv'

        # 使用 utf-8-sig 编码
        results_df.to_csv(temp_file, index=False, encoding='utf-8-sig')

        return send_file(temp_file, as_attachment=True)

    except Exception as e:
        print(f"导出错误: {str(e)}")
        return "导出失败", 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)

HTML

index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<!DOCTYPE html>
<html lang="zh">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Anki 笔记查询</title>
    <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='styles.css') }}">
    <script src="static/javascript.js"></script> <!-- 引入 JavaScript 文件 -->
</head>
<body>
    <h1>Anki 笔记查询</h1>
    <form method="post">
        <input type="text" name="term" placeholder="输入搜索关键词" required>
        <input type="text" id="searchInput" placeholder="搜索内容并导出内容">
        <button type="submit">搜索</button>
        <button onclick="exportNotes()">导出笔记</button>
    </form>
    <h2>查询结果</h2>
    <div class="message">{{ message }}</div>
    <table>
        <tr>
            <th>Note ID</th>
            <th>Fields</th>
<!--            <th>flags</th>-->
        </tr>
        {% for note in results %}
        <tr>
            <td>{{ note.id }}</td>
            <td>{{ note.flds | join('<br>') | safe }}</td>
             <!-- 展示旗帜数据 -->
<!--            <td>{{ note.flags }}</td>-->
        </tr>
        {% endfor %}
    </table>
</body>
</html>

javascript

javascript.js
1
2
3
4
5
6
function exportNotes() {
    const searchTerm = document.getElementById('searchInput').value; // 获取搜索词
    const url = `/export?term=${encodeURIComponent(searchTerm)}`; // 构建请求 URL

    window.location.href = url; // 直接跳转到 URL 下载文件
}

CSS

styles.css
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
/* styles.css */
body {
    font-family: Arial, sans-serif;
    margin: 20px;
    background-color: #f9f9f9; /* 背景颜色 */
}

table {
    width: 100%;
    border-collapse: collapse; /* 确保边框合并 */
    box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1); /* 添加阴影 */
}

th, td {
    border: 1px solid #ddd; /* 添加边框 */
    padding: 12px; /* 增加内边距 */
    text-align: left;
}

th {
    background-color: #4CAF50; /* 表头背景色 */
    color: white; /* 表头字体颜色 */
}

tr:nth-child(even) {
    background-color: #f2f2f2; /* 偶数行背景色 */
}

tr:hover {
    background-color: #e0f7fa; /* 鼠标悬停行的背景色 */
}

.message {
    color: red;
}

.image {
    max-width: 600px; /* 调整图片尺寸 */
    max-height: 600px;
    border-radius: 5px; /* 圆角 */
}